Peter Eisentraut <peter_e@gmx.net> writes:
> then it becomes
> SELECT * FROM (SELECT a, b, c FROM my_table);
> which would presumably be possible with the new query-tree.

Right, that's exactly how we've been planning to fix the problems
with grouped views and so forth.

I am beginning to think that this may have to happen for 7.1, else
view inside ISO-style JOIN constructs aren't going to work right.
Further news as it happens...

			regards, tom lane

Fri, 01 Sep 2000 09:28:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Backend-internal SPI operations 
Jan Wieck <janwieck@Yahoo.com> writes:
>     I  suggest  let's  get  what  we have out of the door now and
>     don't work under pressure on these complex things.

Pressure?  I've got a month, I thought.  Should be plenty of time.

			regards, tom lane

Fri, 01 Sep 2000 18:02:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Backend-internal SPI operations 
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.

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

Fri, 01 Sep 2000 18:08:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Backend-internal SPI operations 
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@Yahoo.com #



Fri, 1 Sep 2000 17:19:01 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Backend-internal SPI operations
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > then it becomes
> > SELECT * FROM (SELECT a, b, c FROM my_table);
> > which would presumably be possible with the new query-tree.
>
> Right, that's exactly how we've been planning to fix the problems
> with grouped views and so forth.
>
> I am beginning to think that this may have to happen for 7.1, else
> view inside ISO-style JOIN constructs aren't going to work right.
> Further news as it happens...

    You really want to start on that NOW?

    I  suggest  let's  get  what  we have out of the door now and
    don't work under pressure on these complex things.


Jan

--

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



Fri, 1 Sep 2000 17:21:24 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
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 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@Yahoo.com #



Fri, 1 Sep 2000 18:36:59 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Backend-internal SPI operations
Jan Wieck <janwieck@yahoo.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

Sat, 02 Sep 2000 00:10:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Backend-internal SPI operations 