Subquery pull-up increases jointree search space

Hi,

As I see on the mailing list, the number of proposals for different
subquery pull-up transformations is growing [1 - 3].

From time to time, I see user complaints on performance degradation
caused by newly introduced transformation - it is usually related to
correlated subplan transformation as well as trivial initplan → join
transformation.

The original issue (according to my case analysis) is usually that, by
adding such a pull-up optimiser excess join collapse limit. As a result,
the query tree tail, which was previously ordered according to the cost
model, is now determined mechanically, sometimes causing severe
degradation in execution time.

The attached example demonstrates how subquery pull-up can degrade
performance. Although not based on a real-world scenario, its primary
purpose is to illustrate the underlying concept.

I suppose it is mostly a rare case not worth cycles to manage. Still,
the core may at least provide a mechanism for users to decide what to do
on their own in case they have problems.

The most straightforward solution is to maintain simple statistics on
the number of flattened sublinks and relations, which may allow an
extension developer to build a sort of replanning infrastructure on top
of the planner_hook. Another way: do it in-core and rewrite
pull_up_sublinks to stop pulling subqueries up if the collapse limit has
already been reached.

Both approaches present significant challenges. Therefore, it would be
valuable to gather additional perspectives on this topic before proceeding.

[1]
https://www.mail-archive.com/pgsql-hackers(at)lists(dot)postgresql(dot)org/msg219224.html
[2]
https://www.mail-archive.com/pgsql-committers(at)lists(dot)postgresql(dot)org/msg33102.html
[3]
https://www.mail-archive.com/pgsql-hackers(at)lists(dot)postgresql(dot)org/msg180151.html

--
regards, Andrei Lepikhov,
pgEdge