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

Daniel Gustafsson <daniel(at)yesql(dot)se> writes:
>> On 20 Nov 2019, at 13:15, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>> 2. why pg can't do it, while greenplum can?

> It's worth noting that Greenplum, the example you're referring to, is using a
> completely different query planner, and different planners have different
> characteristics and capabilities.

Yeah. TBH, I think the described transformation is well out of scope
for what PG's planner tries to do. Greenplum is oriented to use-cases
where it might be worth spending lots of planner cycles looking for
optimizations like this one, but in a wider environment it's much
harder to make the argument that this would be a profitable use of
planner effort. I'm content to say that the application should have
written the query with a GROUP BY to begin with.

Having said that, the best form of criticism is a patch. If somebody
actually wrote the code to do something like this, we could look at
how much time it wasted in which unsuccessful cases and then have
an informed discussion about whether it was worth adopting.

(BTW, I do not think the transformation as described is even formally
correct, at least not without some unstated assumptions. How is it
okay to push down the "p_size > 40" condition into the subquery?
The aggregation in the original query will include rows where that
isn't true.)

regards, tom lane