Re: Materialized views WIP patch

On Tue, Mar 5, 2013 at 08:50:39AM +0000, Simon Riggs wrote:
> Its not a different feature, its what most people expect a feature
> called MV to deliver. That's not a matter of opinion, its simply how
> every other database works currently - Oracle, Teradata, SQLServer at
> least. The fact that we don't allow MVs to automatically optimize

Good points.

> queries is acceptable, as long as that is clearly marked in some way
> to avoid confusion, and I don't mean buried on p5 of the docs. What we
> have here is a partial implementation that can be improved upon over
> next few releases. I hope anyone isn't going to claim that
> "Materialized Views" have been implemented in the release notes in
> this release, because unqualified that would be seriously misleading
> and might even stifle further funding to improve things to the level
> already implemented elsewhere. Just to reiterate, I fully support the
> committing of this partial feature into Postgres in this release
> because it will be a long haul to complete the full feature and what
> we have here is a reasonable stepping stone to get there.
>
> Transactionally up-yo-date MVs can be used like indexes in the
> planner. The idea that this is impossible because of the permutations
> involved is somewhat ridiculous; there is much published work on
> optimising that and some obvious optimisations. Clearly that varies
> according to the number of MVs and the number of tables they touch,
> not the overall complexity of the query. The overhead is probably same
> or less as partial indexes, which we currently think is acceptable. In
> any case, if you don't wish that overhead, don't use MVs.

While you are right that automatically using materialized views is like
the optimizer choosing partial indexes, we actually already have
auto-selection of row-level materialized views with expression indexes
and index-only scans. When you do the insert or update, the indexed
function is called and the value stored in the index. If you later
query the function call, we can pull the value right from the index.
This, of course, is a very crude definition of materialized view, but it
seems relevant.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +