Re: Backend-internal SPI operations

Tom Lane wrote:
> Jan Wieck <janwieck(at)Yahoo(dot)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 rewriting we do
today there's a good chance to end up with index lookups in
my_table for all the other_table matches of 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 more or 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(at)Yahoo(dot)com #