Hello,
On 03/09/2026 20:21, Nathan Bossart wrote:
On Thu, Sep 03, 2026 at 06:01:15PM +0200, Ayoub Kazar wrote:
After looking into more test cases, the opportunity here isn't as big as it
was for COPY FROM in general cases, yet i have seen good performance gains
show up in most cases, and avoiding regressions elsewhere adds some
complexity, there's also the possibility to include the case of TOASTed
attributes here ; which i think is a win.
I've tested this even more, regressions are limited to 3-4%, so the
previous results are still stable for me, which i find not very bad
again. I might need to justify this further with heavier COPY TO
commands in larger variety of cases maybe, we'll see whether the current
state holds strong or not.
Yeah, when I looked into this one a while ago, I couldn't find a good way
to avoid regressions. I wonder if we'll need to make some architectural
adjustments before trying again.
Coming back to that ...
I looked at some simple tests to see where most of the time is spent in
the COPY TO command, in the referenced flamegraph [1], i noticed some
parts that are interesting to optimize, noticeably:
#1 Output functions: in datum_to_json_internal, i saw that we save some
cycles by avoiding palloc, memcpy from using OutputFunctions, but only
for interesting cases of TEXT and similar. I think we can do this too
for COPY TO for the same reasons, we save some allocs and copies.
#2 (Attached patch) CopyAttributeOutCSV scanned each attribute value
twice: once to decide whether quoting was needed, then again in the
escape loop from the start of the string. On the unquoted path it also
called CopySendString, which internally called strlen on a string we had
just walked. The fix hoists tptr so the unquoted path uses tptr - ptr as
the length directly, and the quoted path bulk-emits the already-scanned
clean prefix in one CopySendData call when escapec == quotec (standard
case) before entering the escape loop at tptr. A previous patch i had
also tracked whether we see an escapec in the first loop or not to emit
prefix in both standard CSV case and custom escapec, but this had 11%
regression in clean text strings benchmark.
Benchmarks show: master 798bdc
1: Clean Prefix + Quote trigger: 20-25% improvement
2: Long clean text: 3% improvement
3: Mixed column types: 12-16% improvement
4: First byte triggers (checking for regressions): 1-2% improvement
5: Tiny strings: 4-5% improvement
AFAICT, there shouldn't be any regression in any case i can think of,
one thing to note is:
- CopySendString(cstate, ptr);
+ CopySendData(cstate, ptr, tptr - ptr);
Doesn't show big improvements alone because in my case strlen() is
vectorized (avx2) so its pretty fast, yet i don't think it should be
faster (atleast for large enough clean strings).
What do you think ?
[1]: https://gist.github.com/AyoubKaz07/43feb5ca88e7e118f1f0e4996a624bb7
Regards,
Ayoub
From 84d7270e821175041839021c806d9b66fd50f3b8 Mon Sep 17 00:00:00 2001
From: AyoubKAZ <[email protected]>
Date: Mon, 7 Sep 2026 01:45:43 +0200
Subject: [PATCH] COPY TO: avoid prefix re-scan and strlen in
CopyAttributeOutCSV
CopyAttributeOutCSV makes a preliminary scan over each attribute to
determine if quoting is needed.
On the unquoted path, the preliminary scan has already walked the entire
string until '\0'. By hoisting tptr to the outer scope, we know the
exact string length (tptr - ptr) and can pass it directly to
CopySendData, avoiding a redundant strlen() pass.
On the quoted path, the scan stopped at the first quoting character at
tptr, but the subsequent escape loop previously restarted from the
beginning of the string, rescanning the clean prefix byte-by-byte in
scalar mode. In the standard RFC 4180 CSV configuration (escapec ==
quotec), any escapec in [ptr, tptr) would have matched quotec and
stopped the preliminary scan right there. Thus, the prefix [ptr, tptr)
is guaranteed to contain no escapec characters and can be emitted in one
bulk CopySendData call, beginning the escape loop only at tptr.
The preliminary scan loop itself is left untouched, introducing zero
additional instructions or branches.
---
src/backend/commands/copyto.c | 19 ++++++++++++++++---
1 file changed, 16 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 5850608a3fb..b3e1804bf5c 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -1598,6 +1598,7 @@ CopyAttributeOutCSV(CopyToState cstate, const char *string,
{
const char *ptr;
const char *start;
+ const char *tptr;
char c;
char delimc = cstate->opts.delim[0];
char quotec = cstate->opts.quote[0];
@@ -1613,6 +1614,8 @@ CopyAttributeOutCSV(CopyToState cstate, const char *string,
else
ptr = string;
+ tptr = ptr;
+
/*
* Make a preliminary pass to discover if it needs quoting
*/
@@ -1629,8 +1632,6 @@ CopyAttributeOutCSV(CopyToState cstate, const char *string,
use_quote = true;
else
{
- const char *tptr = ptr;
-
while ((c = *tptr) != '\0')
{
if (c == delimc || c == quotec || c == '\n' || c == '\r')
@@ -1650,6 +1651,18 @@ CopyAttributeOutCSV(CopyToState cstate, const char *string,
{
CopySendChar(cstate, quotec);
+ /*
+ * When escapec == quotec, any escapec in [ptr, tptr) would have
+ * matched quotec and triggered use_quote. Thus the scanned prefix
+ * contains no escapec and can be emitted in bulk, skipping a
+ * redundant re-scan in the escape loop.
+ */
+ if (tptr > ptr && escapec == quotec)
+ {
+ CopySendData(cstate, ptr, tptr - ptr);
+ ptr = tptr;
+ }
+
/*
* We adopt the same optimization strategy as in CopyAttributeOutText
*/
@@ -1674,7 +1687,7 @@ CopyAttributeOutCSV(CopyToState cstate, const char *string,
else
{
/* If it doesn't need quoting, we can just dump it as-is */
- CopySendString(cstate, ptr);
+ CopySendData(cstate, ptr, tptr - ptr);
}
}
--
2.34.1
