On 6/7/26 20:57, Tom Lane wrote: > Tomas Vondra <[email protected]> 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
