Re: Subquery pull-up increases jointree search space

On 6/5/26 12:43, Tomas Vondra wrote:
>
> ...
>
> So to allow increasing join_collapse_limit, we'd need to address this,
> somehow. I have not investigated what exactly uses the memory. Some of
> it may be easy to free, but I recall we have difficulties with freeing
> some of the stuff because we don't know if it's still referenced.
>

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).

Attached are two SQL scripts with the schema and a join query with 12
tables (it increases the join_collapse_limit and disables geqo).

There's also .patch adding the alloc/free trace messages, and a .py
script to aggregate the log messages, and an example summary of the
allocations after the 2.5GB query. For each allocation we know the
backtrace, and so can aggregate allocations per "backtrace".

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
get_joinrel_parampathinfo | 90 MB
create_memoize_path | 48 MB
| 15 MB
calc_nestloop_required_outer | 5226 kB
(9 rows)

This sums to ~2.1GB. It does not include chunk headers etc. so the
actuam memory usage is somewhat higher - accounting for that, it roughly
aligns with the memory context stats and total memory usage reported by
log_planner_stats.

It surprised me ~1/3 of the memory comes from find_mergeclauses, and
another ~1/3 from generating implied equalities. I'm not very familiar
with that code, but I guess it's generating entries over and over,
without freeing them even if unused. I wonder if we can free them? Or is
it unclear if an item is used? Maybe we could a least deduplicate them
in some way? Surely there's not 600MB worth of unique equalities?

regards

--
Tomas Vondra