Hi All, Currently we will not consider EXPR_SUBLINK when pulling up sublinks and this would cause performance issues for some queries with the form of: 'a > (SELECT agg(b) from ...)' as described in [1].
So here is a patch as an attempt to pull up EXPR SubLinks. The idea, which is based on Greenplum's implementation, is to perform the following transformation. For query: select * from foo where foo.a > (select avg(bar.a) from bar where foo.b = bar.b); we transform it to: select * from foo inner join (select bar.b, avg(bar.a) as avg from bar group by bar.b) sub on foo.b = sub.b and foo.a > sub.avg; To do that, we recurse through the quals in sub-select and extract quals of form 'foo(outervar) = bar(innervar)' and then according to innervars we make new SortGroupClause items and TargetEntry items for sub-select. And at last we pull up the sub-select into upper range table. As a result, the plan would change as: FROM QUERY PLAN ---------------------------------------- Seq Scan on foo Filter: ((a)::numeric > (SubPlan 1)) SubPlan 1 -> Aggregate -> Seq Scan on bar Filter: (foo.b = b) (6 rows) TO QUERY PLAN -------------------------------------------------- Hash Join Hash Cond: (foo.b = bar.b) Join Filter: ((foo.a)::numeric > (avg(bar.a))) -> Seq Scan on foo -> Hash -> HashAggregate Group Key: bar.b -> Seq Scan on bar (8 rows) The patch works but still in draft stage. Post it here to see if it is the right thing we want. [1] https://www.postgresql.org/message-id/flat/CAKU4AWodctmbU%2BZj6U83y_RniQk0UeXBvKH1ZaJ%3DLR_iC90GOw%40mail.gmail.com Thanks Richard
v1-0001-Draft-PR-for-pulling-up-EXPR_SUBLINK.patch
Description: Binary data