public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nazir Bilal Yavuz <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: KAZAR Ayoub <[email protected]>
Cc: Neil Conway <[email protected]>
Cc: Manni Wood <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Shinya Kato <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
Date: Wed, 11 Feb 2026 16:27:50 +0300
Message-ID: <CAN55FZ1=O6TjeZM2CUT7T2tu66uJT+w3G9FiRXVs+gt_ousFxQ@mail.gmail.com> (raw)
In-Reply-To: <aYZvdsXPElQvwWOA@nathan>
References: <CAN55FZ0Nd9FL=aDSjOTJTeFAn8VNrZgWG+WbcHR+R7GkDMvUyw@mail.gmail.com>
<CAN55FZ1fwKgGo2wEie1w2M2jzJko6cMi1NWD05Xm47_L9a3D+g@mail.gmail.com>
<CAKWEB6oZdQhhBV3ojHLBwjQgKzfDw0fkqncurt9oi7vNsq41ww@mail.gmail.com>
<CAN55FZ1p5UyUdTRO7iWR_ukjhJDOnpOR2rYNOq=+hcC45OuahQ@mail.gmail.com>
<CAOW5sYZEx=fPw2wp7y2nK_-ifXFeYW4CTmFx_OQeoHFjG7rbHw@mail.gmail.com>
<CA+K2Ru=C_woAnd-3-pGHoNSTR8FOf=7eeSWE1xaLt9ojVWndVg@mail.gmail.com>
<CAN55FZ0FRB2OD6-oEESLvgUT4bLZQVD72pAqUqzdw7Rx5cN0ig@mail.gmail.com>
<CA+K2Run1VdLnmp-5_Qv2Fax0KgT7LLJMH-uzjaaf-NZD1oU-=w@mail.gmail.com>
<aYZdKSTw6N3khsVE@nathan>
<CAN55FZ2DOeLjSXE2Jos99bgHG-Zeo3KjStrSgoA8Rf=2Mu+hFA@mail.gmail.com>
<aYZvdsXPElQvwWOA@nathan>
Hi,
On Sat, 7 Feb 2026 at 01:47, Nathan Bossart <[email protected]> wrote:
>
> On Sat, Feb 07, 2026 at 01:19:16AM +0300, Nazir Bilal Yavuz wrote:
> > I have three possible approaches in my mind, they are actually similar
> > to each other.
> >
> > 1- After encountering a special character, disable SIMD for the rest
> > of the current line and also for the rest of the data.
> >
> > 2- It is a mixed version of the current heuristic and #1. After
> > encountering a special character, skip SIMD for the current line (let'
> > say line 1) and for the next line (line 2). Then try running SIMD for
> > the next line (line 3), if there is no special character continue to
> > run SIMD but if there is a special character then skip running SIMD
> > for two lines this time. And it goes like that, everytime special
> > character is encountered in the SIMD run, skipped SIMD lines are
> > doubled.
> >
> > 3- This version is a bit different from #2. Instead of calculating the
> > number of lines to skip dynamically, skip the constant N number of
> > lines and then try to run SIMD again after these lines. N could be
> > something like 100, 1000, or 10000 etc.. Actually, you and Andrew
> > suggested this approach before [1].
> >
> > I think what you suggested is closer to #1 or #3. I just wanted to
> > hear your opinions, and whether you think any of these approaches are
> > good to implement / work on.
>
> Yeah, I think either (1) or (3) would be a good starting point. (1) is
> basically just (3) with N set to infinity, anyway. I imagine there's some
> value less than infinity that is acceptable, but if I had to pick an
> approach right now, I'd probably go with (1) to essentially remove the
> heuristic from the discussion until we're ready to focus on it.
I am sharing a v6 which implements (1). My benchmark results show
almost no difference for the special-character cases and a nice
improvement for the no-special-character cases.
Timing results after running Manni's v1.2.1 benchmark:
+---------+---------------+----------------+--------------+----------------+
| | text | no sp. | text | 1/3 sp. | csv | no sp. | csv | 1/3 sp. |
+---------+---------------+----------------+--------------+----------------+
| master | 104437 | 118711 | 121173 | 151589 |
+---------+---------------+----------------+--------------+----------------+
| patched | 90062 -%13.7 | 119070 +%0.003 | 88964 -%26.5 | 153710 +%0.013 |
+---------+---------------+----------------+--------------+----------------+
In case the table does not render well in your email client, here is a
short summary:
- Text, no special characters: 13.7% faster
- Text, 1/3 special characters: %0.003 slower, no meaningful change
- CSV, no special characters: 26.5% faster
- CSV, 1/3 special characters: %0.013 slower, no meaningful change
--
Regards,
Nazir Bilal Yavuz
Microsoft
Attachments:
[text/x-patch] v6-0001-Speed-up-COPY-FROM-text-CSV-parsing-using-SIMD.patch (8.0K, 2-v6-0001-Speed-up-COPY-FROM-text-CSV-parsing-using-SIMD.patch)
download | inline diff:
From 494f86e2cd01c9d55e90f7683e151828d127b8e4 Mon Sep 17 00:00:00 2001
From: Nazir Bilal Yavuz <[email protected]>
Date: Wed, 11 Feb 2026 14:49:21 +0300
Subject: [PATCH v6] Speed up COPY FROM text/CSV parsing using SIMD
This patch disables SIMD when SIMD encounters a special character which
is neither EOF nor EOL.
Author: Shinya Kato <[email protected]>
Author: Nazir Bilal Yavuz <[email protected]>
Reviewed-by: Kazar Ayoub <[email protected]>
Reviewed-by: Nathan Bossart <[email protected]>
Reviewed-by: Neil Conway <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Manni Wood <[email protected]>
Reviewed-by: Mark Wong <[email protected]>
Discussion: https://postgr.es/m/CAOzEurSW8cNr6TPKsjrstnPfhf4QyQqB4tnPXGGe8N4e_v7Jig%40mail.gmail.com
---
src/backend/commands/copyfrom.c | 4 +
src/backend/commands/copyfromparse.c | 132 ++++++++++++++++++++++-
src/include/commands/copyfrom_internal.h | 4 +
3 files changed, 135 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 25ee20b23db..fbf78b6698b 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1721,6 +1721,10 @@ BeginCopyFrom(ParseState *pstate,
cstate->cur_attval = NULL;
cstate->relname_only = false;
+ /* Initialize SIMD variables */
+ cstate->simd_enabled = false;
+ cstate->simd_initialized = false;
+
/*
* Allocate buffers for the input pipeline.
*
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 94d6f415a06..554b3cb9bf8 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -72,6 +72,7 @@
#include "miscadmin.h"
#include "pgstat.h"
#include "port/pg_bswap.h"
+#include "port/simd.h"
#include "utils/builtins.h"
#include "utils/rel.h"
@@ -141,12 +142,14 @@ static const char BinarySignature[11] = "PGCOPY\n\377\r\n\0";
/* non-export function prototypes */
static bool CopyReadLine(CopyFromState cstate, bool is_csv);
-static bool CopyReadLineText(CopyFromState cstate, bool is_csv);
static int CopyReadAttributesText(CopyFromState cstate);
static int CopyReadAttributesCSV(CopyFromState cstate);
static Datum CopyReadBinaryAttribute(CopyFromState cstate, FmgrInfo *flinfo,
Oid typioparam, int32 typmod,
bool *isnull);
+static pg_attribute_always_inline bool CopyReadLineText(CopyFromState cstate,
+ bool is_csv,
+ bool simd_enabled);
static pg_attribute_always_inline bool CopyFromTextLikeOneRow(CopyFromState cstate,
ExprContext *econtext,
Datum *values,
@@ -1173,8 +1176,21 @@ CopyReadLine(CopyFromState cstate, bool is_csv)
resetStringInfo(&cstate->line_buf);
cstate->line_buf_valid = false;
- /* Parse data and transfer into line_buf */
- result = CopyReadLineText(cstate, is_csv);
+ /* Initialize SIMD on the first read */
+ if (unlikely(!cstate->simd_initialized))
+ {
+ cstate->simd_initialized = true;
+ cstate->simd_enabled = true;
+ }
+
+ /*
+ * Parse data and transfer into line_buf. To benefit from inlining, call
+ * CopyReadLineText() with constant boolean arguments.
+ */
+ if (cstate->simd_enabled)
+ result = CopyReadLineText(cstate, is_csv, true);
+ else
+ result = CopyReadLineText(cstate, is_csv, false);
if (result)
{
@@ -1241,8 +1257,8 @@ CopyReadLine(CopyFromState cstate, bool is_csv)
/*
* CopyReadLineText - inner loop of CopyReadLine for text mode
*/
-static bool
-CopyReadLineText(CopyFromState cstate, bool is_csv)
+static pg_attribute_always_inline bool
+CopyReadLineText(CopyFromState cstate, bool is_csv, bool simd_enabled)
{
char *copy_input_buf;
int input_buf_ptr;
@@ -1257,6 +1273,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];
@@ -1264,6 +1288,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
}
/*
@@ -1330,6 +1360,98 @@ 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. - When the remaining buffer is smaller than one
+ * vector width (sizeof(Vector8)), since SIMD operates on fixed-size
+ * chunks.
+ *
+ * Note that, SIMD may become slower when the input contains many
+ * special characters. To avoid this regression, we disable SIMD for
+ * the rest of the input once we encounter a special character which
+ * is neither EOF nor EOL.
+ */
+ if (simd_enabled && !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]);
+
+ if (is_csv)
+ {
+ /* \n and \r are not special inside quotes */
+ if (!in_quote)
+ match = vector8_or(vector8_eq(chunk, nl), vector8_eq(chunk, cr));
+
+ match = vector8_or(match, vector8_eq(chunk, quote));
+ if (escapec != '\0')
+ match = vector8_or(match, vector8_eq(chunk, escape));
+ }
+ else
+ {
+ match = vector8_or(vector8_eq(chunk, nl), vector8_eq(chunk, cr));
+ 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);
+ char c1,
+ c2;
+ bool simd_hit_eol,
+ simd_hit_eof;
+
+ input_buf_ptr += advance;
+ c1 = copy_input_buf[input_buf_ptr];
+
+ /*
+ * Since we stopped within the chunk and ((copy_buf_len -
+ * input_buf_ptr) > sizeof(Vector8)) is true,
+ * copy_input_buf[input_buf_ptr + 1] is guaranteed to be
+ * readable.
+ */
+ c2 = copy_input_buf[input_buf_ptr + 1];
+ simd_hit_eol = (c1 == '\r' || c1 == '\n') && (!is_csv || !in_quote);
+ simd_hit_eof = c1 == '\\' && c2 == '.' && !is_csv;
+
+ /*
+ * Do not disable SIMD when we hit EOL or EOF characters. In
+ * practice, it does not matter for EOF because parsing ends
+ * there, but we keep the behavior consistent.
+ */
+ if (!(simd_hit_eof || simd_hit_eol))
+ {
+ simd_enabled = false;
+ cstate->simd_enabled = false;
+ }
+ }
+ 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++];
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 822ef33cf69..56942a15469 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -89,6 +89,10 @@ typedef struct CopyFromStateData
const char *cur_attval; /* current att value for error messages */
bool relname_only; /* don't output line number, att, etc. */
+ /* SIMD variables */
+ bool simd_enabled;
+ bool simd_initialized;
+
/*
* Working state
*/
--
2.47.3
view thread (21+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
In-Reply-To: <CAN55FZ1=O6TjeZM2CUT7T2tu66uJT+w3G9FiRXVs+gt_ousFxQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox