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