Re: Pushing down a subquery relation's ppi_clauses, and more ...

On 26/7/2025 05:09, Richard Guo wrote:
> Here, I'd like to discuss whether it's worthwhile to also consider
> pushing down a subquery relation's ppi_clauses if the subquery is
> LATERAL.
In my opinion, this direction makes sense. Moreover, I have seen 
sophisticated cases where SQL Server pushes parameterisation through 
GROUP BY down into a subquery, significantly speeding up execution.

> First, it's important to note that pushing down ppi_clauses doesn't
> always result in a better execution plan.  While doing so can reduce
> the amount of data processed in each aggregation invocation within the
> subquery, it also means that the aggregation needs to be re-evaluated
> for every outer tuple.  If t1 is very small and t2 is large, pushing
> down ppi_clauses can be a win.  As t1 gets larger, this gets less
> attractive, and eventually it will have a higher cost than the current
> plan, where the aggregation is evaluated only once.
Heh, let me propose a way to mitigate the issue I implemented in the 
Postgres fork. Instead of implementing numerous 'subplan flattening' 
transformations, I found that we can smooth the performance cliff by 
inserting a Memoise node at the top of the subplan. It reduces subplan 
evaluations in case we have duplicated parameter values.
It is designed close to the subplan hashing feature, but, of course, 
logically distinct: it requires a top-down step after the bottom-up 
planning. It has some limitations, but if you have the resources to 
restructure the planning procedure slightly, it may be feasible in the 
Postgres core as well.

> 
> Therefore, if we decide to pursue this approach, we would need to
> generate two paths: one with the ppi_clauses pushed down, and one
> without, and then compare their costs.  A potential concern is that
> this might require re-planning the subquery twice, which could
> increase planning overhead.
Here, we also designed an approach that may provide some insights for 
the further core development.
Correlated subquery pull-up techniques always have bad corner cases 
(like you proposed). We added an extension list field to PlannerGlobal 
and PlannedStmt, enabling features to report the upper-level caller. The 
caller may build a series of plans with and without these contradictory 
features applied and compare the costs.
I implemented the 'plan probing' technique in the GetCachedPlan, which 
is obviously has the most chances to be profitable because it is reused 
multiple times and has the infrastructure to track previous planning 
efforts. At the high architectural level, it seems close to the current 
plan cache auto mode logic: try options, compare costs, and remember 
decisions.

I'm not sure it provides any answers - just existing techniques to ponder.

-- 
regards, Andrei Lepikhov