Обсуждение: 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

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

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

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