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: pgsql-hackers@postgresql.org
Date: 2024-08-01T10:54:12Z
Lists: pgsql-hackers

Attachments

Hi,

In <b1c8c9fa-06c5-4b60-a2b3-d2b4bedbbde9@enterprisedb.com>
  "Re: Make COPY format extendable: Extract COPY TO format implementations" on Tue, 30 Jul 2024 11:51:37 +0200,
  Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:

> I decided to do the benchmark for individual parts of the patch series.
> The attached PDF shows results for master (label 0000) and the 0001-0005
> patches, along with relative performance difference between the patches.
> The color scale is the same as before - red = bad, green = good.
> 
> There are pretty clear differences between the patches and "direction"
> of the COPY. I'm sure it does depend on the hardware - I tried running
> this on rpi5 (with 32-bits), and it looks very different. There might be
> a similar behavior difference between Intel and Ryzen, but my point is
> that when looking for regressions, looking at these "per patch" charts
> can be very useful (as it reduces the scope of changes that might have
> caused the regression).

Thanks.
The numbers on your environment shows that there are
performance problems in the following cases in the v18 patch
set:

1. 0001 + TO
2. 0005 + TO

There are +-~3% differences in FROM cases. They may be noise.
+~6% differences in TO cases may not be noise.

I also tried another benchmark with the v19 (not v18) patch
set with "Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz" not "AMD
Ryzen 9 3900X 12-Core Processor".

The attached PDF visualized my numbers like your PDF but red
= bad, green = good. -30 (blue) means 70% (faster) and 30
(red) means 130% (slower).

0001 + TO is a bit slower like your numbers. Other TO cases
are a bit faster.
0002 + FROM is very slower. Other FROM cases are slower with
less records but a bit faster with many records.

I'll re-run it with "AMD Ryzen 9 3900X 12-Core Processor".


FYI: I've created a repository to push benchmark scripts:
https://gitlab.com/ktou/pg-bench


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.