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

Hi,

Thanks for preparing benchmark.

In <ZbsU53b3eEV-mMT3@paquier.xyz>
  "Re: Make COPY format extendable: Extract COPY TO format implementations" on Thu, 1 Feb 2024 12:49:59 +0900,
  Michael Paquier <michael@paquier.xyz> wrote:

> 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?

Here are some numbers on my local machine (Note that my
local machine isn't suitable for benchmark as I said
before. Each number is median of "\watch 15" results):

1:
 direction     format  n_columns     master         v7        v10
        to       text          1   1077.254   1016.953   1028.434
        to        csv          1    1079.88   1055.545    1053.95
        to     binary          1   1051.247    1033.93    1003.44
        to       text         10   4373.168   3980.442    3955.94
        to        csv         10   4753.842     4719.2   4677.643
        to     binary         10   4598.374   4431.238   4285.757
      from       text          1    875.729    916.526    869.283
      from        csv          1    909.355   1001.277    918.655
      from     binary          1    872.943    907.778    859.433
      from       text         10   2594.429   2345.292   2587.603
      from        csv         10   2968.972   3039.544   2964.468
      from     binary         10    3072.01   3109.267   3093.983

2:
 direction     format  n_columns     master         v7        v10
        to       text          1   1061.908    988.768    978.291
        to        csv          1   1095.109   1037.015   1041.613
        to     binary          1   1076.992   1000.212    983.318
        to       text         10   4336.517   3901.833   3841.789
        to        csv         10   4679.411   4640.975   4570.774
        to     binary         10    4465.04   4508.063   4261.749
      from       text          1    866.689     917.54    830.417
      from        csv          1    917.973   1695.401    871.991
      from     binary          1    841.104   1422.012    820.786
      from       text         10   2523.607   3147.738   2517.505
      from        csv         10   2917.018   3042.685   2950.338
      from     binary         10   2998.051   3128.542   3018.954

3:
 direction     format  n_columns     master         v7        v10
        to       text          1   1021.168   1031.183    962.945
        to        csv          1   1076.549   1069.661   1060.258
        to     binary          1   1024.611   1022.143    975.768
        to       text         10    4327.24   3936.703   4049.893
        to        csv         10   4620.436   4531.676   4685.672
        to     binary         10   4457.165   4390.992   4301.463
      from       text          1    887.532    907.365    888.892
      from        csv          1    945.167    1012.29    895.921
      from     binary          1     853.06    854.652    849.661
      from       text         10   2660.509   2304.256   2527.071
      from        csv         10   2913.644   2968.204   2935.081
      from     binary         10   3020.812   3081.162   3090.803

I'll measure again on my local machine later. I'll stop
other processes such as Web browser, editor and so on as
much as possible when I do.


Thanks,
-- 
kou