Re: Using COPY FREEZE in pgbench

On Mon, 2021-03-08 at 14:39 +0900, Tatsuo Ishii wrote:
> Currently pgbench uses plain COPY to populate pgbench_accounts
> table. With adding FREEZE option to COPY, the time to perform "pgbench
> -i" will be significantly reduced.
>
> Curent master:
> pgbench -i -s 100
> :
> :
> done in 70.78 s (drop tables 0.21 s, create tables 0.02 s, client-side generate 12.42 s, vacuum 51.11 s, primary keys 7.02 s).
>
> Using FREEZE:
> done in 16.86 s (drop tables 0.20 s, create tables 0.01 s, client-side generate 11.86 s, vacuum 0.25 s, primary keys 4.53 s).
>
> As you can see total time drops from 70.78 seconds to 16.86 seconds,
> that is 4.1 times faster. This is mainly because vacuum takes only
> 0.25 seconds after COPY FREEZE while unpatched pgbench takes 51.11
> seconds, which is 204 times slower.
>
> Thanks for the COPY FREEZE patch recently committed:
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7db0cd2145f2bce84cac92402e205e4d2b045bf2
>
> Attached is one line patch for this.

That is indeed low hanging fruit and an improvement.

> - res = PQexec(con, "copy pgbench_accounts from stdin");
> + res = PQexec(con, "copy pgbench_accounts from stdin freeze");

I think it would be better to use the official syntax and put the "freeze"
in parentheses. Perhaps the old syntax will be desupported some day.

Yours,
Laurenz Albe