Re: Postgresql Materialized views

Tom Lane wrote:
> Well, my point is that taking automatic rewriting as a required feature
> has at least two negative impacts:
> 
> * it rules out any form of lazy update, even though for many applications
> an out-of-date summary view would be acceptable for some purposes;
> 
> * requiring MVCC consistency will probably hugely reduce the variety of
> views that we can figure out how to materialize, and cost performance
> even for the ones we can do at all.
> 
> It's not zero-cost, even if you consider implementation effort and
> complexity as free (which I don't).

There is one big additional advantage of automatic rewriting though, I 
believe. If we had the infrastructure to recognize that possibility of 
using a predefined (materialized) view for executing a query, we can 
also use that infrastructure to get implement a kind of optimizer hints.

How? We'd need "statistics-materialized views", which don't materialize 
the full result of the view, but instead compute it's statistical 
properties (the same which ANALYZE computes for a table). When planning 
a query we can then substitute the guessed values for rowcount and 
friends of a subplan by the values computed for the corresponding 
statistics-materialized view.

However, until someone figures out *how* to *actually* recognize that 
possibility of using a MV for executing a query, this is just 
hand-wavering of course...

regards, Florian Pflug