From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Cc: | Daniel Gustafsson <daniel(at)yesql(dot)se>, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: why doesn't optimizer can pull up where a > ( ... ) |
Date: | 2019-11-20 17:36:50 |
Message-ID: | 16205.1574271410@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> On Wed, Nov 20, 2019 at 11:12:56AM -0500, Tom Lane wrote:
>> I'm content to say that the application should have written the query
>> with a GROUP BY to begin with.
> I'm not sure I agree with that. The problem is this really depends on
> the number of rows that will need the subquery result (i.e. based on
> selectivity of conditions in the outer query). For small number of rows
> it's fine to execute the subplan repeatedly, for large number of rows
> it's better to rewrite it to the GROUP BY form. It's hard to make those
> judgements in the application, I think.
Hm. That actually raises the stakes a great deal, because if that's
what you're expecting, it would require planning out both the transformed
and untransformed versions of the query before you could make a cost
comparison. That's a *lot* harder to do in the context of our
optimizer's structure, and it also means that the feature would consume
even more planner cycles, than what I was envisioning (namely, a fixed
jointree-prep-stage transformation similar to subquery pullup).
I have no idea whether Greenplum really does it like that.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Alexey Kondratov | 2019-11-20 18:16:48 | Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly |
Previous Message | Tom Lane | 2019-11-20 17:27:56 | Re: Role membership and DROP |