Re: why doesn't optimizer can pull up where a > ( ... )

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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 19:18:19
Message-ID: 20191120191819.wkr7dcd4kerddzxj@development
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Wed, Nov 20, 2019 at 12:36:50PM -0500, Tom Lane wrote:
>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.

True. I'm not really sure how exactly would the planning logic work or
how Greenplum does it. It might be the case that based on the use cases
they target they simply assume the rewritten query is the right one in
99% of the cases, so they do the transformation always. Not sure.


Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Joe Conway 2019-11-20 19:30:12 Re: add a MAC check for TRUNCATE
Previous Message Alexey Kondratov 2019-11-20 18:16:48 Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly