Hi,
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
0002 - releases a couple more transient lists in nearby functions
0003 - release yet more transient lists in equivclass.c
0004 - release transient lists in estimate_num_groups
0005 - free lists used by mergejoin paths
0006 - free lists used by hahsjoin paths
I think 0002-0004 are a relatively straightforward extensions of the
0001 patch, releasing lists that are not used outside a function.
0005+0006 are a bit more invasive, but also save much more memory.
The places constructing mergejoin/hashjoin paths build lists for
inner/outer keys, merge/hash clauses etc. But then those are leaked,
because (a) it's not clear if a join using those lists was actually
created (or if add_path threw it away), and (b) add_path may keep it and
discard it sometime later. But that releases just the path, not the lists.
So 0005+0006 change the contract a bit:
(a) create_hashjoin_path/create_mergejoin_path always copy these list
(b) add_path frees these list when discarding a mergejoin/hashjoin path
(c) the places creating join paths free the lists too
This is a bit ugly, because sometimes the lists may be the same etc. But
it does work well enough for a hacky PoC (passes make check).
With these patches, the memory usage (per log_planner_stats) drops to
~500MB. That's a nice improvement, from the original 2.6GB. The larger
query that used ~17GB now needs ~3GB of memory.
I was wondering how this impacts planning time, so I ran the original
test with 2-11 tables, with 1000 joins for each join size. Attached are
two charts showing the average plan time and memory usage. The patched
build seems consistently faster - for smaller joins (up to ~8 tables)
the differences are small, not really visible in these charts. As the
joins grow it's getting much more visible, and the differences increase.
I redid the memory tracing for the "smaller" query (which now needs
~500MB), and it looks like this
allocation_group | pg_size_pretty
--------------------------------------+----------------
create_memoize_path | 92 MB
get_joinrel_parampathinfo | 65 MB
estimate_num_groups | 47 MB
add_paths_to_joinrel | 38 MB
generate_join_implied_equalities | 24 MB
| 15 MB
create_material_path | 15 MB
calc_nestloop_required_outer | 5224 kB
find_mergeclauses_for_outer_pathkeys | 0 bytes
make_inner_pathkeys_for_merge | 0 bytes
(10 rows)
It shouldn't be hard to get rid of estimate_num_groups entirely, by
freeing the GroupVarInfo entries (and not just the lists).
But after that, it's going to be harder. I'm not sure what to do about
the memoize/material paths - we're leaking these paths because various
places in joinpath.c try to "inject" these paths below a join. But this
way the logic in add_path may not free the path - we may not even get to
add_path, and even if we do, it operates on the join, not this new made
up memoize/material path. The callers have no idea if the path happens
to be used or not, so can't free it either.
I'm not sure what to do about this. I suppose it'd be good to have a
better idea if the path got used, in some way.
regards
--
Tomas Vondra
From 0f829fa3a3e7a0ea6b2c9155784d7976339ec963 Mon Sep 17 00:00:00 2001
From: test <test>
Date: Tue, 9 Jun 2026 00:06:00 +0200
Subject: [PATCH v2 1/6] Tom's patch
---
src/backend/optimizer/path/equivclass.c | 3 ++-
src/backend/optimizer/path/pathkeys.c | 15 ++++++---------
2 files changed, 8 insertions(+), 10 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index e3697df51a2..1ee6398087e 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1602,7 +1602,7 @@ generate_join_implied_equalities(PlannerInfo *root,
while ((i = bms_next_member(matching_ecs, i)) >= 0)
{
EquivalenceClass *ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
- List *sublist = NIL;
+ List *sublist;
/* ECs containing consts do not need any further enforcement */
if (ec->ec_has_const)
@@ -1632,6 +1632,7 @@ generate_join_implied_equalities(PlannerInfo *root,
inner_rel);
result = list_concat(result, sublist);
+ list_free(sublist);
}
return result;
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 5eb71635d15..978ac1240d7 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -1560,7 +1560,7 @@ find_mergeclauses_for_outer_pathkeys(PlannerInfo *root,
{
PathKey *pathkey = (PathKey *) lfirst(i);
EquivalenceClass *pathkey_ec = pathkey->pk_eclass;
- List *matched_restrictinfos = NIL;
+ bool found_clauses = false;
ListCell *j;
/*----------
@@ -1608,7 +1608,10 @@ find_mergeclauses_for_outer_pathkeys(PlannerInfo *root,
clause_ec = rinfo->outer_is_left ?
rinfo->left_ec : rinfo->right_ec;
if (clause_ec == pathkey_ec)
- matched_restrictinfos = lappend(matched_restrictinfos, rinfo);
+ {
+ mergeclauses = lappend(mergeclauses, rinfo);
+ found_clauses = true;
+ }
}
/*
@@ -1616,14 +1619,8 @@ find_mergeclauses_for_outer_pathkeys(PlannerInfo *root,
* sort-key positions in the pathkeys are useless. (But we can still
* mergejoin if we found at least one mergeclause.)
*/
- if (matched_restrictinfos == NIL)
+ if (!found_clauses)
break;
-
- /*
- * If we did find usable mergeclause(s) for this sort-key position,
- * add them to result list.
- */
- mergeclauses = list_concat(mergeclauses, matched_restrictinfos);
}
return mergeclauses;
--
2.54.0
From 9c47380c6da3ff78617ae80b1c8410a79b2ea396 Mon Sep 17 00:00:00 2001
From: test <test>
Date: Tue, 9 Jun 2026 00:11:47 +0200
Subject: [PATCH v2 2/6] release more lists
---
src/backend/optimizer/path/equivclass.c | 1 +
src/backend/optimizer/util/relnode.c | 5 +++++
2 files changed, 6 insertions(+)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 1ee6398087e..32e169a70e8 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1709,6 +1709,7 @@ generate_join_implied_equalities_for_ecs(PlannerInfo *root,
inner_rel);
result = list_concat(result, sublist);
+ list_free(sublist);
}
return result;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 687e923c46c..88562b6602a 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -1910,6 +1910,8 @@ get_joinrel_parampathinfo(PlannerInfo *root, RelOptInfo *joinrel,
pclauses = lappend(pclauses, rinfo);
}
+ list_free(eclauses);
+
/*
* EquivalenceClasses are harder to deal with than we could wish, because
* of the fact that a given EC can generate different clauses depending on
@@ -1964,6 +1966,9 @@ get_joinrel_parampathinfo(PlannerInfo *root, RelOptInfo *joinrel,
outer_and_req))
pclauses = lappend(pclauses, rinfo);
}
+
+ list_free(dropped_ecs);
+ list_free(eclauses);
}
/*
--
2.54.0
From a11dec8c214b083884f5a79a4bd8ebb3a8b2a59a Mon Sep 17 00:00:00 2001
From: test <test>
Date: Tue, 9 Jun 2026 01:35:31 +0200
Subject: [PATCH v2 3/6] free lists in equivclasses
---
src/backend/optimizer/path/equivclass.c | 12 ++++++++++++
1 file changed, 12 insertions(+)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 32e169a70e8..7caa3defcc4 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1823,6 +1823,10 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
{
/* failed... */
ec->ec_broken = true;
+ list_free(result);
+ list_free(new_members);
+ list_free(inner_members);
+ list_free(outer_members);
return NIL;
}
@@ -1872,6 +1876,10 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
{
/* failed... */
ec->ec_broken = true;
+ list_free(result);
+ list_free(new_members);
+ list_free(inner_members);
+ list_free(outer_members);
return NIL;
}
/* do NOT set parent_ec, this qual is not redundant! */
@@ -1885,6 +1893,10 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
}
}
+ list_free(new_members);
+ list_free(inner_members);
+ list_free(outer_members);
+
return result;
}
--
2.54.0
From 18e9c7db5695bf5386d552f4c396110eeff2f568 Mon Sep 17 00:00:00 2001
From: test <test>
Date: Tue, 9 Jun 2026 00:19:46 +0200
Subject: [PATCH v2 4/6] fix estimate_num_groups
---
src/backend/utils/adt/selfuncs.c | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index d6efd07073a..5bd705c93ab 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -3915,7 +3915,10 @@ estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows,
if (varshere == NIL)
{
if (contain_volatile_functions(groupexpr))
+ {
+ list_free(varinfos);
return input_rows;
+ }
continue;
}
@@ -4033,6 +4036,7 @@ estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows,
}
/* we're done with this relation */
+ list_free(relvarinfos);
relvarinfos = NIL;
}
}
@@ -4119,6 +4123,9 @@ estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows,
numdistinct *= reldistinct;
}
+ list_free(varinfos);
+ list_free_deep(relvarinfos);
+
varinfos = newvarinfos;
} while (varinfos != NIL);
@@ -4134,6 +4141,8 @@ estimate_num_groups(PlannerInfo *root, List *groupExprs, double input_rows,
if (numdistinct < 1.0)
numdistinct = 1.0;
+ list_free(varinfos);
+
return numdistinct;
}
--
2.54.0
From 012a966983d2316245ce847d2889db0ec2bec43f Mon Sep 17 00:00:00 2001
From: test <test>
Date: Tue, 9 Jun 2026 00:17:33 +0200
Subject: [PATCH v2 5/6] free mergejoin keys
---
src/backend/optimizer/path/joinpath.c | 18 ++++++++++-
src/backend/optimizer/util/pathnode.c | 44 ++++++++++++++++++++++++---
2 files changed, 57 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 713283a73aa..9396ce4c145 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -379,6 +379,8 @@ add_paths_to_joinrel(PlannerInfo *root,
if (set_join_pathlist_hook)
set_join_pathlist_hook(root, joinrel, outerrel, innerrel,
save_jointype, &extra);
+
+ list_free(extra.mergeclause_list);
}
/*
@@ -1519,7 +1521,7 @@ sort_inner_and_outer(PlannerInfo *root,
list_delete_nth_cell(list_copy(all_pathkeys),
foreach_current_index(l)));
else
- outerkeys = all_pathkeys; /* no work at first one... */
+ outerkeys = list_copy(all_pathkeys); /* no work at first one... */
/* Sort the mergeclauses into the corresponding ordering */
cur_mergeclauses =
@@ -1573,7 +1575,15 @@ sort_inner_and_outer(PlannerInfo *root,
innerkeys,
jointype,
extra);
+
+ list_free(cur_mergeclauses);
+ list_free(innerkeys);
+
+ if ((outerkeys != merge_pathkeys) && (innerkeys != merge_pathkeys))
+ list_free(outerkeys);
}
+
+ list_free(all_pathkeys);
}
/*
@@ -1634,7 +1644,10 @@ generate_mergejoin_paths(PlannerInfo *root,
}
if (useallclauses &&
list_length(mergeclauses) != list_length(extra->mergeclause_list))
+ {
+ list_free(mergeclauses);
return;
+ }
/* Compute the required ordering of the inner path */
innersortkeys = make_inner_pathkeys_for_merge(root,
@@ -1805,6 +1818,9 @@ generate_mergejoin_paths(PlannerInfo *root,
if (useallclauses)
break;
}
+
+ list_free(innersortkeys);
+ list_free(mergeclauses);
}
/*
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 73518c8f870..911f0de8c82 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -624,6 +624,14 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
parent_rel->pathlist = foreach_delete_current(parent_rel->pathlist,
p1);
+ if (IsA(old_path, MergePath))
+ {
+ MergePath *mjpath = (MergePath *) old_path;
+ list_free(mjpath->path_mergeclauses);
+ list_free(mjpath->innersortkeys);
+ list_free(mjpath->outersortkeys);
+ list_free(mjpath->jpath.path.pathkeys);
+ }
/*
* Delete the data pointed-to by the deleted cell, if possible
*/
@@ -659,6 +667,15 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
}
else
{
+ if (IsA(new_path, MergePath))
+ {
+ MergePath *mjpath = (MergePath *) new_path;
+ list_free(mjpath->path_mergeclauses);
+ list_free(mjpath->innersortkeys);
+ list_free(mjpath->outersortkeys);
+ list_free(mjpath->jpath.path.pathkeys);
+ }
+
/* Reject and recycle the new path */
if (!IsA(new_path, IndexPath))
pfree(new_path);
@@ -863,6 +880,16 @@ add_partial_path(RelOptInfo *parent_rel, Path *new_path)
{
parent_rel->partial_pathlist =
foreach_delete_current(parent_rel->partial_pathlist, p1);
+
+ if (IsA(old_path, MergePath))
+ {
+ MergePath *mjpath = (MergePath *) old_path;
+ list_free(mjpath->path_mergeclauses);
+ list_free(mjpath->innersortkeys);
+ list_free(mjpath->outersortkeys);
+ list_free(mjpath->jpath.path.pathkeys);
+ }
+
pfree(old_path);
}
else
@@ -895,6 +922,15 @@ add_partial_path(RelOptInfo *parent_rel, Path *new_path)
else
{
/* Reject and recycle the new path */
+ if (IsA(new_path, MergePath))
+ {
+ MergePath *mjpath = (MergePath *) new_path;
+ list_free(mjpath->path_mergeclauses);
+ list_free(mjpath->innersortkeys);
+ list_free(mjpath->outersortkeys);
+ list_free(mjpath->jpath.path.pathkeys);
+ }
+
pfree(new_path);
}
}
@@ -2483,15 +2519,15 @@ create_mergejoin_path(PlannerInfo *root,
outer_path->parallel_safe && inner_path->parallel_safe;
/* This is a foolish way to estimate parallel_workers, but for now... */
pathnode->jpath.path.parallel_workers = outer_path->parallel_workers;
- pathnode->jpath.path.pathkeys = pathkeys;
+ pathnode->jpath.path.pathkeys = list_copy(pathkeys);
pathnode->jpath.jointype = jointype;
pathnode->jpath.inner_unique = extra->inner_unique;
pathnode->jpath.outerjoinpath = outer_path;
pathnode->jpath.innerjoinpath = inner_path;
pathnode->jpath.joinrestrictinfo = restrict_clauses;
- pathnode->path_mergeclauses = mergeclauses;
- pathnode->outersortkeys = outersortkeys;
- pathnode->innersortkeys = innersortkeys;
+ pathnode->path_mergeclauses = list_copy(mergeclauses);
+ pathnode->outersortkeys = list_copy(outersortkeys);
+ pathnode->innersortkeys = list_copy(innersortkeys);
pathnode->outer_presorted_keys = outer_presorted_keys;
/* pathnode->skip_mark_restore will be set by final_cost_mergejoin */
/* pathnode->materialize_inner will be set by final_cost_mergejoin */
--
2.54.0
From 0f57a9d6c01548c746338023ff576cbd4499041c Mon Sep 17 00:00:00 2001
From: test <test>
Date: Tue, 9 Jun 2026 00:19:24 +0200
Subject: [PATCH v2 6/6] free hashjoin keys
---
src/backend/optimizer/path/joinpath.c | 5 +++++
src/backend/optimizer/util/pathnode.c | 22 +++++++++++++++++++++-
2 files changed, 26 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 9396ce4c145..d6475985c04 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -2278,7 +2278,10 @@ hash_inner_and_outer(PlannerInfo *root,
*/
if (PATH_PARAM_BY_REL(cheapest_total_outer, innerrel) ||
PATH_PARAM_BY_REL(cheapest_total_inner, outerrel))
+ {
+ list_free(hashclauses);
return;
+ }
/*
* Consider the cheapest startup outer together with the cheapest
@@ -2400,6 +2403,8 @@ hash_inner_and_outer(PlannerInfo *root,
false /* parallel_hash */ );
}
}
+
+ list_free(hashclauses);
}
/*
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 911f0de8c82..1d5cd429516 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -632,6 +632,11 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
list_free(mjpath->outersortkeys);
list_free(mjpath->jpath.path.pathkeys);
}
+ else if (IsA(old_path, HashPath))
+ {
+ HashPath *hjpath = (HashPath *) old_path;
+ list_free(hjpath->path_hashclauses);
+ }
/*
* Delete the data pointed-to by the deleted cell, if possible
*/
@@ -675,6 +680,11 @@ add_path(RelOptInfo *parent_rel, Path *new_path)
list_free(mjpath->outersortkeys);
list_free(mjpath->jpath.path.pathkeys);
}
+ else if (IsA(new_path, HashPath))
+ {
+ HashPath *hjpath = (HashPath *) new_path;
+ list_free(hjpath->path_hashclauses);
+ }
/* Reject and recycle the new path */
if (!IsA(new_path, IndexPath))
@@ -889,6 +899,11 @@ add_partial_path(RelOptInfo *parent_rel, Path *new_path)
list_free(mjpath->outersortkeys);
list_free(mjpath->jpath.path.pathkeys);
}
+ else if (IsA(old_path, HashPath))
+ {
+ HashPath *hjpath = (HashPath *) old_path;
+ list_free(hjpath->path_hashclauses);
+ }
pfree(old_path);
}
@@ -930,6 +945,11 @@ add_partial_path(RelOptInfo *parent_rel, Path *new_path)
list_free(mjpath->outersortkeys);
list_free(mjpath->jpath.path.pathkeys);
}
+ else if (IsA(new_path, HashPath))
+ {
+ HashPath *hjpath = (HashPath *) new_path;
+ list_free(hjpath->path_hashclauses);
+ }
pfree(new_path);
}
@@ -2603,7 +2623,7 @@ create_hashjoin_path(PlannerInfo *root,
pathnode->jpath.outerjoinpath = outer_path;
pathnode->jpath.innerjoinpath = inner_path;
pathnode->jpath.joinrestrictinfo = restrict_clauses;
- pathnode->path_hashclauses = hashclauses;
+ pathnode->path_hashclauses = list_copy(hashclauses);
/* final_cost_hashjoin will fill in pathnode->num_batches */
final_cost_hashjoin(root, pathnode, workspace, extra);
--
2.54.0
