Speeding up COPY TO for uuids and arrays - Mailing list pgsql-hackers

From Laurenz Albe
Subject Speeding up COPY TO for uuids and arrays
Date
Msg-id 679d5455cbbb0af667ccb753da51a475bae1eaed.camel@cybertec.at
Whole thread Raw
Responses Re: Speeding up COPY TO for uuids and arrays
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Maiquel Grassi
Date:
Subject: RE: Psql meta-command conninfo+
Next
From: Pavel Luzanov
Date:
Subject: Re: Things I don't like about \du's "Attributes" column