Thread: RE: [SQL] Joining bug????

>     BTW:  You  might want to take a look at the new documentation
>     of the rule system to understand how views work in detail.
>
I think I'll do that.  I'd love to be able to help you for the rule
rewrite for v6.5 .

>     It could only be the parse stage. After the  rule  system  is
>     through  with the query, the parsetree given to the optimizer
>     is exactly the same  as  if  you  typed  in  the  query  that
>     accesses the real tables.  Having a view
>
>         CREATE VIEW v1 AS SELECT t1.a, t2.b FROM t1, t2
>             WHERE t1.a = t2.a;
>
>     and doing a
>
>         SELECT * FROM v1 WHERE a = 'something';
>
>     is 100% the same as
>
>         SELECT t1.a, t2.b FROM t1, t2
>             WHERE t1.a = 'something'
>               AND t1.a = t2.a;
>
>     The  optimizer  will  get exactly the same parsetree and will
>     generate the same plan then. So the timing difference  cannot
>     be in the optimizer or executor.
>
>     But  it's  interesting.  I'll  do some tests on it and try to
>     find out whether parsing of complicated WHERE clauses is that
>     costly  that  it  can be the reason. Seems the rule system is
>     quicker than the parser :-)
>
What kind of speed increases are people seeing in their queries?
I've actually never created a view in PostgreSQL (say he with head hung
low).


> Jan
    -DEJ

> >     But  it's  interesting.  I'll  do some tests on it and try to
> >     find out whether parsing of complicated WHERE clauses is that
> >     costly  that  it  can be the reason. Seems the rule system is
> >     quicker than the parser :-)
> >
> What kind of speed increases are people seeing in their queries?
> I've actually never created a view in PostgreSQL (say he with head hung
> low).

    Used  a  view that is a join of 5 tables. There is absolutely
    no difference between using the view or the equivalent select
    from the tables directly in what -s prints as times.

    There   must  be  something  else  that  causes  the  speedup
    reported.  Maybe another ordering in the qualifications  when
    using  the  view.   The  view  qual's  are  added last to the
    original queries qual. Putting them the other  way  round  in
    the query might produce another join order.


Jan

--

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