On Wed, Nov 26, 2025 at 8:21 AM Manni Wood <[email protected]>
wrote:
>
>
> On Wed, Nov 26, 2025 at 5:51 AM KAZAR Ayoub <[email protected]> wrote:
>
>> Hello,
>> On Wed, Nov 19, 2025 at 10:01 PM Nathan Bossart <[email protected]>
>> wrote:
>>
>>> On Tue, Nov 18, 2025 at 05:20:05PM +0300, Nazir Bilal Yavuz wrote:
>>> > Thanks, done.
>>>
>>> I took a look at the v3 patches. Here are my high-level thoughts:
>>>
>>> + /*
>>> + * Parse data and transfer into line_buf. To get benefit from
>>> inlining,
>>> + * call CopyReadLineText() with the constant boolean variables.
>>> + */
>>> + if (cstate->simd_continue)
>>> + result = CopyReadLineText(cstate, is_csv, true);
>>> + else
>>> + result = CopyReadLineText(cstate, is_csv, false);
>>>
>>> I'm curious whether this actually generates different code, and if it
>>> does,
>>> if it's actually faster. We're already branching on
>>> cstate->simd_continue
>>> here.
>>
>> I've compiled both versions with -O2 and confirmed they generate
>> different code. When simd_continue is passed as a constant to
>> CopyReadLineText, the compiler optimizes out the condition checks from the
>> SIMD path.
>> A small benchmark on a 1GB+ file shows the expected benefit which is
>> around 6% performance improvement.
>> I've attached the assembly outputs in case someone wants to check
>> something else.
>>
>>
>> Regards,
>> Ayoub Kazar
>>
>
> Correction to my last post:
>
> I also tried files that alternated lines with no special characters and
> lines with 1/3rd special characters, thinking I could force the algorithm
> to continually check whether or not it should use simd and therefore force
> more overhead in the try-simd/don't-try-simd housekeeping code. The text
> file was still 20% faster (not 50% faster as I originally stated --- that
> was a typo). The CSV file was still 13% faster.
>
> Also, apologies for posting at the top in my last e-mail.
> --
> -- Manni Wood EDB: https://www.enterprisedb.com
>
Hello, all.
Andrew, I tried your suggestion of just reading the first chunk of the copy
file to determine if SIMD is worth using. Attached are v4 versions of the
patches showing a first attempt at doing that.
I attached test.sh.txt to show how I've been testing, with 5 million lines
of the various copy file variations introduced by Ayub Kazar.
The text copy with no special chars is 30% faster. The CSV copy with no
special chars is 48% faster. The text with 1/3rd escapes is 3% slower. The
CSV with 1/3rd quotes is 0.27% slower.
This set of patches follows the simplest suggestion of just testing the
first N lines (actually first N bytes) of the file and then deciding
whether or not to enable SIMD. This set of patches does not follow Andrew's
later suggestion of maybe checking again every million lines or so.
--
-- Manni Wood EDB: https://www.enterprisedb.com
#!/bin/bash
set -e
set -o pipefail
set -u
PG="psql -X -U postgres -h localhost -d postgres"
echo " ======= Create table t"
${PG} <<EOF
DROP TABLE IF EXISTS t;
CREATE UNLOGGED TABLE t (id INT PRIMARY KEY, filler TEXT);
EOF
echo " ======= Text, no special characters; create /tmp/t_none.txt"
${PG} <<EOF
truncate t;
INSERT INTO t
SELECT s, repeat('A', 4096)
FROM generate_series(1, 5000000) AS s;
COPY t TO '/tmp/t_none.txt' (FORMAT text);
EOF
echo " ======= Text, no special characters; load times"
${PG} <<'EOF'
\timing
truncate table t;
\copy t from /tmp/t_none.txt
truncate table t;
\copy t from /tmp/t_none.txt
truncate table t;
\copy t from /tmp/t_none.txt
truncate table t;
\copy t from /tmp/t_none.txt
truncate table t;
\copy t from /tmp/t_none.txt
EOF
rm /tmp/t_none.txt
echo " ======= CSV, no special characters; create /tmp/t_none.csv"
${PG} <<EOF
truncate t;
INSERT INTO t
SELECT s, repeat('A', 4096)
FROM generate_series(1, 5000000) AS s;
COPY t TO '/tmp/t_none.csv' (FORMAT csv, QUOTE '"');
EOF
echo " ======= CSV, no special characters; load times"
${PG} <<'EOF'
\timing
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
truncate table t;
\copy t from /tmp/t_none.csv (format csv)
EOF
rm /tmp/t_none.csv
echo " ======= Text, with 1/3 escapes; create /tmp/t_escape.txt"
${PG} <<'EOF'
truncate t;
INSERT INTO t
SELECT s, repeat('A\A', 1365)
FROM generate_series(1, 5000000) AS s;
COPY t TO '/tmp/t_escape.txt' (FORMAT text);
EOF
echo " ======= Text, with 1/3 escapes; load times"
${PG} <<'EOF'
\timing
truncate table t;
\copy t from /tmp/t_escape.txt
truncate table t;
\copy t from /tmp/t_escape.txt
truncate table t;
\copy t from /tmp/t_escape.txt
truncate table t;
\copy t from /tmp/t_escape.txt
truncate table t;
\copy t from /tmp/t_escape.txt
EOF
rm /tmp/t_escape.txt
echo " ======= CSV, with 1/3 quotes; create /tmp/t_quote.csv"
${PG} <<EOF
truncate t;
INSERT INTO t
SELECT s, repeat('A"A', 1365)
FROM generate_series(1, 5000000) AS s;
COPY t TO '/tmp/t_quote.csv' (FORMAT csv, QUOTE '"');
EOF
echo " ======= CSV, with 1/3 quotes; load times"
${PG} <<'EOF'
\timing
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
truncate table t;
\copy t from /tmp/t_quote.csv (format csv)
EOF
rm /tmp/t_quote.csv
echo " ======= Drop table t"
${PG} <<EOF
DROP TABLE IF EXISTS t;
EOF
From 38b587dda44cb7160ee734cdea55a573f302c3a9 Mon Sep 17 00:00:00 2001
From: Manni Wood <[email protected]>
Date: Fri, 5 Dec 2025 18:33:46 -0600
Subject: [PATCH v4 2/2] Speed up COPY FROM text/CSV parsing using SIMD
Authors: Shinya Kato <[email protected]>,
Nazir Bilal Yavuz <[email protected]>,
Ayoub Kazar <[email protected]>
Reviewers: Andrew Dunstan <[email protected]>
Descussion:
https://www.postgresql.org/message-id/flat/caozeursw8cnr6tpksjrstnpfhf4qyqqb4tnpxgge8n4e_v7...@mail.gmail.com
---
src/backend/commands/copyfrom.c | 3 +++
src/backend/commands/copyfromparse.c | 29 +++++++++++++++++++++++-
src/include/commands/copyfrom_internal.h | 11 +++++++++
3 files changed, 42 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 12781963b4f..e638623e5b5 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1720,6 +1720,9 @@ BeginCopyFrom(ParseState *pstate,
cstate->cur_attname = NULL;
cstate->cur_attval = NULL;
cstate->relname_only = false;
+ cstate->special_chars_encountered = 0;
+ cstate->checked_simd = false;
+ cstate->use_simd = false;
/*
* Allocate buffers for the input pipeline.
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 1edb525f072..8cfdfcd4cd8 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -1346,6 +1346,28 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
#ifndef USE_NO_SIMD
+ /*
+ * Wait until we have read more than BYTES_PROCESSED_UNTIL_SIMD_CHECK.
+ * cstate->bytes_processed will grow an unpredictable amount with each
+ * call to this function, so just wait until we have crossed the
+ * threshold.
+ */
+ if (!cstate->checked_simd && cstate->bytes_processed > BYTES_PROCESSED_UNTIL_SIMD_CHECK)
+ {
+ cstate->checked_simd = true;
+
+ /*
+ * If we have not read too many special characters
+ * (SPECIAL_CHAR_SIMD_THRESHOLD) then start using SIMD to speed up
+ * processing. This heuristic assumes that input does not vary too
+ * much from line to line and that number of special characters
+ * encountered in the first BYTES_PROCESSED_UNTIL_SIMD_CHECK are
+ * indicitive of the whole file.
+ */
+ if (cstate->special_chars_encountered < SPECIAL_CHAR_SIMD_THRESHOLD)
+ cstate->use_simd = true;
+ }
+
/*
* Use SIMD instructions to efficiently scan the input buffer for
* special characters (e.g., newline, carriage return, quote, and
@@ -1358,7 +1380,7 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
* sequentially. - The remaining buffer is smaller than one vector
* width (sizeof(Vector8)); SIMD operates on fixed-size chunks.
*/
- if (!last_was_esc && copy_buf_len - input_buf_ptr >= sizeof(Vector8))
+ if (cstate->use_simd && !last_was_esc && copy_buf_len - input_buf_ptr >= sizeof(Vector8))
{
Vector8 chunk;
Vector8 match = vector8_broadcast(0);
@@ -1415,6 +1437,7 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
*/
if (c == '\r')
{
+ cstate->special_chars_encountered++;
IF_NEED_REFILL_AND_NOT_EOF_CONTINUE(0);
}
@@ -1446,6 +1469,7 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
/* Process \r */
if (c == '\r' && (!is_csv || !in_quote))
{
+ cstate->special_chars_encountered++;
/* Check for \r\n on first line, _and_ handle \r\n. */
if (cstate->eol_type == EOL_UNKNOWN ||
cstate->eol_type == EOL_CRNL)
@@ -1502,6 +1526,7 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
/* Process \n */
if (c == '\n' && (!is_csv || !in_quote))
{
+ cstate->special_chars_encountered++;
if (cstate->eol_type == EOL_CR || cstate->eol_type == EOL_CRNL)
ereport(ERROR,
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
@@ -1524,6 +1549,8 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
{
char c2;
+ cstate->special_chars_encountered++;
+
IF_NEED_REFILL_AND_NOT_EOF_CONTINUE(0);
IF_NEED_REFILL_AND_EOF_BREAK(0);
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index c8b22af22d8..215215f909f 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -181,6 +181,17 @@ typedef struct CopyFromStateData
#define RAW_BUF_BYTES(cstate) ((cstate)->raw_buf_len - (cstate)->raw_buf_index)
uint64 bytes_processed; /* number of bytes processed so far */
+
+ /* the amount of bytes to read until checking if we should try simd */
+#define BYTES_PROCESSED_UNTIL_SIMD_CHECK 100000
+ /* the number of special chars read below which we use simd */
+#define SPECIAL_CHAR_SIMD_THRESHOLD 20000
+ uint64 special_chars_encountered; /* number of special chars
+ * encountered so far */
+ bool checked_simd; /* we read BYTES_PROCESSED_UNTIL_SIMD_CHECK
+ * and checked if we should use SIMD on the
+ * rest of the file */
+ bool use_simd; /* use simd to speed up copying */
} CopyFromStateData;
extern void ReceiveCopyBegin(CopyFromState cstate);
--
2.52.0
From 0b1f786bf58c3d90e078d4afa83b7d43dda08491 Mon Sep 17 00:00:00 2001
From: Manni Wood <[email protected]>
Date: Fri, 5 Dec 2025 18:30:00 -0600
Subject: [PATCH v4 1/2] Speed up COPY FROM text/CSV parsing using SIMD
Authors: Shinya Kato <[email protected]>,
Nazir Bilal Yavuz <[email protected]>,
Ayoub Kazar <[email protected]>
Reviewers: Andrew Dunstan <[email protected]>
Descussion:
https://www.postgresql.org/message-id/flat/caozeursw8cnr6tpksjrstnpfhf4qyqqb4tnpxgge8n4e_v7...@mail.gmail.com
---
src/backend/commands/copyfromparse.c | 73 ++++++++++++++++++++++++++++
1 file changed, 73 insertions(+)
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index a09e7fbace3..1edb525f072 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -71,7 +71,9 @@
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "pgstat.h"
+#include "port/pg_bitutils.h"
#include "port/pg_bswap.h"
+#include "port/simd.h"
#include "utils/builtins.h"
#include "utils/rel.h"
@@ -1255,6 +1257,14 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
char quotec = '\0';
char escapec = '\0';
+#ifndef USE_NO_SIMD
+ Vector8 nl = vector8_broadcast('\n');
+ Vector8 cr = vector8_broadcast('\r');
+ Vector8 bs = vector8_broadcast('\\');
+ Vector8 quote = vector8_broadcast(0);
+ Vector8 escape = vector8_broadcast(0);
+#endif
+
if (is_csv)
{
quotec = cstate->opts.quote[0];
@@ -1262,6 +1272,12 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
/* ignore special escape processing if it's the same as quotec */
if (quotec == escapec)
escapec = '\0';
+
+#ifndef USE_NO_SIMD
+ quote = vector8_broadcast(quotec);
+ if (quotec != escapec)
+ escape = vector8_broadcast(escapec);
+#endif
}
/*
@@ -1328,6 +1344,63 @@ CopyReadLineText(CopyFromState cstate, bool is_csv)
need_data = false;
}
+#ifndef USE_NO_SIMD
+
+ /*
+ * Use SIMD instructions to efficiently scan the input buffer for
+ * special characters (e.g., newline, carriage return, quote, and
+ * escape). This is faster than byte-by-byte iteration, especially on
+ * large buffers.
+ *
+ * We do not apply the SIMD fast path in either of the following
+ * cases: - When the previously processed character was an escape
+ * character (last_was_esc), since the next byte must be examined
+ * sequentially. - The remaining buffer is smaller than one vector
+ * width (sizeof(Vector8)); SIMD operates on fixed-size chunks.
+ */
+ if (!last_was_esc && copy_buf_len - input_buf_ptr >= sizeof(Vector8))
+ {
+ Vector8 chunk;
+ Vector8 match = vector8_broadcast(0);
+ uint32 mask;
+
+ /* Load a chunk of data into a vector register */
+ vector8_load(&chunk, (const uint8 *) ©_input_buf[input_buf_ptr]);
+
+ /* \n and \r are not special inside quotes */
+ if (!in_quote)
+ match = vector8_or(vector8_eq(chunk, nl), vector8_eq(chunk, cr));
+
+ if (is_csv)
+ {
+ match = vector8_or(match, vector8_eq(chunk, quote));
+ if (escapec != '\0')
+ match = vector8_or(match, vector8_eq(chunk, escape));
+ }
+ else
+ match = vector8_or(match, vector8_eq(chunk, bs));
+
+ /* Check if we found any special characters */
+ mask = vector8_highbit_mask(match);
+ if (mask != 0)
+ {
+ /*
+ * Found a special character. Advance up to that point and let
+ * the scalar code handle it.
+ */
+ int advance = pg_rightmost_one_pos32(mask);
+
+ input_buf_ptr += advance;
+ }
+ else
+ {
+ /* No special characters found, so skip the entire chunk */
+ input_buf_ptr += sizeof(Vector8);
+ continue;
+ }
+ }
+#endif
+
/* OK to fetch a character */
prev_raw_ptr = input_buf_ptr;
c = copy_input_buf[input_buf_ptr++];
--
2.52.0
