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

From Andy Fan
Subject Re: Trying to pull up EXPR SubLinks
Date
Msg-id CAKU4AWoQ=SWSwQacjQf99cEdZXgHYLpw7iQMmAiERmE9Z3uuWw@mail.gmail.com
Whole thread Raw
In response to Trying to pull up EXPR SubLinks  (Richard Guo <guofenglinux@gmail.com>)
Responses Re: Trying to pull up EXPR SubLinks
List pgsql-hackers


On Fri, Feb 28, 2020 at 2:35 PM Richard Guo <guofenglinux@gmail.com> wrote:
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;

Glad to see this.  I think the hard part is this transform is not *always* 
good.  for example foo.a only has 1 rows, but bar has a lot  of rows, if so 
the original would be the better one.  doss this patch consider this problem? 


Thanks
Richard

pgsql-hackers by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Make mesage at end-of-recovery less scary.
Next
From: Michael Paquier
Date:
Subject: Re: Assert failure due to "drop schema pg_temp_3 cascade" fortemporary tables and \d+ is not showing any info after drooping temp tableschema