Subject: Re: Reuse child_relids in try_partitionwise_join was Re: Assert failure on bms_equal(child_joinrel->relids, child_joinrelids)

From: Ashutosh Bapat <[email-masked]>
Date: Mon, 6 Nov 2023 14:48:18 +0530

explain.out in 0001 needed some adjustments. Without those CIbot shows
failures. Fixed in the attached patchset. 0001 is just for diagnosis,
not for actual commit. 0002 which is the actual patch has no changes
wrt to the previous version.

On Tue, Oct 31, 2023 at 11:06 AM Ashutosh Bapat
<[email-masked]> wrote:
>
> On Fri, Sep 8, 2023 at 3:22 PM Ashutosh Bapat
> <[email-masked]> wrote:
> >
> > On Fri, Jul 28, 2023 at 3:16 PM Ashutosh Bapat
> > <[email-masked]> wrote:
> > >
> > > Hi Tom, Richard,
> > >
> > > On Mon, Jul 24, 2023 at 8:17 AM Richard Guo <[email-masked]> wrote:
> > > >
> > > > Thanks for pushing it!
> > >
> > > With this fix, I saw a noticeable increase in the memory consumption
> > > of planner. I was running experiments mentioned in [1] The reason is
> > > the Bitmapset created by bms_union() are not freed during planning and
> > > when there are thousands of child joins involved, bitmapsets takes up
> > > a large memory and there any a large number of bitmaps.
> > >
> > > Attached 0002 patch fixes the memory consumption by calculating
> > > appinfos only once and using them twice. The number look like below
> > >
> > > Number of tables joined | without patch | with patch |
> > > ------------------------------------------------------
> > >                       2 |      40.8 MiB |   40.3 MiB |
> > >                       3 |     151.6 MiB |  146.9 MiB |
> > >                       4 |     463.9 MiB |  445.5 MiB |
> > >                       5 |    1663.9 MiB | 1563.3 MiB |
> > >
> > > The memory consumption is prominent at higher number of joins as that
> > > exponentially increases the number of child joins.
> > >
> > > Attached setup.sql and queries.sql and patch 0001 were used to measure
> > > memory similar to [1].
> > >
> > > I don't think it's a problem with the patch itself. We should be able
> > > to use Bitmapset APIs similar to what patch is doing. But there's a
> > > problem with our Bitmapset implementation. It's not space efficient
> > > for thousands of partitions. A quick calculation reveals this.
> > >
> > > If the number of partitions is 1000, the matching partitions will
> > > usually be 1000, 2000, 3000 and so on. Thus the bitmapset represnting
> > > the relids will be {b 1000, 2000, 3000, ...}. To represent a single
> > > 1000th digit current Bitmapset implementation will allocate 1000/8 =
> > > 125 bytes of memory. A 5 way join will require 125 * 5 = 625 bytes of
> > > memory. This is even true for lower relid numbers since they will be
> > > 1000 bits away e.g. (1, 1001, 2001, 3001, ...). So 1000 such child
> > > joins require 625KiB memory. Doing this as many times as the number of
> > > joins we get 120 * 625 KiB = 75 MiB which is closer to the memory
> > > difference we see above.
> > >
> > > Even if we allocate a list to hold 5 integers it will not take 625
> > > bytes. I think we need to improve our Bitmapset representation to be
> > > efficient in such cases. Of course whatever representation we choose
> > > has to be space efficient for a small number of tables as well and
> > > should gel well with our planner logic. So I guess some kind of
> > > dynamic representation which changes the underlying layout based on
> > > the contents is required. I have looked up past hacker threads to see
> > > if this has been discussed previously.
> > >
> > > I don't think this is the thread to discuss it and also I am not
> > > planning to work on it in the immediate future. But I thought I would
> > > mention it where I found it.
> > >
> > > [1] https://www.postgresql.org/message-id/[email-masked]
> > >
> >
> > Adding this small patch to the commitfest in case somebody finds it
> > worth fixing this specific memory consumption. With a new subject.
>
> Rebased patches.
> 0001 - is same as the squashed version of patches at
> https://www.postgresql.org/message-id/CAExHW5sCJX7696sF-OnugAiaXS=Ag95=[email-masked].
> 0002 is the actual fix described earlier
>
> --
> Best Wishes,
> Ashutosh Bapat

-- 
Best Wishes,
Ashutosh Bapat
From 84d59123ba6cec8717368ce9cefa290b711d7b3d Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email-masked]>
Date: Wed, 26 Jul 2023 12:08:55 +0530
Subject: [PATCH 2/2] Reuse child_relids bitmapset in partitionwise joinrels

Bitmapsets containing child relids consume large memory when thousands of
partitions are involved. Create them once, use multiple times and free them up
once their use is over.

Ashutosh Bapat
---
 src/backend/optimizer/path/joinrels.c | 10 ++++++----
 src/backend/optimizer/util/relnode.c  | 18 +++---------------
 src/include/optimizer/pathnode.h      |  3 ++-
 3 files changed, 11 insertions(+), 20 deletions(-)

diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index d03ace50a1..1feb1f99c0 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -1526,6 +1526,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 		RelOptInfo *child_joinrel;
 		AppendRelInfo **appinfos;
 		int			nappinfos;
+		Bitmapset  *child_relids = NULL;
 
 		if (joinrel->partbounds_merged)
 		{
@@ -1621,9 +1622,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 											   child_rel2->relids);
 
 		/* Find the AppendRelInfo structures */
-		appinfos = find_appinfos_by_relids(root,
-										   bms_union(child_rel1->relids,
-													 child_rel2->relids),
+		child_relids = bms_union(child_rel1->relids, child_rel2->relids);
+		appinfos = find_appinfos_by_relids(root, child_relids,
 										   &nappinfos);
 
 		/*
@@ -1641,7 +1641,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 		{
 			child_joinrel = build_child_join_rel(root, child_rel1, child_rel2,
 												 joinrel, child_restrictlist,
-												 child_sjinfo);
+												 child_sjinfo, appinfos,
+												 nappinfos);
 			joinrel->part_rels[cnt_parts] = child_joinrel;
 			joinrel->live_parts = bms_add_member(joinrel->live_parts, cnt_parts);
 			joinrel->all_partrels = bms_add_members(joinrel->all_partrels,
@@ -1659,6 +1660,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 									child_restrictlist);
 
 		pfree(appinfos);
+		bms_free(child_relids);
 	}
 }
 
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 5d83f60eb9..81044f12cc 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -853,15 +853,15 @@ build_join_rel(PlannerInfo *root,
  * 'restrictlist': list of RestrictInfo nodes that apply to this particular
  *		pair of joinable relations
  * 'sjinfo': child join's join-type details
+ * 'appinfos' and 'nappinfos': AppendRelInfo array for child relids
  */
 RelOptInfo *
 build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 					 RelOptInfo *inner_rel, RelOptInfo *parent_joinrel,
-					 List *restrictlist, SpecialJoinInfo *sjinfo)
+					 List *restrictlist, SpecialJoinInfo *sjinfo,
+					 AppendRelInfo **appinfos, int nappinfos)
 {
 	RelOptInfo *joinrel = makeNode(RelOptInfo);
-	AppendRelInfo **appinfos;
-	int			nappinfos;
 
 	/* Only joins between "other" relations land here. */
 	Assert(IS_OTHER_REL(outer_rel) && IS_OTHER_REL(inner_rel));
@@ -869,16 +869,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 	/* The parent joinrel should have consider_partitionwise_join set. */
 	Assert(parent_joinrel->consider_partitionwise_join);
 
-	/*
-	 * Find the AppendRelInfo structures for the child baserels.  We'll need
-	 * these for computing the child join's relid set, and later for mapping
-	 * Vars to the child rel.
-	 */
-	appinfos = find_appinfos_by_relids(root,
-									   bms_union(outer_rel->relids,
-												 inner_rel->relids),
-									   &nappinfos);
-
 	joinrel->reloptkind = RELOPT_OTHER_JOINREL;
 	joinrel->relids = adjust_child_relids(parent_joinrel->relids,
 										  nappinfos, appinfos);
@@ -993,8 +983,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 										nappinfos, appinfos,
 										parent_joinrel, joinrel);
 
-	pfree(appinfos);
-
 	return joinrel;
 }
 
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 6e557bebc4..65336c47dc 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -341,6 +341,7 @@ extern Bitmapset *get_param_path_clause_serials(Path *path);
 extern RelOptInfo *build_child_join_rel(PlannerInfo *root,
 										RelOptInfo *outer_rel, RelOptInfo *inner_rel,
 										RelOptInfo *parent_joinrel, List *restrictlist,
-										SpecialJoinInfo *sjinfo);
+										SpecialJoinInfo *sjinfo,
+										AppendRelInfo **appinfos, int nappinfos);
 
 #endif							/* PATHNODE_H */
-- 
2.25.1

From 0b73e106bb84478172a8f759e7e7d36b96203a67 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email-masked]>
Date: Wed, 12 Jul 2023 14:34:14 +0530
Subject: [PATCH 1/2] Report memory used for planning a query in EXPLAIN

Helper patch which is squashed version of patches submitted in
https://www.postgresql.org/message-id/CAExHW5sCJX7696sF-OnugAiaXS=Ag95=[email-masked].
Not to be committed with this patch set.

Ashutosh Bapat
---
 src/backend/commands/explain.c        | 79 ++++++++++++++++++++++++++-
 src/backend/commands/prepare.c        | 24 +++++++-
 src/backend/utils/mmgr/mcxt.c         | 12 ++++
 src/include/commands/explain.h        | 12 +++-
 src/include/utils/memutils.h          |  2 +
 src/test/regress/expected/explain.out | 26 +++++++--
 src/tools/pgindent/typedefs.list      |  1 +
 7 files changed, 148 insertions(+), 8 deletions(-)

diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index f1d71bc54e..9cd9b577c7 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -122,6 +122,8 @@ static const char *explain_get_index_name(Oid indexId);
 static void show_buffer_usage(ExplainState *es, const BufferUsage *usage,
 							  bool planning);
 static void show_wal_usage(ExplainState *es, const WalUsage *usage);
+static void show_planning_memory(ExplainState *es,
+								 const MemUsage *usage);
 static void ExplainIndexScanDetails(Oid indexid, ScanDirection indexorderdir,
 									ExplainState *es);
 static void ExplainScanTarget(Scan *plan, ExplainState *es);
@@ -397,9 +399,27 @@ ExplainOneQuery(Query *query, int cursorOptions,
 					planduration;
 		BufferUsage bufusage_start,
 					bufusage;
+		MemoryContextCounters mem_counts_start;
+		MemoryContextCounters mem_counts_end;
+		MemUsage	mem_usage;
+		MemoryContext planner_ctx;
+		MemoryContext saved_ctx;
+
+		/*
+		 * Create a new memory context to accurately measure memory malloc'ed
+		 * by the planner. For further accuracy we should use the same type of
+		 * memory context as the planner would use. That's usually AllocSet
+		 * but ensure that.
+		 */
+		Assert(IsA(CurrentMemoryContext, AllocSetContext));
+		planner_ctx = AllocSetContextCreate(CurrentMemoryContext,
+											"explain analyze planner context",
+											ALLOCSET_DEFAULT_SIZES);
 
 		if (es->buffers)
 			bufusage_start = pgBufferUsage;
+		MemoryContextMemConsumed(planner_ctx, &mem_counts_start);
+		saved_ctx = MemoryContextSwitchTo(planner_ctx);
 		INSTR_TIME_SET_CURRENT(planstart);
 
 		/* plan the query */
@@ -407,6 +427,9 @@ ExplainOneQuery(Query *query, int cursorOptions,
 
 		INSTR_TIME_SET_CURRENT(planduration);
 		INSTR_TIME_SUBTRACT(planduration, planstart);
+		MemoryContextSwitchTo(saved_ctx);
+		MemoryContextMemConsumed(planner_ctx, &mem_counts_end);
+		calc_mem_usage(&mem_usage, &mem_counts_end, &mem_counts_start);
 
 		/* calc differences of buffer counters. */
 		if (es->buffers)
@@ -417,7 +440,8 @@ ExplainOneQuery(Query *query, int cursorOptions,
 
 		/* run it (if needed) and produce output */
 		ExplainOnePlan(plan, into, es, queryString, params, queryEnv,
-					   &planduration, (es->buffers ? &bufusage : NULL));
+					   &planduration, (es->buffers ? &bufusage : NULL),
+					   &mem_usage);
 	}
 }
 
@@ -527,7 +551,7 @@ void
 ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 			   const char *queryString, ParamListInfo params,
 			   QueryEnvironment *queryEnv, const instr_time *planduration,
-			   const BufferUsage *bufusage)
+			   const BufferUsage *bufusage, const MemUsage *mem_usage)
 {
 	DestReceiver *dest;
 	QueryDesc  *queryDesc;
@@ -630,6 +654,13 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 		ExplainPropertyFloat("Planning Time", "ms", 1000.0 * plantime, 3, es);
 	}
 
+	if (es->summary && mem_usage)
+	{
+		ExplainOpenGroup("Planning Memory", "Planning Memory", true, es);
+		show_planning_memory(es, mem_usage);
+		ExplainCloseGroup("Planning Memory", "Planning Memory", true, es);
+	}
+
 	/* Print info about runtime of triggers */
 	if (es->analyze)
 		ExplainPrintTriggers(es, queryDesc);
@@ -3766,6 +3797,50 @@ show_wal_usage(ExplainState *es, const WalUsage *usage)
 	}
 }
 
+/*
+ * Show planner's memory usage details.
+ */
+static void
+show_planning_memory(ExplainState *es, const MemUsage *usage)
+{
+	if (es->format == EXPLAIN_FORMAT_TEXT)
+	{
+		appendStringInfo(es->str,
+						 "Planning Memory: used=%zu bytes allocated=%zu bytes",
+						 usage->mem_used, usage->mem_allocated);
+		appendStringInfoChar(es->str, '\n');
+	}
+	else
+	{
+		ExplainPropertyInteger("Used", "bytes", usage->mem_used, es);
+		ExplainPropertyInteger("Allocated", "bytes", usage->mem_allocated, es);
+	}
+}
+
+/*
+ * Compute memory usage from the start and end memory counts.
+ */
+void
+calc_mem_usage(MemUsage *mem_usage, MemoryContextCounters *mem_counts_end,
+			   MemoryContextCounters *mem_counts_start)
+{
+	Size		mem_used_start;
+	Size		mem_used_end;
+
+	mem_used_start = mem_counts_start->totalspace - mem_counts_start->freespace;
+	mem_used_end = mem_counts_end->totalspace - mem_counts_end->freespace;
+
+	mem_usage->mem_used = mem_used_end - mem_used_start;
+
+	/*
+	 * The net memory used is from total memory allocated and not necessarily
+	 * the net memory allocated between the two given samples. Hence do not
+	 * compute the difference between allocated memory reported in the two
+	 * given samples.
+	 */
+	mem_usage->mem_allocated = mem_counts_end->totalspace;
+}
+
 /*
  * Add some additional details about an IndexScan or IndexOnlyScan
  */
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 18f70319fc..eb39823d7a 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -583,9 +583,27 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
 	instr_time	planduration;
 	BufferUsage bufusage_start,
 				bufusage;
+	MemoryContextCounters mem_counts_start;
+	MemoryContextCounters mem_counts_end;
+	MemUsage	mem_usage;
+	MemoryContext planner_ctx;
+	MemoryContext saved_ctx;
+
+	/*
+	 * Create a new memory context to accurately measure memory malloc'ed by
+	 * the planner. For further accuracy we should use the same type of memory
+	 * context as the planner would use. That's usually AllocSet but ensure
+	 * that.
+	 */
+	Assert(IsA(CurrentMemoryContext, AllocSetContext));
+	planner_ctx = AllocSetContextCreate(CurrentMemoryContext,
+										"explain analyze planner context",
+										ALLOCSET_DEFAULT_SIZES);
 
 	if (es->buffers)
 		bufusage_start = pgBufferUsage;
+	MemoryContextMemConsumed(planner_ctx, &mem_counts_start);
+	saved_ctx = MemoryContextSwitchTo(planner_ctx);
 	INSTR_TIME_SET_CURRENT(planstart);
 
 	/* Look it up in the hash table */
@@ -623,6 +641,9 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
 
 	INSTR_TIME_SET_CURRENT(planduration);
 	INSTR_TIME_SUBTRACT(planduration, planstart);
+	MemoryContextSwitchTo(saved_ctx);
+	MemoryContextMemConsumed(planner_ctx, &mem_counts_end);
+	calc_mem_usage(&mem_usage, &mem_counts_end, &mem_counts_start);
 
 	/* calc differences of buffer counters. */
 	if (es->buffers)
@@ -640,7 +661,8 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es,
 
 		if (pstmt->commandType != CMD_UTILITY)
 			ExplainOnePlan(pstmt, into, es, query_string, paramLI, queryEnv,
-						   &planduration, (es->buffers ? &bufusage : NULL));
+						   &planduration, (es->buffers ? &bufusage : NULL),
+						   &mem_usage);
 		else
 			ExplainOneUtility(pstmt->utilityStmt, into, es, query_string,
 							  paramLI, queryEnv);
diff --git a/src/backend/utils/mmgr/mcxt.c b/src/backend/utils/mmgr/mcxt.c
index 9fc83f11f6..94159a6799 100644
--- a/src/backend/utils/mmgr/mcxt.c
+++ b/src/backend/utils/mmgr/mcxt.c
@@ -747,6 +747,18 @@ MemoryContextStatsDetail(MemoryContext context, int max_children,
 								 grand_totals.totalspace - grand_totals.freespace)));
 }
 
+/*
+ * Return the memory used in the given context and its children.
+ */
+extern void
+MemoryContextMemConsumed(MemoryContext context,
+						 MemoryContextCounters *mem_consumed)
+{
+	memset(mem_consumed, 0, sizeof(*mem_consumed));
+
+	MemoryContextStatsInternal(context, 0, false, 100, mem_consumed, false);
+}
+
 /*
  * MemoryContextStatsInternal
  *		One recursion level for MemoryContextStats
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index f9525fb572..05dcb91c49 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -62,6 +62,12 @@ typedef struct ExplainState
 	ExplainWorkersState *workers_state; /* needed if parallel plan */
 } ExplainState;
 
+typedef struct MemUsage
+{
+	Size		mem_used;
+	Size		mem_allocated;
+} MemUsage;
+
 /* Hook for plugins to get control in ExplainOneQuery() */
 typedef void (*ExplainOneQuery_hook_type) (Query *query,
 										   int cursorOptions,
@@ -92,7 +98,8 @@ extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into,
 						   ExplainState *es, const char *queryString,
 						   ParamListInfo params, QueryEnvironment *queryEnv,
 						   const instr_time *planduration,
-						   const BufferUsage *bufusage);
+						   const BufferUsage *bufusage,
+						   const MemUsage *mem_usage);
 
 extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc);
 extern void ExplainPrintTriggers(ExplainState *es, QueryDesc *queryDesc);
@@ -125,5 +132,8 @@ extern void ExplainOpenGroup(const char *objtype, const char *labelname,
 							 bool labeled, ExplainState *es);
 extern void ExplainCloseGroup(const char *objtype, const char *labelname,
 							  bool labeled, ExplainState *es);
+extern void calc_mem_usage(MemUsage *mem_usage,
+						   MemoryContextCounters *mem_counts_end,
+						   MemoryContextCounters *mem_counts_start);
 
 #endif							/* EXPLAIN_H */
diff --git a/src/include/utils/memutils.h b/src/include/utils/memutils.h
index 21640d62a6..d254a044c1 100644
--- a/src/include/utils/memutils.h
+++ b/src/include/utils/memutils.h
@@ -92,6 +92,8 @@ extern void MemoryContextStatsDetail(MemoryContext context, int max_children,
 									 bool print_to_stderr);
 extern void MemoryContextAllowInCriticalSection(MemoryContext context,
 												bool allow);
+extern void MemoryContextMemConsumed(MemoryContext context,
+									 MemoryContextCounters *mem_consumed);
 
 #ifdef MEMORY_CONTEXT_CHECKING
 extern void MemoryContextCheck(MemoryContext context);
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 809655e16e..e4b1aa7fdf 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -65,8 +65,9 @@ select explain_filter('explain (analyze) select * from int8_tbl i8');
 -----------------------------------------------------------------------------------------------
  Seq Scan on int8_tbl i8  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
  Planning Time: N.N ms
+ Planning Memory: used=N bytes allocated=N bytes
  Execution Time: N.N ms
-(3 rows)
+(4 rows)
 
 select explain_filter('explain (analyze, verbose) select * from int8_tbl i8');
                                             explain_filter                                            
@@ -74,16 +75,18 @@ select explain_filter('explain (analyze, verbose) select * from int8_tbl i8');
  Seq Scan on public.int8_tbl i8  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
    Output: q1, q2
  Planning Time: N.N ms
+ Planning Memory: used=N bytes allocated=N bytes
  Execution Time: N.N ms
-(4 rows)
+(5 rows)
 
 select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8');
                                         explain_filter                                         
 -----------------------------------------------------------------------------------------------
  Seq Scan on int8_tbl i8  (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
  Planning Time: N.N ms
+ Planning Memory: used=N bytes allocated=N bytes
  Execution Time: N.N ms
-(3 rows)
+(4 rows)
 
 select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8');
                      explain_filter                     
@@ -128,6 +131,10 @@ select explain_filter('explain (analyze, buffers, format xml) select * from int8
        <Temp-Written-Blocks>N</Temp-Written-Blocks>    +
      </Planning>                                       +
      <Planning-Time>N.N</Planning-Time>                +
+     <Planning-Memory>                                 +
+       <Used>N</Used>                                  +
+       <Allocated>N</Allocated>                        +
+     </Planning-Memory>                                +
      <Triggers>                                        +
      </Triggers>                                       +
      <Execution-Time>N.N</Execution-Time>              +
@@ -174,6 +181,9 @@ select explain_filter('explain (analyze, buffers, format yaml) select * from int
      Temp Read Blocks: N      +
      Temp Written Blocks: N   +
    Planning Time: N.N         +
+   Planning Memory:           +
+     Used: N                  +
+     Allocated: N             +
    Triggers:                  +
    Execution Time: N.N
 (1 row)
@@ -284,6 +294,10 @@ select explain_filter('explain (analyze, buffers, format json) select * from int
        "Temp I/O Write Time": N.N   +
      },                             +
      "Planning Time": N.N,          +
+     "Planning Memory": {           +
+       "Used": N,                   +
+       "Allocated": N               +
+     },                             +
      "Triggers": [                  +
      ],                             +
      "Execution Time": N.N          +
@@ -535,7 +549,11 @@ select jsonb_pretty(
          "Triggers": [                                      +
          ],                                                 +
          "Planning Time": 0.0,                              +
-         "Execution Time": 0.0                              +
+         "Execution Time": 0.0,                             +
+         "Planning Memory": {                               +
+             "Used": 0,                                     +
+             "Allocated": 0                                 +
+         }                                                  +
      }                                                      +
  ]
 (1 row)
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 87c1aee379..bcc2f48efd 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1547,6 +1547,7 @@ MemoryContextData
 MemoryContextMethodID
 MemoryContextMethods
 MemoryStatsPrintFunc
+MemUsage
 MergeAction
 MergeActionState
 MergeAppend
-- 
2.25.1

---

From: Ashutosh Bapat <[email-masked]>
Date: Tue, 6 Feb 2024 18:37:20 +0530

> wrt to the previous version.

First patch is no longer required. PFA rebased second patch.

-- 
Best Wishes,
Ashutosh Bapat
From 7cf3ad0fd9a7cfffa52a8faad759f16988427a70 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email-masked]>
Date: Wed, 26 Jul 2023 12:08:55 +0530
Subject: [PATCH] Reuse child_relids bitmapset in partitionwise joinrels

Ashutosh Bapat
---

index 4750579b0a..d9dbe0e930 100644

 
 		/*
 
 		pfree(appinfos);
 
index e5f4062bfb..98558f4661 100644
@@ -869,15 +869,15 @@ build_join_rel(PlannerInfo *root,
 
@@ -885,16 +885,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 
-	/*
@@ -1010,8 +1000,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 
-	pfree(appinfos);
-
 	return joinrel;
 }
 
index c43d97b48a..90f062ce96 100644
@@ -342,6 +342,7 @@ extern Bitmapset *get_param_path_clause_serials(Path *path);
 
 #endif							/* PATHNODE_H */
-- 
2.25.1

---

From: David Christensen <[email-masked]>
Date: Thu, 30 May 2024 13:49:07 -0700

Hello,

I am looking through some outstanding CommitFest entries; I wonder if
this particular patch is already effectively fixed by 5278d0a2, which
is both attributed to the original author as well as an extremely
similar approach.  Can this entry
(https://commitfest.postgresql.org/48/4553/) be closed?

Best,

David

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 5 Jun 2024 13:17:56 +0530

Hi David,
Thanks for looking into this.

> (https://commitfest.postgresql.org/48/4553/) be closed?

This is different. But it needs a rebase. PFA rebased patch. The revised
commit message explains the change better.

Here are numbers revised after 5278d0a2. Since the code changes only affect
partitionwise join code path, I am reporting only partition wise join
numbers. The first column reports the number of joining relations, each
having 1000 partitions. Rest of the column names are self-explanatory.

Planning memory used:
 num_joins | master  | patched | memory saved | memory saved
-----------+---------+---------+--------------+----------------------------
         2 | 31 MB   | 30 MB   | 525 kB       |                       1.68%
         3 | 111 MB  | 107 MB  | 4588 kB      |                       4.03%
         4 | 339 MB  | 321 MB  | 17 MB        |                       5.13%
         5 | 1062 MB | 967 MB  | 95 MB        |                       8.96%

Here's planning time measurements.
 num_joins | master (ms) | patched (ms) | change in planning time (ms) |
change in planning time
-----------+-------------+--------------+------------------------------+---------------------------------------
         2 |      187.86 |       177.27 |                        10.59 |
                               5.64%
         3 |      721.81 |       758.80 |                       -36.99 |
                              -5.12%
         4 |     2239.87 |      2236.19 |                         3.68 |
                               0.16%
         5 |     6830.86 |      7027.76 |                      -196.90 |
                              -2.88%
The patch calls find_appinfos_by_relids() only once (instead of twice),
calls bms_union() on child relids only once, allocates and deallocates
appinfos array only once saving some CPU cycles but spends some CPU cycles
in bms_free(). There's expected to be a net small saving in planning time.
But above numbers show planning time increases in some cases and decreases
in some cases. Repeating the experiment multiple times does not show a
stable increase or decrease. But the variations all being within noise
levels. We could conclude that the patch does not affect planning time
adversely.

The scripts I used originally [1] need a revision too since EXPLAIN MEMORY
output has changed. PFA the scripts. setup.sql creates the required
functions and tables. queries.sql EXPLAINs queries with different number of
joining relations collecting planning time and memory numbers in a table
(msmts). We need to change psql variable code to 'patched' or 'master' when
using master + patches or master branch respectively. Following queries are
used to report above measurements from msmt.

planning memory: select p.num_joins, pg_size_pretty(m.average * 1024)
"master", pg_size_pretty(p.average * 1024) as "patched",
pg_size_pretty((m.average - p.average) * 1024) "memory saved", ((m.average
- p.average) * 100/m.average)::numeric(42, 2) "memory saved in percentage"
from msmts p, msmts m where p.num_joins = m.num_joins and p.variant =
m.variant and p.code = 'patched' and m.code = 'master' and p.measurement =
m.measurement and p.measurement = 'planning memory' and p.variant = 'pwj'
order by num_joins;

planning time: select p.num_joins, m.average "master (ms)", p.average
"patched (ms)", m.average - p.average "change in planning time (ms)",
((m.average - p.
average) * 100/m.average)::numeric(42, 2) "change in planning time as
percentage" from msmts p, msmts m where p.num_joins = m.num_joins and
p.variant = m.var
iant and p.code = 'patched' and m.code = 'master' and p.measurement =
m.measurement and p.measurement = 'planning time' and p.variant = 'pwj'
order by p.vari
ant, num_joins;

[1]
https://www.postgresql.org/message-id/CAExHW5snUW7pD2RdtaBa1T_TqJYaY6W_YPVjWDrgSf33i-0uqA%40mail.gmail.com

-- 
Best Wishes,
Ashutosh Bapat
From 4a37f580806a388e5d78e452426b99f738a583e5 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email-masked]>
Date: Wed, 26 Jul 2023 12:08:55 +0530
Subject: [PATCH] Avoid repeated computations in try_partitionwise_join() and
 minions

try_partitionwise_join() computes bms_union() of Bitmapsets representing
joining child relations and fetches AppendRelInfos corresponding child
base relations participating in the join. The same computation is
repeated in build_child_join_rel(). Avoid this repeated computation by
performing it only once in try_partitionwise_join() and passing the
AppendRelInfos gathered there to build_child_join_rel().

Bitmapsets representing child relids consume large memory when thousands
of partitions are involved. By performing the bms_union() only once and
freeing the result when it's no longer required, we save memory. The
memory savings are considerable when many partitioned tables with
thousands of partitions are joined using partitionwise join.

Author: Ashutosh Bapat
Discussion: https://www.postgresql.org/message-id/flat/CAExHW5snUW7pD2RdtaBa1T_TqJYaY6W_YPVjWDrgSf33i-0uqA%40mail.gmail.com#1f9e0950518310dd300e21574979646f
---

diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index f3a9412d18..887135869c 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -1544,6 +1544,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 		RelOptInfo *child_joinrel;
 		AppendRelInfo **appinfos;
 		int			nappinfos;
+		Bitmapset  *child_relids = NULL;
 
 		if (joinrel->partbounds_merged)
 		{
@@ -1639,9 +1640,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 											   child_rel2->relids);

 		/*
@@ -1659,7 +1659,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 		{
 			child_joinrel = build_child_join_rel(root, child_rel1, child_rel2,
 												 joinrel, child_restrictlist,
-												 child_sjinfo);
+												 child_sjinfo, appinfos,
+												 nappinfos);
 			joinrel->part_rels[cnt_parts] = child_joinrel;
 			joinrel->live_parts = bms_add_member(joinrel->live_parts, cnt_parts);
 			joinrel->all_partrels = bms_add_members(joinrel->all_partrels,
@@ -1678,6 +1679,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 
 		pfree(appinfos);
 		free_child_join_sjinfo(child_sjinfo);
+		bms_free(child_relids);
 	}
 }
 
index e05b21c884..989bee0fa8 100644
@@ -876,15 +876,15 @@ build_join_rel(PlannerInfo *root,
 
@@ -892,16 +892,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 
-	/*
@@ -1017,8 +1007,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 
-	pfree(appinfos);
-
 	return joinrel;
 }
 
index 112e7c23d4..71fda9f2a4 100644
@@ -346,6 +346,7 @@ extern Bitmapset *get_param_path_clause_serials(Path *path);
 
 #endif							/* PATHNODE_H */
-- 
2.34.1

---

From: Ashutosh Bapat <[email-masked]>
Date: Thu, 6 Jun 2024 10:31:06 +0530

On Wed, Jun 5, 2024 at 1:17 PM Ashutosh Bapat <[email-masked]>
wrote:

> Here are numbers revised after 5278d0a2. Since the code changes only
> affect partitionwise join code path, I am reporting only partition wise
> join numbers. The first column reports the number of joining relations,
> each having 1000 partitions. Rest of the column names are self-explanatory.
>
> Planning memory used:

Here are some numbers with lower number of partitions per relation
For 100 partitions
Planning memory:
 num_joins | master  | patched | memory saved  | memory saved in percentage
-----------+---------+---------+---------------+----------------------------
         2 | 2820 kB | 2812 kB | 8192.00 bytes |                       0.28%
         3 | 9335 kB | 9270 kB | 65 kB         |                       0.70%
         4 | 27 MB   | 27 MB   | 247 kB        |                       0.90%
         5 | 78 MB   | 77 MB   | 1101 kB       |                       1.37%

Planning time:
 num_joins | master (ms) | patched (ms) | change in planning time (ms) |
change in planning time as percentage
-----------+-------------+--------------+------------------------------+---------------------------------------
         2 |        3.33 |         3.21 |                         0.12 |
                               3.60%
         3 |       11.40 |        11.17 |                         0.23 |
                               2.02%
         4 |       37.61 |        37.56 |                         0.05 |
                               0.13%
         5 |      124.39 |       126.08 |                        -1.69 |
                              -1.36%

For 10 partitions
Planning memory:
 num_joins | master  | patched | memory saved  | memory saved in percentage
-----------+---------+---------+---------------+----------------------------
         2 | 310 kB  | 310 kB  | 0.00 bytes    |                       0.00
         3 | 992 kB  | 989 kB  | 3072.00 bytes |                       0.30
         4 | 2891 kB | 2883 kB | 8192.00 bytes |                       0.28
         5 | 8317 kB | 8290 kB | 27 kB         |                       0.32

Planning time:
 num_joins | master (ms) | patched (ms) | change in planning time (ms) |
change in planning time as percentage
-----------+-------------+--------------+------------------------------+---------------------------------------
         2 |        0.42 |         0.37 |                         0.05 |
                              11.90
         3 |        1.08 |         1.09 |                        -0.01 |
                              -0.93
         4 |        3.56 |         3.32 |                         0.24 |
                               6.74
         5 |        8.86 |         8.78 |                         0.08 |
                               0.90

Observations:
1. As expected the memory savings are smaller for a small number of
partitions, but they are not negative. Thus the patch helps in case of a
large number of partitions but does not adversely affect a small number of
partitions
2. Planning time changes are within noise. But usually I see that the
planning time varies a lot. Thus the numbers here just indicate that the
planning times are not adversely affected by the change. Theoretically, I
would expect the patch to improve planning time as explained in the earlier
email.
3. As mentioned in the earlier email, the patch affects only the
partitionwise join code path hence I am reporting numbers with
partitionwise join enabled.

--
Best Wishes,
Ashutosh Bapat

---

From: Richard Guo <[email-masked]>
Date: Tue, 23 Jul 2024 16:35:44 +0800

On Wed, Jun 5, 2024 at 3:48 PM Ashutosh Bapat
<[email-masked]> wrote:
> This is different. But it needs a rebase. PFA rebased patch. The revised commit message explains the change better.

I looked through this patch and I think it is in a good shape.

Some minor comments:

* I think it's better to declare 'child_relids' as type Relids
rather than Bitmapset *.  While they are the same thing, Bitmapset *
isn't typically used in joinrels.c.  And I doubt that it is necessary
to explicitly initialize it to NULL.

* This patch changes the signature of function build_child_join_rel().
I recommend arranging the two newly added parameters as 'int
nappinfos, AppendRelInfo **appinfos' to maintain consistency with
other functions that include these parameters.

* At the end of the for loop in try_partitionwise_join(), we attempt
to free the variables used within the loop.  I suggest freeing these
variables in the order or reverse order of their allocation, rather
than arbitrarily.  Also, in non-partitionwise-join planning, we do not
free local variables in this manner.  I understand that we do this for
partitionwise-join because accumulating local variables can lead to
significant memory usage, particularly with many partitions.  I think
it would be beneficial to add a comment in try_partitionwise_join()
explaining this behavior.

Thanks
Richard

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 24 Jul 2024 13:00:23 +0530

On Tue, Jul 23, 2024 at 2:05 PM Richard Guo <[email-masked]> wrote:
> isn't typically used in joinrels.c.

Agreed. Sorry. Fixed now.

> And I doubt that it is necessary

Done.

> other functions that include these parameters.

Yes. Done.

> than arbitrarily.

Done. Are you suggesting it for aesthetic purposes or there's
something else (read, less defragmentation, performance gain etc.)? I
am curious.

> Also, in non-partitionwise-join planning, we do not

Sounds useful. Done.

PFA patches:
0001 - same as previous one
0002 - addresses your review comments

--
Best Wishes,
Ashutosh Bapat
From 53be9fd80aa89bf7612105ddfbbe48200b1f0ce0 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email-masked]>
Date: Wed, 26 Jul 2023 12:08:55 +0530
Subject: [PATCH 1/2] Avoid repeated computation in try_partitionwise_join()
 and build_child_join_rel()

Author: Ashutosh Bapat
Reviewed-by: David Christensen

diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index a3677f824f..f9ab82a23f 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -1547,6 +1547,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 		RelOptInfo *child_joinrel;
 		AppendRelInfo **appinfos;
 		int			nappinfos;
+		Bitmapset  *child_relids = NULL;
 
 		if (joinrel->partbounds_merged)
 		{
@@ -1642,9 +1643,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 											   child_rel2->relids);

 		/*
@@ -1662,7 +1662,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 		{
 			child_joinrel = build_child_join_rel(root, child_rel1, child_rel2,
 												 joinrel, child_restrictlist,
-												 child_sjinfo);
+												 child_sjinfo, appinfos,
+												 nappinfos);
 			joinrel->part_rels[cnt_parts] = child_joinrel;
 			joinrel->live_parts = bms_add_member(joinrel->live_parts, cnt_parts);
 			joinrel->all_partrels = bms_add_members(joinrel->all_partrels,
@@ -1681,6 +1682,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 
 		pfree(appinfos);

 
-	/*
 
-	pfree(appinfos);
-
 	return joinrel;
 }

 #endif							/* PATHNODE_H */
-- 
2.34.1

From ba0eed8f13f65d28abfc934ec08b47c9e9ad9dd1 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <[email-masked]>
Date: Wed, 24 Jul 2024 11:55:17 +0530
Subject: [PATCH 2/2] Address Richard's comments

---
 src/backend/optimizer/path/joinrels.c | 12 ++++++++----
 src/backend/optimizer/util/relnode.c  |  4 ++--
 src/include/optimizer/pathnode.h      |  2 +-
 3 files changed, 11 insertions(+), 7 deletions(-)

diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index f9ab82a23f..bf4605795a 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -1547,7 +1547,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 		RelOptInfo *child_joinrel;
 		AppendRelInfo **appinfos;
 		int			nappinfos;
-		Bitmapset  *child_relids = NULL;
+		Relids		child_relids;
 
 		if (joinrel->partbounds_merged)
 		{
@@ -1662,8 +1662,7 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 		{
 			child_joinrel = build_child_join_rel(root, child_rel1, child_rel2,
 												 joinrel, child_restrictlist,
-												 child_sjinfo, appinfos,
-												 nappinfos);
+												 child_sjinfo, nappinfos, appinfos);
 			joinrel->part_rels[cnt_parts] = child_joinrel;
 			joinrel->live_parts = bms_add_member(joinrel->live_parts, cnt_parts);
 			joinrel->all_partrels = bms_add_members(joinrel->all_partrels,
@@ -1680,9 +1679,14 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
 									child_joinrel, child_sjinfo,
 									child_restrictlist);
 
+		/*
+		 * When there are thousands of partitions involved, this loop will
+		 * accumulate a lot of memory in objects useful only in this loop.
+		 * Save it.
+		 */
 		pfree(appinfos);
-		free_child_join_sjinfo(child_sjinfo);
 		bms_free(child_relids);
+		free_child_join_sjinfo(child_sjinfo);
 	}
 }
 
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 989bee0fa8..971d1c7aae 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -876,13 +876,13 @@ build_join_rel(PlannerInfo *root,
  * 'restrictlist': list of RestrictInfo nodes that apply to this particular
  *		pair of joinable relations
  * 'sjinfo': child join's join-type details
- * 'appinfos' and 'nappinfos': AppendRelInfo array for child relids
+ * 'nappinfos' and 'appinfos': AppendRelInfo array for child relids
  */
 RelOptInfo *
 build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 					 RelOptInfo *inner_rel, RelOptInfo *parent_joinrel,
 					 List *restrictlist, SpecialJoinInfo *sjinfo,
-					 AppendRelInfo **appinfos, int nappinfos)
+					 int nappinfos, AppendRelInfo **appinfos)
 {
 	RelOptInfo *joinrel = makeNode(RelOptInfo);
 
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 71fda9f2a4..f00bd55f39 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -347,6 +347,6 @@ extern RelOptInfo *build_child_join_rel(PlannerInfo *root,
 										RelOptInfo *outer_rel, RelOptInfo *inner_rel,
 										RelOptInfo *parent_joinrel, List *restrictlist,
 										SpecialJoinInfo *sjinfo,
-										AppendRelInfo **appinfos, int nappinfos);
+										int nappinfos, AppendRelInfo **appinfos);
 
 #endif							/* PATHNODE_H */
-- 
2.34.1

---

From: Richard Guo <[email-masked]>
Date: Fri, 26 Jul 2024 17:44:33 +0800

> something else (read, less defragmentation, performance gain etc.)? I

I think it's just for readability.

> 0002 - addresses your review comments

I've worked a bit more on the comments and commit message, and I plan
to push the attached soon, barring any objections or comments.

Thanks
Richard

---

From: Richard Guo <[email-masked]>
Date: Mon, 29 Jul 2024 11:26:09 +0800

> to push the attached soon, barring any objections or comments.

Pushed.

Thanks
Richard

---

From: Ashutosh Bapat <[email-masked]>
Date: Mon, 29 Jul 2024 09:14:16 +0530

Thanks a lot Richard.

-- 
Best Wishes,
Ashutosh Bapat