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