Re: Subquery pull-up increases jointree search space

On 6/13/26 12:07, Andrei Lepikhov wrote:
> On 09/06/2026 12:43, Tomas Vondra wrote:
>> I kept poking at the patch releasing some transient sublists, trying to
>> make it release even more memory. Attached is a couple hacky patches
>> releasing memory in a couple more places:
>>
>> 0001 - Tom's patch releasing a couple transient lists
>>
>
> I used to think that list_concat() simply added a reference from sublist1 to
> sublist2, forgetting that it's actually an array underneath. I’m curious how
> many parts of the code are written with the same assumption.
>
> Let's check some of them:
>
> 1. result = list_concat(result, generate_join_implied_equalities(root, ...
> 2. pclauses = list_concat(pclauses, eqclauses);
> 3. qpquals = list_concat(extract_nonindex_conditions(path->indexinfo- ...
>
> in relnode.c, costsize.c looks like a good candidates too
>
> out_list = list_concat(out_list, ...
>
> in pull_ors / pull_ands also might be freed earlier.

Perhaps. But I don't think we need to worry about places invoked only
once (or very limited number of times) per query/plan. Which I think is
the case of pull_ors, pull_ands, qpquals, etc. Those do not matter.

It's the calls that happen for every path we consider/create, because if
there are many paths (and for large joins there can be very many) that
ends up using a lot of memory.

>> 0002 - releases a couple more transient lists in nearby functions
>>
>
> Also, quite a typical template. Maybe an automatic AI search might crawl the
> code and detect similar places? Maybe it makes sense to analyse and free also
> bitmapsets outer_and_req, inner_and_req, and real_outer_and_req? In case of
> partitioned tables, they might be quite massive.
>

Could be. It's certainly possible other queries or queries on
partitioned tables will consume a lot of memory in other places.

>> 0003 - release yet more transient lists in equivclass.c
>>
>
> Yes, EC memebers might be quite long lists
>
>> 0004 - release transient lists in estimate_num_groups
>>
>
> I'm not sure this is necessary. The grouping list is usually short, and cleaning
> varinfos seems like over-managing.
>

It's not just the question of how long the lists are. It's how often we
build them. In the example query we ended up with ~100MB used by these
things, and ~1/2 of that was the varinfo items.

>> 0005 - free lists used by mergejoin paths
>>
>
> Here, as well, I don't see the reason - pathkeys lists are quite short and
> truncate_useless_pathkeys reduces their nomenclature quite effectively
>
>> 0006 - free lists used by hahsjoin paths
>
> The same as above
>

I don't follow. I posted this table a couple messages back:

allocation | size
--------------------------------------+----------------
find_mergeclauses_for_outer_pathkeys | 671 MB
generate_join_implied_equalities | 660 MB
...

Which shows the lists in merge joins consuming ~671MB of memory. That's
not exactly negligible, it's ~30% of the memory usage at that point.
Maybe it wasn't just because of pathkey lists, I don't recall if I
folded some more stuff into it. But it's certainly part of it.

Again, it does not matter if the individual lists are short if we build
an exponential number of them.

> With these patches, the results of the benchmark [1] change consumption and
> planning time a little (don't forget 'geqo = off'):
>
> collapse limit | Memory consumed | Planning time
> 8 | 0.4 GB -> 0.4 GB | 0.7s -> 0.7s
> 12 | 0.8 GB -> 0.6 GB | 2.5s -> 2.3s
> 14 | 1.1 GB -> 0.8 GB | 5.5s -> 5.5s
> 18 | 9.3 GB -> 6.1 GB | 52s -> 48s
>
> [1] https://www.postgresql.org/message-id/200907091700.43411.andres%40anarazel.de
>

It's not surprising a different query uses different amounts of memory,
allocated in different places.

regards

--
Tomas Vondra