Re: Make COPY format extendable: Extract COPY TO format implementations - Mailing list pgsql-hackers

Hi,

In <20240222.183948.518018047578925034.kou@clear-code.com>
  "Re: Make COPY format extendable: Extract COPY TO format implementations" on Thu, 22 Feb 2024 18:39:48 +0900 (JST),
  Sutou Kouhei <kou@clear-code.com> wrote:

> How about adding "is_csv" to CopyReadline() and
> CopyReadLineText() too?

I tried this on my environment. This is a change for COPY
FROM not COPY TO but this decreases COPY TO
performance with [1]... Hmm...

master:   697.693 msec (the best case)
v15:      576.374 msec (the best case)
v15+this: 593.559 msec (the best case)

[1] COPY (SELECT
1::int2,2::int2,3::int2,4::int2,5::int2,6::int2,7::int2,8::int2,9::int2,10::int2,11::int2,12::int2,13::int2,14::int2,15::int2,16::int2,17::int2,18::int2,19::int2,20::int2,
generate_series(1,1000000::int4)) TO '/dev/null' \watch c=15
 

So I think that v15 is good.


perf result of master:

# Children      Self  Command   Shared Object      Symbol                                   
# ........  ........  ........  .................  .........................................
#
    31.39%    14.54%  postgres  postgres           [.] CopyOneRowTo
            |--17.00%--CopyOneRowTo
            |          |--10.61%--FunctionCall1Coll
            |          |           --8.40%--int2out
            |          |                     |--2.58%--pg_ltoa
            |          |                     |           --0.68%--pg_ultoa_n
            |          |                     |--1.11%--pg_ultoa_n
            |          |                     |--0.83%--AllocSetAlloc
            |          |                     |--0.69%--__memcpy_avx_unaligned_erms (inlined)
            |          |                     |--0.58%--FunctionCall1Coll
            |          |                      --0.55%--memcpy@plt
            |          |--3.25%--appendBinaryStringInfo
            |          |           --0.56%--pg_ultoa_n
            |           --0.69%--CopyAttributeOutText

perf result of v15:

# Children      Self  Command   Shared Object      Symbol                                   
# ........  ........  ........  .................  .........................................
#
    25.60%    10.47%  postgres  postgres           [.] CopyToTextOneRow
            |--15.39%--CopyToTextOneRow
            |          |--10.44%--FunctionCall1Coll
            |          |          |--7.25%--int2out
            |          |          |          |--2.60%--pg_ltoa
            |          |          |          |           --0.71%--pg_ultoa_n
            |          |          |          |--0.90%--FunctionCall1Coll
            |          |          |          |--0.84%--pg_ultoa_n
            |          |          |           --0.66%--AllocSetAlloc
            |          |          |--0.79%--ExecProjectSet
            |          |           --0.68%--int4out
            |          |--2.50%--appendBinaryStringInfo
            |           --0.53%--CopyAttributeOutText


The profiles on Michael's environment [2] showed that
CopyOneRow() % was increased by v15. But it
(CopyToTextOneRow() % not CopyOneRow() %) wasn't increased
by v15. It's decreased instead.

[2] https://www.postgresql.org/message-id/flat/ZdbtQJ-p5H1_EDwE%40paquier.xyz#6439e6ad574f2d47cd7220e9bfed3889

So I think that v15 doesn't have performance regression but
my environment isn't suitable for benchmark...


Thanks,
-- 
kou