Re: Backend-internal SPI operations

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.

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 ...

regards, tom lane

Peter Eisentraut wrote:
> Jan Wieck writes:
>
> > Hmmm, don't know what you mean with that.
>
> If I define a view
>
> CREATE my_view AS SELECT a, b, c FROM my_table;
>
> and then do
>
> SELECT * FROM my_view;
>
> then it becomes
>
> SELECT * FROM (SELECT a, b, c FROM my_table);
>
> which would presumably be possible with the new query-tree.

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';

And then have a little more complex "my_view", maybe a join
with it's own WHERE clause.

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 #

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 #

Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> 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.

Actually, what I was envisioning was pulling the subselect's guts *up*
into the main query (collapsing out the sub-Query node) if the sub-Query
is simple enough --- that is, no grouping/sorting/aggregates/etc. The
nice thing about that is we can start with a very simple method that
only deals with easy cases. The hard cases will still *work*.
I consider that an improvement over the current situation, where even
simple cases are nightmarishly difficult to implement (as you well know)
and the hard cases don't work. Worst case is that some
intermediate-complexity examples might lose performance for a while
until we build up a smart subquery-merging algorithm, but that seems
a price worth paying.

> Then again, let's get 7.1 out as is

Has the release schedule moved up without my knowing about it?
I don't feel any urgent need to freeze development now...

> and do the full querytree
> redesign for 7.2. It looks easy, but I fear it's more or less
> like an iceberg.

The original reason for this effort was to do a trial balloon that would
give us more knowledge about how to do it right for 7.2. The more I get
into it, the more I realize what a good idea that was. I'm not sure how
much of what I'm doing now will be completely discarded in the 7.2
cycle, but I do know that I understand the issues a lot better than
I did a week ago...

regards, tom lane