Re: Materialized views WIP patch

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Mar 5, 2013 at 7:15 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>> I don't think I disagree with any of what Simon says other than his
>> feelings about the planning cost.

> I'm not sure I agree. Suppose you have a query like SELECT * FROM a
> INNER JOIN b ON a.x = b.x INNER JOIN c ON a.y = c.y WHERE <some
> stuff>. The query planner will construct paths for scans on a, b, and
> c. Then it will construct joinrels for (a b), (a c), (b c), and
> eventually (a b c) and calculate a set of promising paths for each of
> them. If there is a materialized view available for one of those
> joinrels, all we really need to do is add the possible paths for
> scanning that materialized view to the joinrel.

That only works to the extent that a materialized view can be described
by a path. My impression is that most of the use-cases for MVs will
involve aggregates or similar data reduction operators, and we don't
currently implement anything about aggregates at the Path level.
Arguably it would be useful to do so; in particular, we could get rid
of the currently hard-wired mechanism for choosing between sorted and
hashed aggregation, and perhaps there'd be a less grotty way to deal
with index-optimized MIN/MAX aggregates. But there's a great deal to do
to make that happen, and up to now I haven't seen any indication that it
would do much except add overhead.

FWIW, my opinion is that doing anything like this in the planner is
going to be enormously expensive. Index matching is already pretty
expensive, and that has the saving grace that we only do it once per
base relation. Your sketch above implies trying to match to MVs once
per considered join relation, which will be combinatorially worse.
Even with a lot of sweat over reducing the cost of the matching, it
will hurt.

> All that having been said, it's hard for me to imagine that anyone
> really cares about any of this until we have an incremental update
> feature, which right now we don't.

Agreed. Even if we're willing to have an "approximate results are OK"
GUC (which frankly strikes me as a horrid idea), people would certainly
not be willing to turn it on without some guarantee as to how stale the
results could be.

regards, tom lane