On Tue, 26 May 2026 at 18:05, Nathan Bossart <[email protected]>
wrote:
Another approach we
could take is to just send the query via PQexecParams(), but a simple test
(creating and unlinking 10K LOs) showed a ~41% slowdown compared to HEAD.
So, I guess we'll need to keep PQnfn() around for now...
Thoughts?
I had a small WIP patch (fully AI generated and not yet vetted by me)
lying around for unrelated reasons to make the extended protocol perform
closer to the simple protocol with pgbench --select-only by using
CreateOneShotCachedPlan to create the plan for unnamed prepared
statements (see attached).
Could you share the simple LO test you were running here and/or rerun it
with this patch applied? I'd love to know if the patch reduces the
slowdown significantly, or if something else is the bottleneck.
From 3586deb8bbcc25ac0c4de5c7ef6421ffddc93839 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <[email protected]>
Date: Tue, 26 May 2026 16:07:57 +0200
Subject: [PATCH v1] Speed up extended protocol by using oneshot cached plan
DISCLAIMER: Fully written by AI. I still need to check if the code is
fully correct. The approach seems sensible though, and meson test
passes on my machine.
On my laptop on master I get much lower perf with the extended protocol
than the simple protocol in a basic pgbench --select-only test:
pgbench -i
pgbench --select-only -T 10 --protocol simple
pgbench --select-only -T 10 --protocol extended
For simple I get ~56k TPS and for extended I only get ~49k TPS
With this change I get ~53k TPS with extended so a ~8% improvement.
---
src/backend/tcop/postgres.c | 57 ++++++++++++++++++++++++++++++++-----
1 file changed, 50 insertions(+), 7 deletions(-)
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index dbef734a93f..3ab9d7ab3b2 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -1513,10 +1513,27 @@ exec_parse_message(const char *query_string, /* string to execute */
/*
* Create the CachedPlanSource before we do parse analysis, since it
- * needs to see the unmodified raw parse tree.
+ * needs to see the unmodified raw parse tree. For unnamed statements
+ * use a one-shot plan: this skips the deep copy of the raw parse
+ * tree, the deep copy of the rewritten query tree in BuildCachedPlan,
+ * and the SaveCachedPlan reparent into CacheMemoryContext. The
+ * trade-off is no invalidation support, which is acceptable for the
+ * common case where the unnamed statement is Parse+Bind+Execute'd in
+ * a single transaction.
*/
- psrc = CreateCachedPlan(raw_parse_tree, query_string,
- CreateCommandTag(raw_parse_tree->stmt));
+ if (is_named)
+ psrc = CreateCachedPlan(raw_parse_tree, query_string,
+ CreateCommandTag(raw_parse_tree->stmt));
+ else
+ {
+ /*
+ * CreateOneShotCachedPlan doesn't copy query_string, so we must
+ * stash it in unnamed_stmt_context (CurrentMemoryContext) so it
+ * outlives the MessageContext reset between Parse and Bind.
+ */
+ psrc = CreateOneShotCachedPlan(raw_parse_tree, pstrdup(query_string),
+ CreateCommandTag(raw_parse_tree->stmt));
+ }
/*
* Set up a snapshot if parse analysis will need one.
@@ -1546,8 +1563,12 @@ exec_parse_message(const char *query_string, /* string to execute */
{
/* Empty input string. This is legal. */
raw_parse_tree = NULL;
- psrc = CreateCachedPlan(raw_parse_tree, query_string,
- CMDTAG_UNKNOWN);
+ if (is_named)
+ psrc = CreateCachedPlan(raw_parse_tree, query_string,
+ CMDTAG_UNKNOWN);
+ else
+ psrc = CreateOneShotCachedPlan(raw_parse_tree, pstrdup(query_string),
+ CMDTAG_UNKNOWN);
querytree_list = NIL;
}
@@ -1584,9 +1605,13 @@ exec_parse_message(const char *query_string, /* string to execute */
else
{
/*
- * We just save the CachedPlanSource into unnamed_stmt_psrc.
+ * For unnamed statements we use a one-shot CachedPlanSource, so we
+ * can't SaveCachedPlan it. Instead reparent its context (which is
+ * unnamed_stmt_context) under CacheMemoryContext so that it survives
+ * the MessageContext reset between Parse and the following Bind.
+ * drop_unnamed_stmt() will MemoryContextDelete it explicitly.
*/
- SaveCachedPlan(psrc);
+ MemoryContextSetParent(psrc->context, CacheMemoryContext);
unnamed_stmt_psrc = psrc;
}
@@ -2028,8 +2053,18 @@ exec_bind_message(StringInfo input_message)
* Obtain a plan from the CachedPlanSource. Any cruft from (re)planning
* will be generated in MessageContext. The plan refcount will be
* assigned to the Portal, so it will be released at portal destruction.
+ *
+ * For one-shot plans (used for unnamed prepared statements),
+ * BuildCachedPlan does not allocate a dedicated plan_context; the plan
+ * ends up in CurrentMemoryContext. Switch to the portal's context for
+ * the call so the plan survives MessageContext resets between Bind and
+ * Execute (e.g. in pipelined extended-protocol traffic).
*/
+ if (psrc->is_oneshot)
+ MemoryContextSwitchTo(portal->portalContext);
cplan = GetCachedPlan(psrc, params, NULL, NULL);
+ if (psrc->is_oneshot)
+ MemoryContextSwitchTo(MessageContext);
/*
* Now we can define the portal.
@@ -2906,8 +2941,16 @@ drop_unnamed_stmt(void)
{
CachedPlanSource *psrc = unnamed_stmt_psrc;
+ /*
+ * For one-shot plans DropCachedPlan does not free the context (it's
+ * owned by the caller), so capture it and delete it ourselves.
+ */
+ MemoryContext oneshot_ctx = psrc->is_oneshot ? psrc->context : NULL;
+
unnamed_stmt_psrc = NULL;
DropCachedPlan(psrc);
+ if (oneshot_ctx)
+ MemoryContextDelete(oneshot_ctx);
}
}
base-commit: 6aa26be288fa811270dfc1e39c015c23a97688b4
--
2.54.0
