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@debis.com (Jan Wieck) #