Re: Subquery pull-up increases jointree search space

On 6/7/26 20:57, Tom Lane wrote:
> Tomas Vondra <tomas(at)vondra(dot)me> writes:
>> I was wondering about which places allocate most memory during planning.
>> I selected a query that allocates a manageable amount (~2.5GB) to plan,
>> and added some instrumentation to trace alloc/free calls for the
>> PortalContext (which is the context used by the planner).
>
> Thanks for doing this work, very interesting.
>
>> After classifying the allocations a bit, I see this:
>
>> allocation | size
>> --------------------------------------+----------------
>> find_mergeclauses_for_outer_pathkeys | 671 MB
>> generate_join_implied_equalities | 660 MB
>> add_paths_to_joinrel | 242 MB
>> make_inner_pathkeys_for_merge | 195 MB
>> estimate_num_groups | 92 MB
> ...
>
> I experimented with the attached trivial patch, which just avoids
> leaking sublists within find_mergeclauses_for_outer_pathkeys and
> generate_join_implied_equalities. I did not bother with adding
> any measurement infrastructure, just watched the process's virtual
> size with top(1). What I see is that HEAD consumes about 2.6GB
> and this patch gets it down to 1.8GB. So we could move the needle
> noticeably just by not wasting memory we don't have to.
>

Thanks. I've tried the fix on the original query, and I confirm the
memory usage reported by log_planner_stats drops from ~2.7GB to ~1.8GB,
so quite a bit. I assume your numbers were from the same query, so this
aligns with your numbers from 'top'.

I also tried with a larger 13-table join, using ~17GB of memory. The fix
gets it to ~13GB, so it saves ~30% in both cases (the trace log has nice
1.2TB in this case ...).

I haven't measured how this affect plan time, but I can't imagine it'd
make it worse. If anything, it'll reduce the number of page faults -
which can be quite expensive.

So +1 to do this.

Of course, if we want to consider increasing join_collapse_limit, we'd
need to reduce memory usage further. Also, I wonder which other join
features (LATERAL, partition-wise joins, ...) might use significant
amounts of memory. The queries generated by the script are rather basic.
I suppose we might have to impose somewhat stricter rules about freeing
memory during planning, etc.

regards

--
Tomas Vondra