Re: Backend-internal SPI operations

Tom Lane wrote:
> Jan Wieck <janwieck@Yahoo.com> writes:
> >     Hmm  -  too  simple  - real life is harder. So to what do you
> >     expand the query
>
> >         SELECT a, c, d FROM my_view, other_table
> >             WHERE my_view.a = other_table.a
> >             AND other_table.x = 'foo';
>
>         SELECT a, c, d
>        FROM (SELECT a, b, c FROM my_table) AS my_view, other_table
>             WHERE my_view.a = other_table.a
>             AND other_table.x = 'foo';
>
> I'm still not detecting a problem here ... if selecting from a view
> *doesn't* act exactly like a sub-SELECT, it'd be broken IMHO.
   I  do.  The  qualification does not restrict the subselect in   any way.  So it'll be a sequential scan - no?
   Imagine my_table  has  10,000,000  rows  and  other_table  is   small.   With  an index on my_table.a and the
rewritingwe do   today there's a good chance to end up with index  lookups  in   my_table for all the other_table
matchesof x = 'foo'.
 
   Of  course,  after all the view must behave like a subselect.   But please only logical - not physical!
   So the hard part of the NEW rewriter will be to detect  which   qualifications   can  be  moved/duplicated  down
into which   subselects (tuple sources) to restrict scans.
 

> We're not that far away from being able to do this, and it looks more
> attractive to work on that than to hack the rewriter into an even
> greater state of unintelligibility ...
   Then again, let's get 7.1 out as is and do the full querytree   redesign for 7.2. It looks easy, but I fear it's
moreor less   like an iceberg.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #