Обсуждение: uncorrelated subqueries

Hello,
 In v6.4.2 do uncorrelated subqueries get run on each iteration of the
outer loop?  For instance,

SELECT a
FROM     table1
WHERE  table1.b=XX    AND   table1.c  IN ( SELECT d  FROM  table2 WHERE table2.e=YY )
;

Does the subquery get run for each row from table 1 where table1.b = XX
is true?

Bruce Lowery
edventions.com

Bruce Lowery <bruce.lowery@edventions.com> writes:
>   In v6.4.2 do uncorrelated subqueries get run on each iteration of the
> outer loop?

It looks like the particular example you give is done that way, but the
system does know about uncorrelated subqueries.  For example, using 6.5:

explain SELECT a FROM table1 WHERE table1.b=33 AND
table1.c  = ( SELECT d  FROM  table2 WHERE table2.e=44);
NOTICE:  QUERY PLAN:
Seq Scan on table1  (cost=43.00 rows=1 width=4) InitPlan   ->  Seq Scan on table2  (cost=43.00 rows=1 width=4)

explain SELECT a FROM table1 WHERE table1.b=33 AND
table1.c  = ( SELECT d  FROM  table2 WHERE table2.e=table1.a);
NOTICE:  QUERY PLAN:
Seq Scan on table1  (cost=43.00 rows=1 width=4) SubPlan   ->  Seq Scan on table2  (cost=43.00 rows=1 width=4)

You can see that we get an "InitPlan" (ie, run once) for an uncorrelated
subquery but a "SubPlan" (repeat each time) for a correlated one.
Unfortunately, the case you care about is:

explain SELECT a FROM table1 WHERE table1.b=33 AND
table1.c  IN ( SELECT d  FROM  table2 WHERE table2.e=44);
NOTICE:  QUERY PLAN:
Seq Scan on table1  (cost=43.00 rows=1 width=4) SubPlan   ->  Seq Scan on table2  (cost=43.00 rows=1 width=4)

The main problem that would have to be solved to convert this to
an InitPlan is what to do if the subselect returns a huge number
of tuples ... with the current implementation, since we scan the
tuples one at a time, there's no problem, but if we try to store
all the tuples we could run out of memory.
        regards, tom lane

At 22:30 +0300 on 08/07/1999, Tom Lane wrote:


> The main problem that would have to be solved to convert this to
> an InitPlan is what to do if the subselect returns a huge number
> of tuples ... with the current implementation, since we scan the
> tuples one at a time, there's no problem, but if we try to store
> all the tuples we could run out of memory.

How about putting them in a temporary table if the number of returned
tuples is big? Although the scan on the temp table will be sequential, it's
still not the same as scanning the original table (checking conditions and
perhaps even joining). If the internal query is ran on a 3 million record
table, and returns a million of them, it's worthwhile storing in a temp
table.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma