Feedback-ID: i0fe9450f:Fastmail
Date: Tue, 6 Feb 2024 10:01:36 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Andres Freund <andres@anarazel.de>
Cc: Sutou Kouhei <kou@clear-code.com>, sawada.mshk@gmail.com,
	zhjwpku@gmail.com, andrew@dunslane.net, nathandbossart@gmail.com,
	pgsql-hackers@postgresql.org
Subject: Re: Make COPY format extendable: Extract COPY TO format
 implementations
Message-ID: <ZcGE8LrjGW8pmtOf@paquier.xyz>
References: <ZbyJ60Fd7CHt4m0i@paquier.xyz>
 <20240202.163319.975394400703526633.kou@clear-code.com>
 <ZbyiDHIrxRgzYT99@paquier.xyz>
 <20240202.174618.349091157390883477.kou@clear-code.com>
 <ZcCKwAeFrlOqPBuN@paquier.xyz>
 <20240205182118.h5rkbnjgujwzuxip@awork3.anarazel.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="yw7GqxmVywdAlt9E"
Content-Disposition: inline
In-Reply-To: <20240205182118.h5rkbnjgujwzuxip@awork3.anarazel.de>
Archived-At: <https://www.postgresql.org/message-id/ZcGE8LrjGW8pmtOf%40paquier.xyz>
Precedence: bulk


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

On Mon, Feb 05, 2024 at 10:21:18AM -0800, Andres Freund wrote:
> Have you benchmarked the performance effects of 2889fd23be5 ? I'd not at all
> be surprised if it lead to a measurable performance regression.

Yes, I was looking at runtimes and some profiles around CopyOneRowTo()
to see the effects that this has yesterday.  The principal point of
contention is CopyOneRowTo() where the callback is called once per
attribute, so more attributes stress it more.  The method I've used is
described in [1], where I've used up to 50 int attributes (fixed value
size to limit appendBinaryStringInfo) with 5 million rows, with
shared_buffers large enough that all the data fits in it, while
prewarming the whole.  Postgres runs on a tmpfs, and COPY TO is
redirected to /dev/null.

For reference, I still have some reports lying around (-g attached to
the backend process running the COPY TO queries with text format), so
here you go:
* At 95fb5b49024a:
-   83.04%    11.46%  postgres  postgres            [.] CopyOneRowTo
    - 71.58% CopyOneRowTo
       - 30.37% OutputFunctionCall
          + 27.77% int4out
       + 13.18% CopyAttributeOutText
       + 10.19% appendBinaryStringInfo
         3.76% 0xffffa7096234
         2.78% 0xffffa7096214
       + 2.49% CopySendEndOfRow
         1.21% int4out
         0.83% memcpy@plt
         0.76% 0xffffa7094ba8
         0.75% 0xffffa7094ba4
         0.69% pgstat_progress_update_param
         0.57% enlargeStringInfo
         0.52% 0xffffa7096204
         0.52% 0xffffa7094b8c
    + 11.46% _start
* At 2889fd23be56:
-   83.53%    14.24%  postgres  postgres            [.] CopyOneRowTo
    - 69.29% CopyOneRowTo
       - 29.89% OutputFunctionCall
          + 27.43% int4out
       - 12.89% CopyAttributeOutText
            pg_server_to_any
       + 9.31% appendBinaryStringInfo
         3.68% 0xffffa6940234
       + 2.74% CopySendEndOfRow
         2.43% 0xffffa6940214
         1.36% int4out
         0.74% 0xffffa693eba8
         0.73% pgstat_progress_update_param
         0.65% memcpy@plt
         0.53% MemoryContextReset
    + 14.24% _start

If you have concerns about that, I'm OK to revert, I'm not wedded to
this level of control.  Note that I've actually seen *better*
runtimes.

[1]: https://www.postgresql.org/message-id/Zbr6piWuVHDtFFOl@paquier.xyz

> I think callbacks for individual attributes is the wrong approach - the
> dispatch needs to happen at a higher level, otherwise there are too many
> indirect function calls.

Hmm.  Do you have concerns about v13 posted on [2] then?  If yes, then
I'd assume that this shuts down the whole thread or that it needs a
completely different approach, because we will multiply indirect
function calls that can control how data is generated for each row,
which is the original case that Sutou-san wanted to tackle.  There
could be many indirect calls with custom callbacks that control how
things should be processed at row-level, and COPY likes doing work
with loads of data.  The End, Start and In/OutFunc callbacks are
called only once per query, so these don't matter AFAIU.

[2]: https://www.postgresql.org/message-id/ZcFz59nJjQNjwgX0@paquier.xyz
--
Michael

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

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

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmXBhPAACgkQnvQgOdby
QH3S6Q//Vn1Tp6cynshkLif2+4aizJ6YmzCgTFOVPXjTBxRmR81LWLv0PmfYlOX2
qIMK25W2DkndRsetNQBIYoDZBfpnqhoT9M/QQYHweiR4fSsIvEDrfZr0WiuncQCJ
TCjhaNGul2T1gEzncXZCV6C55IZATPxA75XkVkpe+OOc71FMBuNa+iAB7qd+VALm
3DqNJPuPvH+IP4l0UIGPLssmrz45GwO2nBiy5b4JacAv+xMT1xKK3cZ380EsgRXn
LYU44DUOIns+QZahAM6c5GrFhextJMzDk+JviEGenFAwe2H3jX8TBWCoS4Wry3Pp
48GafDy/0RdE9KkqXuPjpBowWZAjItFz4BoPWQpxhlY49oligjofbQDlaKtmwCiS
biB4MDuUf+x6CHUAd9rSpw7J/v82e0k6ti/vfpUH2zxUsy1tkBHUxPmRCCl0hkXW
XoJEQSTbP3Iy3J4FoS60ozrJgpTxuF473DZbl6w99L9txKy0jlfU9lT8e02ELjdw
zu8ez18J3khW6nWwGtYsRxKoiFlir4UcvsdV58+omlZdIPWhF2HUi6BKXChAImeF
6zVmsvACxuHOuYkRL+uPYCgsxX1oDEA20N1nxuoSDsa12tbTfiHiiCe1Hbh7VYgj
wy/4dEFVZ5vlByB/Zl1OSlKSxk7Qe+JdQwWB0BV1VRsY5DH3ptU=
=8xCq
-----END PGP SIGNATURE-----

--yw7GqxmVywdAlt9E--