Horton

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

Michael Paquier <michael@paquier.xyz>

View thread View on postgr.es
From: Michael Paquier <michael@paquier.xyz>
To: Sutou Kouhei <kou@clear-code.com>
Cc: sawada.mshk@gmail.com, zhjwpku@gmail.com, andrew@dunslane.net, nathandbossart@gmail.com, pgsql-hackers@postgresql.org
Date: 2024-02-01T03:49:59Z
Lists: pgsql-hackers 
On Thu, Feb 01, 2024 at 10:57:58AM +0900, Michael Paquier wrote:
> And here are the results I get for text and binary (ms, average of 15
> queries after discarding the three highest and three lowest values):
>       test       | master |  v7  | v10  
> -----------------+--------+------+------
>  from_bin_1col   | 1575   | 1546 | 1575
>  from_bin_10col  | 5364   | 5208 | 5230
>  from_text_1col  | 1690   | 1715 | 1684
>  from_text_10col | 4875   | 4793 | 4757
>  to_bin_1col     | 1717   | 1730 | 1731
>  to_bin_10col    | 7728   | 7707 | 7513
>  to_text_1col    | 1710   | 1730 | 1698
>  to_text_10col   | 5998   | 5960 | 5987

Here are some numbers from a second local machine:
      test       | master |  v7  | v10  
-----------------+--------+------+------
 from_bin_1col   | 508    | 467  | 461
 from_bin_10col  | 2192   | 2083 | 2098
 from_text_1col  | 510    | 499  | 517
 from_text_10col | 1970   | 1678 | 1654
 to_bin_1col     | 575    | 577  | 573
 to_bin_10col    | 2680   | 2678 | 2722
 to_text_1col    | 516    | 506  | 527
 to_text_10col   | 2250   | 2245 | 2235

This is confirming a speedup with COPY FROM for both text and binary,
with more impact with a larger number of attributes.  That's harder to
conclude about COPY TO in both cases, but at least I'm not seeing any
regression even with some variance caused by what looks like noise.
We need more numbers from more people.  Sutou-san or Sawada-san, or
any volunteers?
--
Michael

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.