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 #
