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.
Thanks
Richard