Re: pull-up subquery if JOIN-ON contains refs to upper-query

Hi Alena,

I reviewed v11-patches.

The 0001-patch LGTM; I have no further comments on it.

For the 0002-patch, I found a performance regression with independent
correlation keys:
```
CREATE TABLE po (x int);
CREATE TABLE pa (x int);
CREATE TABLE pc (x int, y int);

INSERT INTO po VALUES (1);
INSERT INTO pa VALUES (1);
INSERT INTO pc SELECT g, 1 FROM generate_series(1, 100000) g;

CREATE INDEX ON pc (y, x);
ANALYZE po;
ANALYZE pa;
ANALYZE pc;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT po.x, pa.x
FROM po LEFT JOIN pa
ON EXISTS (
SELECT 1 FROM pc
WHERE pc.y = pa.x AND pc.x = po.x
);
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop Left Join (actual rows=1.00 loops=1)
Join Filter: CASE WHEN (true) THEN ((pc.x = po.x) IS TRUE) ELSE false END
Rows Removed by Join Filter: 99999
Buffers: shared hit=887 dirtied=1 written=1
-> Seq Scan on po (actual rows=1.00 loops=1)
Buffers: shared hit=1
-> Nested Loop Left Join (actual rows=100000.00 loops=1)
Join Filter: (pc.y = pa.x)
Buffers: shared hit=886 dirtied=1 written=1
-> Seq Scan on pa (actual rows=1.00 loops=1)
Buffers: shared hit=1
-> HashAggregate (actual rows=100000.00 loops=1)
Group Key: pc.y, pc.x
Batches: 1 Memory Usage: 6937kB
Buffers: shared hit=885 dirtied=1 written=1
-> Seq Scan on pc (actual rows=100000.00 loops=1)
Buffers: shared hit=885 dirtied=1 written=1
Planning:
Buffers: shared hit=81 read=9
Planning Time: 1.169 ms
Execution Time: 36.584 ms
(21 rows)
```

Adding OFFSET 0 to the subquery retains the SubPlan:
```
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT po.x, pa.x
FROM po LEFT JOIN pa
ON EXISTS (
SELECT 1 FROM pc
WHERE pc.y = pa.x AND pc.x = po.x
OFFSET 0
);
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop Left Join (actual rows=1.00 loops=1)
Join Filter: EXISTS(SubPlan exists_1)
Buffers: shared hit=5
-> Seq Scan on po (actual rows=1.00 loops=1)
Buffers: shared hit=1
-> Seq Scan on pa (actual rows=1.00 loops=1)
Buffers: shared hit=1
SubPlan exists_1
-> Index Only Scan using pc_y_x_idx on pc (actual rows=1.00 loops=1)
Index Cond: ((y = pa.x) AND (x = po.x))
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3
Planning Time: 0.419 ms
Execution Time: 0.209 ms
(15 rows)
```

The fallback groups all 100 000 rows and rejects 99 999 through the
outer case, while SubPlan performs a single index-only search using
boh correlation keys.

How should we choose between these strategies? Currently, the fallback
is selected before path generation, so the original SubPlan is no
longer available for cost comparison.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/