Subject: Re: Performance degradation on concurrent COPY into a single relation in PG16.

From: David Rowley <[email-masked]>
Date: Fri, 21 Jul 2023 10:35:05 +1200

On Thu, 20 Jul 2023 at 20:37, Dean Rasheed <[email-masked]> wrote:
>
> On Thu, 20 Jul 2023 at 00:56, David Rowley <[email-masked]> wrote:
> I agree with the principal though. In the attached updated patch, I
> replaced that test with a simpler one:
>
> +    /*
> +     * Process the number's digits. We optimize for decimal input (expected to
> +     * be the most common case) first. Anything that doesn't start with a base
> +     * prefix indicator must be decimal.
> +     */
> +
> +   /* process decimal digits */
> +   if (likely(ptr[0] != '0' || !isalpha((unsigned char) ptr[1])))
>
> So hopefully any compiler should only need to do the one comparison
> against '0' for any non-zero decimal input.
>
> Doing that didn't give any measurable performance improvement for me,
> but it did at least not make it noticeably worse, and seems more
> likely to generate better code with not-so-smart compilers. I'd be
> interested to know how that performs for you (and if your compiler
> really does generate 3 "first digit is '0'" tests for the unpatched
> code).

That seems better.  I benchmarked it on two machines:

gcc12.2/linux/amd3990x
create table a (a int);
insert into a select x from generate_series(1,10000000)x;
copy a to '/tmp/a.dump';

master @ ab29a7a9c
postgres=# truncate a; copy a from '/tmp/a.dump';
Time: 2226.912 ms (00:02.227)
Time: 2214.168 ms (00:02.214)
Time: 2206.481 ms (00:02.206)
Time: 2219.640 ms (00:02.220)
Time: 2205.004 ms (00:02.205)

master + pg_strtoint32_base_10_first.v2.patch
postgres=# truncate a; copy a from '/tmp/a.dump';
Time: 2067.108 ms (00:02.067)
Time: 2070.401 ms (00:02.070)
Time: 2073.423 ms (00:02.073)
Time: 2065.407 ms (00:02.065)
Time: 2066.536 ms (00:02.067) (~7% faster)

apple m2 pro/clang

master @ 9089287a
postgres=# truncate a; copy a from '/tmp/a.dump';
Time: 1286.369 ms (00:01.286)
Time: 1300.534 ms (00:01.301)
Time: 1295.661 ms (00:01.296)
Time: 1296.404 ms (00:01.296)
Time: 1268.361 ms (00:01.268)
Time: 1259.321 ms (00:01.259)

master + pg_strtoint32_base_10_first.v2.patch
postgres=# truncate a; copy a from '/tmp/a.dump';
Time: 1253.519 ms (00:01.254)
Time: 1235.256 ms (00:01.235)
Time: 1269.501 ms (00:01.270)
Time: 1267.801 ms (00:01.268)
Time: 1275.758 ms (00:01.276)
Time: 1261.478 ms (00:01.261) (a bit noisy but avg of ~1.8% faster)

David