Feedback-ID: i0fe9450f:Fastmail
Date: Wed, 21 Feb 2024 14:29:05 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Andres Freund <andres@anarazel.de>, pgsql-hackers@lists.postgresql.org
Subject: Re: Speeding up COPY TO for uuids and arrays
Message-ID: <ZdWKIV3Y2kcPD28e@paquier.xyz>
References: <679d5455cbbb0af667ccb753da51a475bae1eaed.camel@cybertec.at>
 <20240217202433.b4f3hbvu45ntld7c@awork3.anarazel.de>
 <1339b52e16d4977ce59a9d3e91837c1033713930.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="VZaABRGs7GrUiyvO"
Content-Disposition: inline
In-Reply-To: <1339b52e16d4977ce59a9d3e91837c1033713930.camel@cybertec.at>
Archived-At: 
 <https://www.postgresql.org/message-id/ZdWKIV3Y2kcPD28e%40paquier.xyz>
Precedence: bulk


--VZaABRGs7GrUiyvO
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--VZaABRGs7GrUiyvO
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmXViiEACgkQnvQgOdby
QH1tsQ//X+cTSYbY+kppMogs0Q1LIySWbgIpTk5WSU8kax293lxdH0Hc8t7mQYMY
cPXJFiH0QfVdxl38PnaAccc2wkoTgfHQWZ2rhOiZERuU0PvHFAy5r78PvNPOjLIf
u38S/N5QuG4fi0OOQblZ4Bd9NoZHVHu9f7Nzg3ij6DJuLp2A9M3+Pc8ZUpNoWV2D
EYWvSLKZvT19dfKLNksist7Lr8XzftHIBhIAo97U1WJTD3qjOAWDMvqhYkHc+bYL
MlNDbHZZiSU27ki++4KZbSuetkkbA5T2pAD73iIsCywtN81LM82NjAyBWTSdN++D
kzdObCuzPwzvCL7c1TSm/m+9rURDRj3RiJcWemLHNkQbo3GKBXT7/n/AjQLrxK7W
OeSOF3YqhRCgOLZDh+CpEb2RdvUbta0hdIH0PWjH0IJTSUt+nDbIhvLSmyOygY9m
FSDg8XNULhGWccWthwuvLjiUy/iQKVt/MjZzO1BtYzdx2eDfyh/8AjEsRD1uSKo8
DuwUBK9bl1kp5J7x/NBt5TlLp+4tfYWddlX5hiu+eS+/gPVw5d6e8ZagyA96as87
nqAq0dioa5lBDqZ2kYb7xPvVlrE4kcZ037OYEHK3BtCqKRV+3aj2o4g4NOlGSx06
bTL0wnAkpz1M/vdDaWZYS2XqyywF0E5X6PMTdSB0/8PAwQ+8nrs=
=a84p
-----END PGP SIGNATURE-----

--VZaABRGs7GrUiyvO--