On Tue, 5 Apr 2022 at 07:40, Arne Roland <a.rol...@index.de> wrote: > can someone point out to me, why we don't consider pushdowns of the joinqual > for these queries beyond the distinct on? > > When the qual matches the distinct clause, it should be possible to generate > both parametrized and non parametrized subplans for the same query. The same > should hold true for aggregates, if the group by clause matches. Is there any > specific reason we aren't doing that already?
Your example shows that it's not always beneficial to pushdown such quals. In all cases where we currently consider qual pushdowns, we do so without any costing. This is done fairly early in planning before we have any visibility as to if it would be useful or not. With your example case, if we unconditionally rewrote the subquery to be laterally joined and pushed the condition into the subquery then we could slow down a bunch of cases as the planner would be forced into using a parameterized nested loop. I don't really see how we could properly cost this short of performing the join search twice. The join search is often the most costly part of planning. When you consider that there might be many quals to push and/or many subqueries to do this to, the number of times we'd need to perform the join search might explode fairly quickly. That wouldn't be great for queries where there are many join-levels to search. It might be possible if we could come up with some heuristics earlier in planning to determine if it's going to be a useful transformation to make. However, that seems fairly difficult in the absence of any cardinality estimations. David
