Re: Materialized views WIP patch

On 3 March 2013 23:39, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> writes:
>> 2013/3/3 Kevin Grittner <kgrittn(at)ymail(dot)com>:
>>> Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com> wrote:
>>>> I think that automatically using materialized views even when the
>>>> query doesn’t mention them directly, is akin to automatically
>>>> using indexes without having to mention them in the query.
>
>>> Oh, I understand that concept perfectly well, I just wonder how
>>> often it is useful in practice.
>
> There's a much more fundamental reason why this will never happen, which
> is that the query planner is not licensed to decide that you only want
> an approximate and not an exact answer to your query.
>
> If MVs were guaranteed always up-to-date, maybe we could think about
> automatic use of them --- but that's a far different feature from what
> Kevin has here.

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
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.

Non-transactionally up-to-date MVs could also be used like indexes in
the planner, if we gave the planner the "licence" it (clearly) lacks.
If using MV makes a two-hour query return in 1 minute, then using an
MV that is 15 minutes out of date is likely to be a win. The "licence"
is some kind of user parameter/option that specifies how stale an
answer a query can return. For many queries that involve averages and
sums, a stale or perhaps an approximate answer would hardly differ
anyway. So I think there is room somewhere there for a "staleness"
time specification by the user, allowing approximation.

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