Обсуждение: pgsql/src/test/regress/expected (union.out)

  Date: Thursday, October  5, 2000 @ 15:11:39
Author: tgl

Update of /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected
     from hub.org:/home/projects/pgsql/tmp/cvs-serv22406/src/test/regress/expected

Modified Files:
    union.out

-----------------------------  Log Message  -----------------------------

Reimplementation of UNION/INTERSECT/EXCEPT.  INTERSECT/EXCEPT now meet the
SQL92 semantics, including support for ALL option.  All three can be used
in subqueries and views.  DISTINCT and ORDER BY work now in views, too.
This rewrite fixes many problems with cross-datatype UNIONs and INSERT/SELECT
where the SELECT yields different datatypes than the INSERT needs.  I did
that by making UNION subqueries and SELECT in INSERT be treated like
subselects-in-FROM, thereby allowing an extra level of targetlist where the
datatype conversions can be inserted safely.
INITDB NEEDED!

* Tom Lane <tgl@hub.org> [001005 12:12] wrote:
>   Date: Thursday, October  5, 2000 @ 15:11:39
> Author: tgl
>
> Update of /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected
>      from hub.org:/home/projects/pgsql/tmp/cvs-serv22406/src/test/regress/expected
>
> Modified Files:
>     union.out
>
> -----------------------------  Log Message  -----------------------------
>
> Reimplementation of UNION/INTERSECT/EXCEPT.  INTERSECT/EXCEPT now meet the
> SQL92 semantics, including support for ALL option.  All three can be used
> in subqueries and views.  DISTINCT and ORDER BY work now in views, too.
> This rewrite fixes many problems with cross-datatype UNIONs and INSERT/SELECT
> where the SELECT yields different datatypes than the INSERT needs.  I did
> that by making UNION subqueries and SELECT in INSERT be treated like
> subselects-in-FROM, thereby allowing an extra level of targetlist where the
> datatype conversions can be inserted safely.
> INITDB NEEDED!

Does this mean that in the next release EXCEPT will be a lot faster?
Will I probably be able to drop my "NOT EXISTS" hacks that I've
been using?

thanks,
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Alfred Perlstein <bright@wintelcom.net> writes:
>> Reimplementation of UNION/INTERSECT/EXCEPT.

> Does this mean that in the next release EXCEPT will be a lot faster?
> Will I probably be able to drop my "NOT EXISTS" hacks that I've
> been using?

UNION/INTERSECT/EXCEPT are now all basically a sort phase and a
unique-filter phase, with minor variations on what the unique filter
thinks it should output.  So the cost should be O((M+N) log (M+N)) for
M+N input tuples, as opposed to O(M*N) for the old INTERSECT and
EXCEPT code.

I didn't do anything to change EXISTS ...

            regards, tom lane

* Tom Lane <tgl@sss.pgh.pa.us> [001005 14:32] wrote:
> Alfred Perlstein <bright@wintelcom.net> writes:
> >> Reimplementation of UNION/INTERSECT/EXCEPT.
>
> > Does this mean that in the next release EXCEPT will be a lot faster?
> > Will I probably be able to drop my "NOT EXISTS" hacks that I've
> > been using?
>
> UNION/INTERSECT/EXCEPT are now all basically a sort phase and a
> unique-filter phase, with minor variations on what the unique filter
> thinks it should output.  So the cost should be O((M+N) log (M+N)) for
> M+N input tuples, as opposed to O(M*N) for the old INTERSECT and
> EXCEPT code.
>
> I didn't do anything to change EXISTS ...

A while back I think you helped me, I was using EXCEPT, but the perf
was really awful:

* Tom Lane <tgl@sss.pgh.pa.us> [000510 16:22] wrote:
> Alfred Perlstein <bright@wintelcom.net> writes:
> > =# select ref_id from ref_old except select ref_id from ref_new;
> > Takes over 10 minutes, probably closer to half an hour.
> > I've also tried using 'NOT IN ( select ref_id from ref_new )'
>
> Yup.  EXCEPT is effectively translated to a NOT IN, if I recall
> correctly, and neither IN ( sub-select ) nor NOT IN ( sub-select )
> are implemented very efficiently.  Basically you get O(N^2) behavior
> because the inner select is rescanned for each outer tuple.
>
> We have a TODO list item to try to be smarter about this...
>
> > Is there a way to formulate my SQL to get Postgresql to follow
> > this algorithm [ kind of like a mergejoin ]
>
> No, but you could try
>
> select ref_id from ref_old where not exists
> (select ref_id from ref_new where ref_id = ref_old.ref_id);
>
> which would at least be smart enough to consider using an index
> on ref_new(ref_id) instead of a sequential scan.

Is this what you fixed?

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Alfred Perlstein <bright@wintelcom.net> writes:
> Is this what you fixed?

Yes.

            regards, tom lane

* Tom Lane <tgl@sss.pgh.pa.us> [001005 14:42] wrote:
> Alfred Perlstein <bright@wintelcom.net> writes:
> > Is this what you fixed?
>
> Yes.

Awesome, thanks!