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

> > > > 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
>
Would it be possible to make the executor reentrant for those
subqueries which couldn't be rewritten/resolved into the parent query.
If you took your second example above and store the results of v1
and v2 into temp tables v1_temp, v2_temp respectively, They could be used to
complete the query on another executor pass.
You wouldn't need to re-parse/optimize because you could simple
replace the sections of the RTE with the oids of the temp tables and then
execute. There wouldn't be any indexes to optimize upon so you could just
choose a join method (i.e. HASH) that would work best with the number of
rows that need to be sequentially scanned and/or sorted.
I think it would be dog slow but it would work for those cases.

I haven't thought through all of the possible cases but it appears
that the best case for combining is a single table single constraint
situation.
From your first example it's easy to see that the constant would be
taken into the subselect and since this leaves the outside query without any
constraining terms then see if you can just rewrite the select list to
perform the query without the subselect.

If you're willing to give me a fairly comprehensive query/view
combinations I'm willing to work out a strategy to resolve them all; I don't
know how efficient it will all be but I'll give it a whirl.

discussion can always be useful,
-DEJ