While analyzing a customer's performance problem, I noticed that
the performance of pg_dump for large arrays is terrible.
As a test case, I created a table with 10000 rows, each of which
had an array of 10000 uuids. The table resided in shared buffers.
The following took 24.5 seconds:
COPY mytab TO '/dev/null';
Most of the time was spent in array_out and uuid_out.
I tried binary copy, which took 4.4 seconds:
COPY mytab TO '/dev/null' (FORMAT 'binary');
Here, a lot of time was spent in pq_begintypsend.
So I looked for low-hanging fruit, and the result is the attached
patch series.
- Patch 0001 speeds up pq_begintypsend with a custom macro.
That brought the binary copy down to 3.7 seconds, which is a
speed gain of 15%.
- Patch 0001 speeds up uuid_out by avoiding the overhead of
a Stringinfo. This brings text mode COPY to 19.4 seconds,
which is speed gain of 21%.
- Patch 0003 speeds up array_out a bit by avoiding some zero
byte writes. The measured speed gain is under 2%.
Yours,
Laurenz Albe
