Horton

Re: Make COPY format extendable: Extract COPY TO format implementations

Sutou Kouhei <kou@clear-code.com>

View thread View on postgr.es
From: Sutou Kouhei <kou@clear-code.com>
To: sawada.mshk@gmail.com
Cc: pgsql-hackers@postgresql.org
Date: 2024-09-28T15:56:45Z
Lists: pgsql-hackers

Attachments

Hi,

In <CAD21AoCwMmwLJ8PQLnZu0MbB4gDJiMvWrHREQD4xRp3-F2RU2Q@mail.gmail.com>
  "Re: Make COPY format extendable: Extract COPY TO format implementations" on Fri, 27 Sep 2024 16:33:13 -0700,
  Masahiko Sawada <sawada.mshk@gmail.com> wrote:

>> * 0005 (that add "void *opaque" to Copy{From,To}StateData)
>>   has a bit negative impact for FROM and a bit positive
>>   impact for TO
>>   * But I don't know why. This doesn't change per row
>>     related codes. Increasing Copy{From,To}StateData size
>>     ("void *opaque" is added) may be related.
> 
> I was surprised that the 0005 patch made COPY FROM slower (with fewer
> rows) and COPY TO faster overall in spite of just adding one struct
> field and some functions.

Me too...

> I'm interested in why the performance trends of COPY FROM are
> different between fewer than 6M rows and more than 6M rows.

My hypothesis:

With this patch set:
  1. One row processing is faster than master.
  2. Non row related processing is slower than master.

If we have many rows, 1. impact is greater than 2. impact.


> Separating the patches into two parts (one is for COPY TO and another
> one is for COPY FROM) could be a good idea. It would help reviews and
> investigate performance regression in COPY FROM cases. And I think we
> can commit them separately.
> 
> Also, could you please rebase the patches as they conflict with the
> current HEAD?

OK. I've prepared 2 patch sets:

v20: It just rebased on master. It still mixes COPY TO and
COPY FROM implementations.

v21: It's based on v20 but splits COPY TO implementations
and COPY FROM implementations.
0001-0005 includes only COPY TO related changes.
0006-0010 includes only COPY FROM related changes.

(v21 0001 + 0006) == (v20 v0001),
(v21 0002 + 0007) == (v20 v0002) and so on.

>               I'll run some benchmarks on my environment as well.

Thanks. It's very helpful.


Thanks,
-- 
kou

Commits

  1. Refactor Copy{From|To}GetRoutine() to use pass-by-reference argument.

  2. Refactor COPY FROM to use format callback functions.

  3. Refactor COPY TO to use format callback functions.

  4. Another try to fix BF failure introduced in commit ddd5f4f54a.

  5. Revert "Refactor CopyReadAttributes{CSV,Text}() to use a callback in COPY FROM"

  6. Improve COPY TO performance when server and client encodings match

  7. Simplify signature of CopyAttributeOutCSV() in copyto.c

  8. Revert "Refactor CopyAttributeOut{CSV,Text}() to use a callback in COPY TO"

  9. Refactor CopyAttributeOut{CSV,Text}() to use a callback in COPY TO

  10. Refactor CopyReadAttributes{CSV,Text}() to use a callback in COPY FROM

  11. Add progress reporting of skipped tuples during COPY FROM.

  12. pgbench: Add \syncpipeline

  13. meson: Make gzip and tar optional

  14. Export the external file reader used in COPY FROM as APIs.