From: Michael Paquier <michael@paquier.xyz>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Speeding up COPY TO for uuids and arrays
Date: Wed, 21 Feb 2024 14:29:05 +0900
Message-ID: <ZdWKIV3Y2kcPD28e@paquier.xyz> (raw)
In-Reply-To: <1339b52e16d4977ce59a9d3e91837c1033713930.camel@cybertec.at>
References: <679d5455cbbb0af667ccb753da51a475bae1eaed.camel@cybertec.at>
<20240217202433.b4f3hbvu45ntld7c@awork3.anarazel.de>
<1339b52e16d4977ce59a9d3e91837c1033713930.camel@cybertec.at>
On Mon, Feb 19, 2024 at 03:08:45PM +0100, Laurenz Albe wrote:
> On Sat, 2024-02-17 at 12:24 -0800, Andres Freund wrote:
>> I wonder if we should move the core part for converting to hex to numutils.c,
>> we already have code the for the inverse. There does seem to be further
>> optimization potential in the conversion, and that seems better done somewhere
>> central rather than one type's output function. OTOH, it might not be worth
>> it, given the need to add the dashes.
>
> I thought about it, but then decided not to do that.
> Calling a function that converts the bytes to hex and then adding the
> hyphens will slow down processing, and I think the code savings would be
> minimal at best.
Yeah, I'm not sure either if that's worth doing, the current
conversion code is simple enough. I'd be curious to hear about ideas
to optimize that more locally, though.
I was curious about the UUID one, and COPYing out 10M rows with a
single UUID attribute brings down the runtime of a COPY from 3.8s to
2.3s here on a simple benchmark, with uuid_out showing up at the top
of profiles easily on HEAD. Some references for HEAD:
31.63% 5300 postgres postgres [.] uuid_out
19.79% 3316 postgres postgres [.] appendStringInfoChar
11.27% 1887 postgres postgres [.] CopyAttributeOutText
6.36% 1065 postgres postgres [.] pgstat_progress_update_param
And with the patch for uuid_out:
22.66% 2147 postgres postgres [.] CopyAttributeOutText
12.99% 1231 postgres postgres [.] uuid_out
11.41% 1081 postgres postgres [.] pgstat_progress_update_param
4.79% 454 postgres postgres [.] CopyOneRowTo
That's very good, so I'd like to apply this part. Let me know if
there are any objections.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: michael@paquier.xyz, laurenz.albe@cybertec.at, andres@anarazel.de, pgsql-hackers@lists.postgresql.org
Subject: Re: Speeding up COPY TO for uuids and arrays
In-Reply-To: <ZdWKIV3Y2kcPD28e@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox