Re: [POC] Allow flattening of subquery with a link to upper query

On 1/9/2022 19:24, Richard Guo wrote:
> Even if we ignore these assertion checks, in the final plan we would
> have to access the RHS of the B/C semi join, i.e. C, to evaluate qual
> 'c.j = a.j' at the join level of A/BC join, which is wrong.
Having committed 9f13376396 recently, we did a lot of work in this area.
By applying regression tests from my last patch [1] to the master, I
compared these two implementations.
As I see, using the LATERAL trick allowed us to simplify the code
drastically. But because we know just a fact of the lateral link, not
its place, in the master we do less when in the patch proposed in that
thread. For example, having query:

explain (costs off)
SELECT relname FROM pg_class c1
WHERE relname = ANY (
SELECT a.amname from pg_am a WHERE a.oid=c1.oid GROUP BY a.amname
);

We see on master:
Nested Loop
-> Seq Scan on pg_class c1
-> Subquery Scan on "ANY_subquery"
Filter: (c1.relname = "ANY_subquery".amname)
-> Group
Group Key: a.amname
-> Sort
Sort Key: a.amname
-> Seq Scan on pg_am a
Filter: (oid = c1.oid)

And with this patch:
Hash Join
Hash Cond: ((c1.relname = a.amname) AND (c1.oid = a.oid))
-> Seq Scan on pg_class c1
-> Hash
-> HashAggregate
Group Key: a.amname
-> Seq Scan on pg_am a

Also, we attempted to fix links from a non-parent query block.
So, in my opinion, the reason for this patch still exists, and we can
continue this work further, maybe elaborating on flattening LATERAL
references - this needs some research.

[1]
https://www.postgresql.org/message-id/35c8a3e8-d080-dfa8-2be3-cf5fe702010a%40postgrespro.ru

--
regards,
Andrei Lepikhov
Postgres Professional