Hello Richard,
Currently for quals in the form of "NOT NOT (SubLink)", this SubLink would not
be considered when pulling up sublinks. For instance:
gpadmin=# explain select * from a where NOT NOT (a.i in (select b.i from b));
QUERY PLAN
-------------------------------------------------------------
Seq Scan on a (cost=51.50..85.62 rows=1005 width=8)
Filter: (hashed SubPlan 1)
SubPlan 1
-> Seq Scan on b (cost=0.00..44.00 rows=3000 width=4)
(4 rows)
Should we give it a chance, like the attached does?
Sometimes hashed subplan is faster than hash join and than all the other options, as it preserves the order.
Using NOT NOT, one can control whether to use it or not:
https://pgblog.bashtanov.com/2017/12/08/double-negative-and-query-performance/ (test case and results in the bottom of the page).
Surely dirty tricks should not be the way to control the planner, but when breaking them we should probably provide a way to achieve the same result,
ideally making the planner choose the best plan without hints.
Best,
Alex