Query Rewrite with Postgres' materialized views

On Sun, Feb 15, 2015 at 10:05 PM, Eric Grinstein
<ericgrinstein@gmail.com> wrote:

Are there any plans for implementing query rewriting (i.e. allowing the
optimizer to substitute materialized views for queries)
in upcoming versions? I have recently seen this answer, saying that query
rewriting could be achieved using the rules system, but
I could not figure out how to. Could someone please give me some tips on how
to do it? Is it possible to make a rule not to all SELECTs of a table but
for a specific query? Or the trick is done with another approach?

I'm not really sure what Josh was talking about in that answer. In
terms of doing this automatically, I doubt that's likely to happen
until we have a way to automatically update a materialized view when
the underlying data changes --- and Kevin Grittner has done a bunch of
work towards that, but more is needed to get us there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

I'm not really sure what Josh was talking about in that answer. In
terms of doing this automatically, I doubt that's likely to happen
until we have a way to automatically update a materialized view when
the underlying data changes --- and Kevin Grittner has done a bunch of
work towards that, but more is needed to get us there.

Even with auto maintenance, it seems unlikely that matviews would ever
be so transparent that it would be okay for the planner to automatically
substitute them into a query. The data in a matview is going to be at
least a little bit stale, else You're Doing It Wrong.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Feb 21, 2015 at 1:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

I'm not really sure what Josh was talking about in that answer. In
terms of doing this automatically, I doubt that's likely to happen
until we have a way to automatically update a materialized view when
the underlying data changes --- and Kevin Grittner has done a bunch of
work towards that, but more is needed to get us there.

Even with auto maintenance, it seems unlikely that matviews would ever
be so transparent that it would be okay for the planner to automatically
substitute them into a query. The data in a matview is going to be at
least a little bit stale, else You're Doing It Wrong.

Perhaps, but this is a feature we've gotten MANY customer requests
for. IDK why, but it's true.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thank you for your answers.
I am very eager to contribute to Postgres, especially in the materialized
views area.
I have created a thread
</messages/by-id/CAK7uWEzzXJjsXP9UoXWHpnjjutjaJVpkja9SkZaLsNNrDJsJ6A@mail.gmail.com&gt;
proposing
to work on it as my Google Summer of Code project.
It became a consensus that implementing the query rewrite feature would be
unfeasible
for such a short period of time, but Tomas Vondra suggested I could work on
a first part of It, such as
making a staleness flag for the MVs. If you have any opinions on the
thread, i'd be pleased to read them.

Thank you and regards,

Eric

2015-02-21 16:09 GMT-02:00 Robert Haas <robertmhaas@gmail.com>:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

I'm not really sure what Josh was talking about in that answer. In
terms of doing this automatically, I doubt that's likely to happen
until we have a way to automatically update a materialized view when
the underlying data changes --- and Kevin Grittner has done a bunch of
work towards that, but more is needed to get us there.

Even with auto maintenance, it seems unlikely that matviews would ever
be so transparent that it would be okay for the planner to automatically
substitute them into a query. The data in a matview is going to be at
least a little bit stale, else You're Doing It Wrong.

There are people who want all sorts of different timings for
applying the changes to the matviews -- from so eager that the
matview changes are immediately visible to the transaction
modifying the underlying table (i.e., the same timing as a
non-deferred trigger), to applying the delta "on demand" on some
schedule. For that reason I think you're Doing It Wrong if there
is not a decoupling of the delta calculation from the application.
I think we should support all of the requested timings, but that
there may be ways to take advantage of cases where the matview is
always in sync with the underlying tables.

My intention has been to keep those separate, but to implement one
of the eager timings first, because it avoids implementing the
queue and scheduling in the same patch.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers