Обсуждение: Speed up COPY TO text/CSV parsing using SIMD

Hi,

On 2026-02-12 22:07:52 +0100, KAZAR Ayoub wrote:
> Currently optimizing COPY FROM using SIMD is still under review, but for
> the case of COPY TO using the same ideas, we found that the problem is
> trivial, the attached patch gives very nice speedups as confirmed by
> Manni's benchmarks.

I have a hard time believing that adding a strlen() to the handling of a short
column won't be a measurable overhead with lots of short attributes.
Particularly because the patch afaict will call it repeatedly if there are any
to-be-escaped characters.

I also don't think it's good how much code this repeats. I think you'd have to
start with preparatory moving the exiting code into static inline helper
functions and then introduce SIMD into those.

Greetings,

Andres Freund

On Sat, Feb 14, 2026 at 04:02:21PM +0100, KAZAR Ayoub wrote:
> On Thu, Feb 12, 2026 at 10:25 PM Andres Freund <andres@anarazel.de> wrote:
>> I have a hard time believing that adding a strlen() to the handling of a
>> short column won't be a measurable overhead with lots of short attributes.
>> Particularly because the patch afaict will call it repeatedly if there are
>> any to-be-escaped characters.
> 
> [...]
> 
> 1000 columns:
> TEXT: 17% regression
> CSV: 3.4% regression
> 
> 500 columns:
> TEXT: 17.7% regression
> CSV: 3.1% regression
> 
> 100 columns:
> TEXT: 17.3% regression
> CSV: 3% regression
> 
> A bit unstable results, but yeah the overhead for worse cases like this is
> really significant, I can't argue whether this is worth it or not, so
> thoughts on this ?

I seriously doubt we'd commit something that produces a 17% regression
here.  Perhaps we should skip the SIMD paths whenever transcoding is
required.

-- 
nathan

On Sat, Mar 14, 2026 at 11:43:38PM +0100, KAZAR Ayoub wrote:
> Just a small concern about where some varlenas have a larger binary size
> than its text representation ex:
> SELECT pg_column_size(to_tsvector('SIMD is GOOD'));
>  pg_column_size
> ----------------
>              32
> 
> its text representation is less than sizeof(Vector8) so currently v3 would
> enter SIMD path and exit out just from the beginning (two extra branches)
> because it does this:
> + if (TupleDescAttr(tup_desc, attnum - 1)->attlen == -1 &&
> + VARSIZE_ANY_EXHDR(DatumGetPointer(value)) > sizeof(Vector8))
> 
> I thought maybe we could do * 2 or * 4 its binary size, depends on the type
> really but this is just a proposition if this case is something concerning.

Can we measure the impact of this?  How likely is this case?

> +static pg_attribute_always_inline void CopyAttributeOutText(CopyToState cstate, const char *string,
> +                                                            bool use_simd, size_t len);
> +static pg_attribute_always_inline void CopyAttributeOutCSV(CopyToState cstate, const char *string,
> +                                                           bool use_quote, bool use_simd, size_t len);

Can you test this on its own, too?  We might be able to separate this and
the change below into a prerequisite patch, assuming they show benefits.

>              if (is_csv)
> -                CopyAttributeOutCSV(cstate, string,
> -                                    cstate->opts.force_quote_flags[attnum - 1]);
> +            {
> +                if (use_simd)
> +                    CopyAttributeOutCSV(cstate, string,
> +                                        cstate->opts.force_quote_flags[attnum - 1],
> +                                        true, len);
> +                else
> +                    CopyAttributeOutCSV(cstate, string,
> +                                        cstate->opts.force_quote_flags[attnum - 1],
> +                                        false, len);
> +            }
>              else
> -                CopyAttributeOutText(cstate, string);
> +            {
> +                if (use_simd)
> +                    CopyAttributeOutText(cstate, string, true, len);
> +                else
> +                    CopyAttributeOutText(cstate, string, false, len);
> +            }

There isn't a terrible amount of branching on use_simd in these functions,
so I'm a little skeptical this makes much difference.  As above, it would
be good to measure it.

-- 
nathan

On Wed, Mar 18, 2026 at 12:02:28AM +0100, KAZAR Ayoub wrote:
>   Test                 Master    v3       v3_var   v3_var_noinl
>   TEXT clean           1504ms   -24.1%   -23.0%   -21.5%
>   CSV clean            1760ms   -34.9%   -32.7%   -33.0%

Nice!

>   TEXT 1/3 backslashes     3763ms    +4.6%    +6.9%   +4.1%
>   CSV 1/3 quotes           3885ms    +3.1%    +2.7%    -0.8%

Hm.  These seem a little bit beyond what we could ignore as noise.

> Wide table TEXT (integer columns):
> 
>   Cols    Master    v3       v3_var   v3_var_noinl
>   50      2083ms   -0.7%    -0.6%    +3.5%
>   100     4094ms   -0.1%    -0.5%    +4.5%
>   200     1560ms   +0.6%    -2.3%    +3.2%
>   500     1905ms   -1.0%    -1.3%    +4.7%
>   1000    1455ms   +1.8%    +0.4%    +4.3%

These numbers look roughly within the noise range.

> Wide table CSV:
> 
>   Cols    Master    v3       v3_var   v3_var_noinl
>   50      2421ms   +4.0%    +6.7%    +5.8%

Hm.  Is this reproducible?  A 4% regression is a bit worrisome.

>   100     4980ms   +0.1%    +2.0%     +0.1%
>   200     1901ms   +1.4%    +3.5%    +1.4%
>   500     2328ms   +1.8%    +2.7%    +2.2%
>   1000    1815ms   +2.0%    +2.8%    +2.5%

These numbers don't bother me too much, but maybe there are some ways to
minimize the regressions further.

-- 
nathan

On Wed, Mar 18, 2026 at 03:29:32AM +0100, KAZAR Ayoub wrote:
> If we have some json(b) column like : {"key1":"val1","key2":"val2"}, for
> CSV format this would immediately exit the SIMD path because of quote
> character, for json(b) this is going to be always the case.
> I measured the overhead of exiting the SIMD path a lot (8 million times for
> one COPY TO command), i only found 3% regression for this case, sometimes
> 2%.

I'm a little worried that we might be dismissing small-yet-measurable
regressions for extremely common workloads.  Unlike the COPY FROM work,
this operates on a per-attribute level, meaning we only use SIMD when an
attribute is at least 16 bytes.  The extra branching for each attribute
might not be something we can just ignore.

> For cases where we do a false commitment on SIMD because we read a binary
> size >= sizeof(Vector8), which i found very niche too, the short circuit to
> scalar each time is even more negligible (the above CSV JSON case is the
> absolute worst case).

That's good to hear.

-- 
nathan

On Fri, Mar 27, 2026 at 07:48:38PM +0100, KAZAR Ayoub wrote:
> I added a prescan loop inside the simd helpers trying to catch special
> chars in sizeof(Vector8) characters, i measured how good is this at
> reducing the overhead of starting simd and exiting at first vector:
> the scalar loop is better than SIMD for one vector if it finds a special
> character before 6th character, worst case is not a clean vector, where the
> scalar loop needs 20 more cycles compared to SIMD.
> This helps mitigate the case of JSON(B) in CSV format, this is why I only
> added this for CSV case only.

Interesting.

> In a benchmark with 10M early SIMD exit like the JSONB case, the previous
> 3% regression is gone.

While these are nice results, I think it's best that we target v20 for this
patch so that we have more time to benchmark and explore edge cases.

-- 
nathan