Re: Trying to pull up EXPR SubLinks - Mailing list pgsql-hackers

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;

Thanks

Richard