Re: Subquery pull-up increases jointree search space

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.

regards, tom lane