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/[email protected]/msg219224.html [2] https://www.mail-archive.com/[email protected]/msg33102.html [3] https://www.mail-archive.com/[email protected]/msg180151.html -- regards, Andrei Lepikhov, pgEdge
exp1.sql
Description: application/sql
