Re: pushdown of joinquals beyond group by/distinct on

On Tue, 5 Apr 2022 at 07:40, Arne Roland <A(dot)Roland(at)index(dot)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