Re: AW: [HACKERS] create rule changes table to view ?

Andreas Zeugswetter wrote:

>
>
> > > For thing's like aggregates, distinct/grouping and the like,
> > > we need to take a step backward and really do some kind of
> > > view materialization (create a real execution path for the
> > > view's definition). But don't force that to be done whenever
> > > a view is used - that doesn't make things better.
> >
> > Thanks. Now I understand why aggregates cause problems with rules.
> >
> Couldn't all views be expressed with the rule system, if we had subselects
> in the
> from clause ? This would be useful for other SQL too. RDB has this e.g.

I hope so,

because the FROM clause is what I (thinking in querytrees)
usually call the rangetable. After parsing, all relations
(tables and views - the parser doesn't care) the user
mentioned in his query appear in the querytree as RTE's
(Range Table Entries).

On a first thought it looks simple to just add another Node
pointer to the RTE structure and if a view has something that
requires materialization just throw it's querytree from
pg_rewrite into there. The planner then has to produce the
entire subtree for that as a left- or righttree for the
"relation".

The problem is just to decide which restrictions from the
WHERE clause could be taken down into this subselecting RTE
to reduce the amount of data the view materializes instead of
filtering them out later.

Example:

CREATE VIEW v1 AS SELECT a, sum(b) FROM t1 GROUP BY a;

SELECT count(*) FROM v1 WHERE a < 10;

Let's assume now that t1 has a million rows but only a few
hundred that match a < 10. If we now materialize the view in
a subplan without telling a < 10, a seqscan over the entire
table plus sorting/grouping and summing would happen instead
of fetching the few tuples by index and then sort/group/sum.

The opposite:

CREATE VIEW v2 AS SELECT a, sum(c) FROM t2 GROUP BY a;

SELECT v1.a FROM v1, v2 WHERE v1.a = v2.a AND v1.b = v2.c;

This time there is no chance - we ask for comparision of two
aggregates of different views. The WHERE clause here can only
be evaluated after both views have completely been
materialized.

> I do not beleive, that Stonebraker had an incomplete Rule System in mind.

At least his concept is expandable to meet our needs. An
expandable concept is never really incomplete as long as it
never leaves the drawing board :-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #