Re: Subquery pull-up increases jointree search space

On 07/06/2026 20:10, Tomas Vondra wrote:
> On 6/5/26 12:43, Tomas Vondra wrote:
> 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)

It is fun to use the benchmark script from the previous research on this topic
[1], conducted in 2009. Query, consuming a lot of memory and planning time [2],
now shows the following numbers:

join_collapse_limit = 12: Memory: used=0.8GB, Time: 2.5s
join_collapse_limit = 14: Memory: used=1.1GB, Time: 5.5s
join_collapse_limit = 18: Memory: used=9.3GB, Time: 50.6s

It's interesting to compare these results with your memory consumption profile.
I tried using the memtrace patch and the Python script directly, but they gave
unusual output on my machine, so I couldn't rely on them without more detailed
instructions. Instead, I used the heaptrack tool, which gave me the following
profiles:

bytes pct function
collapse limit = 14:
354.48 MB 29.3% get_relation_foreign_keys
109.07 MB 9.0% generate_join_implied_equalities_normal
75.51 MB 6.2% get_eclass_indexes_for_relids
67.12 MB 5.5% find_mergeclauses_for_outer_pathkeys
67.12 MB 5.5% create_nestloop_path
58.73 MB 4.9% make_inner_pathkeys_for_merge
38.27 MB 3.2% expression_tree_mutator_impl
33.56 MB 2.8% hash_inner_and_outer
collapse limit = 18:
1.23 GB 12.8% generate_join_implied_equalities_normal
1.11 GB 11.5% get_joinrel_parampathinfo
989.90 MB 10.3% get_eclass_indexes_for_relids
755.04 MB 7.9% find_mergeclauses_for_outer_pathkeys
721.44 MB 7.5% calc_nestloop_required_outer
721.43 MB 7.5% get_param_path_clause_serials
629.15 MB 6.5% bms_intersect
578.81 MB 6.0% have_unsafe_outer_join_ref
411.08 MB 4.3% make_inner_pathkeys_for_merge
354.48 MB 3.7% get_relation_foreign_keys
335.58 MB 3.5% create_nestloop_path
301.99 MB 3.1% generate_join_implied_equalities

Overall, the results for generate_join_implied_equalities and
find_mergeclauses_for_outer_pathkeys are consistent. This test also highlights
other sources of memory allocations, such as parameterised paths. The memory
profile changes as the number of joins in the 'join problem' increases.

[1]
https://www.postgresql.org/message-id/flat/603c8f070907062230v169541b0ka5a939de1132fd5c%40mail.gmail.com
[2] Test
https://www.postgresql.org/message-id/200907091700.43411.andres%40anarazel.de

--
regards, Andrei Lepikhov,
pgEdge