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