Re: Materialized views WIP patch

On 6 March 2013 14:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On 5 March 2013 22:02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> FWIW, my opinion is that doing anything like this in the planner is
>>> going to be enormously expensive.
>
>> As we already said: no MVs => zero overhead => no problem.
>
> Well, in the first place that statement is false on its face: we'll
> still spend cycles looking for relevant MVs, or at least maintaining a
> complexly-indexed cache that helps us find out that there are none in
> a reasonable amount of time. In the second place, even if it were
> approximately true it wouldn't help the people who were using MVs.

We can store info in the relcache, and reduce such a lookup to a
simple if test in the planner. Populating the cache would be easy
enough, approx same overhead as deriving list of constraints for the
relcache.

If you were using MVs, there are further heuristics to apply. MVs come
in various shapes, so we can assess whether they use aggregates,
joins, filters etc and use that for a general match against a query. I
don't see the need for complex assessments in every case.

>> It costs in
>> the cases where time savings are possible and not otherwise.
>
> And that is just complete nonsense: matching costs whether you find a
> match or not. Could we have a little less Pollyanna-ish optimism and
> a bit more realism about the likely cost of such a feature?

It's not a trivial feature; this is a lot of work. But it can be done
efficiently, without significant effect on other workloads. If that
really were to be true, then enable_lookaside = off can be the
default, just as we have for another costly planning feature,
constraint_exclusion.

Matview lookaside is the next-best-action for further work on the
planner, AFAICS. Correctly optimised query parallelism is harder,
IMHO.

What I'm hearing at the moment is "please don't make any changes in my
area" or "don't climb the North face". Considering the rather high bar
to being able to do this effectively, I do understand your interest in
not having your/our time wasted by casual attempts to approach the
problem, but I don't want to slam the door on a serious attempt (2
year project, 1+ man year effort).

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services