Re: Speeding up COPY TO for uuids and arrays

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