MIME-Version: 1.0
References: <aPkvi5P7kpA8oQKc@nathan>
 <5d81fbbb-7609-4445-9bc4-8af211fb7674@dunslane.net>
 <CAKWEB6qdyhN3EoUNAK23etXX-kXH-_79NNbTsKqtF1g1WkuaBQ@mail.gmail.com>
 <CA+K2RumMC+avYGSX-AWNeod3w+XOGHrVPz8HiqkvJj7AZ5tZXA@mail.gmail.com>
 <CAKWEB6pev=pNVi4qDYWS50N=YFrKRbjH1h=5F1bXpnK7WR5CYg@mail.gmail.com>
 <aRue0D4QQkUf2B_N@nathan>
 <CAOzEurTHCGL-Txqf5rxMsPgTF=dTCOsr=uhJdXebqjEJy-0L7g@mail.gmail.com>
 <CAN55FZ0+JZvKYVCnJqLhHaWF9eBGmTaF1BCEpttxw1aT3G_+Qw@mail.gmail.com>
 <8e226753-57af-489a-bfbe-caa23dd71286@dunslane.net>
 <CAN55FZ1XF=R7F7B__gq04rp2nQnJqs1yfExEXo4riWc68+Pe0w@mail.gmail.com>
 <aR4wDwNdLc5TmcQq@nathan>
 <CA+K2Rump8NoMRZRZ2r4jHXUJwByasy_c3_b0oaO+TLkSbMD-jw@mail.gmail.com>
 <CAKWEB6rLxPVtN4ffZ3CMTL518zhk_BWzzBt6ZE2oUSaErdphxA@mail.gmail.com>
 <CAKWEB6oO4gQd+UJBrU=uuUTE8Hv7GMznjMouvn0Lskr52UqjhQ@mail.gmail.com>
In-Reply-To: 
 <CAKWEB6oO4gQd+UJBrU=uuUTE8Hv7GMznjMouvn0Lskr52UqjhQ@mail.gmail.com>
From: Bilal Yavuz <byavuz81@gmail.com>
Date: Sat, 6 Dec 2025 10:55:50 +0300
Message-ID: 
 <CAN55FZ0Nd9FL=aDSjOTJTeFAn8VNrZgWG+WbcHR+R7GkDMvUyw@mail.gmail.com>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
To: Manni Wood <manni.wood@enterprisedb.com>
Cc: KAZAR Ayoub <ma_kazar@esi.dz>, Nathan Bossart <nathandbossart@gmail.com>,
	Andrew Dunstan <andrew@dunslane.net>, Shinya Kato <shinya11.kato@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAN55FZ0Nd9FL%3DaDSjOTJTeFAn8VNrZgWG%2BWbcHR%2BR7GkDMvUyw%40mail.gmail.com>
Precedence: bulk

Hi,

On Sat, 6 Dec 2025 at 04:40, Manni Wood <manni.wood@enterprisedb.com> wrote=
:
> Hello, all.
>
> Andrew, I tried your suggestion of just reading the first chunk of the co=
py file to determine if SIMD is worth using. Attached are v4 versions of th=
e patches showing a first attempt at doing that.

Thank you for doing this!

> I attached test.sh.txt to show how I've been testing, with 5 million line=
s of the various copy file variations introduced by Ayub Kazar.
>
> The text copy with no special chars is 30% faster. The CSV copy with no s=
pecial chars is 48% faster. The text with 1/3rd escapes is 3% slower. The C=
SV with 1/3rd quotes is 0.27% slower.
>
> This set of patches follows the simplest suggestion of just testing the f=
irst N lines (actually first N bytes) of the file and then deciding whether=
 or not to enable SIMD. This set of patches does not follow Andrew's later =
suggestion of maybe checking again every million lines or so.

My input-generation script is not ready to share yet, but the inputs
follow this format: text_${n}.input, where n represents the number of
normal characters before the delimiter. For example:

n =3D 0 -> "\n\n\n\n\n..." (no normal characters)
n =3D 1 -> "a\n..." (1 normal character before the delimiter)
...
n =3D 5 -> "aaaaa\n..."
=E2=80=A6 continuing up to n =3D 32.

Each line has 4096 chars and there are a total of 100000 lines in each
input file.

I only benchmarked the text format. I compared the latest heuristic I
shared [1] with the current method. The benchmarks show roughly a ~16%
regression at the worst case (n =3D 2), with regressions up to n =3D 5.
For the remaining values, performance was similar.

Actual comparison of timings (in ms):

current method / heuristic
n =3D 0 -> 3252.7253 / 2856.2753 (%12)
n =3D 1 -> 2910.321 / 2520.7717 (%13)
n =3D 2 -> 2865.008 / 2403.2017 (%16)
n =3D 3 -> 2608.649 / 2353.1477 (%9)
n =3D 4 -> 2460.74 / 2300.1783 (%6)
n =3D 5 -> 2451.696 / 2362.1573 (%3)
No difference for the rest.

Side note: Sorry for the delay in responding, I will continue working
on this next week.

[1] https://postgr.es/m/CAN55FZ1KF7XNpm2XyG%3DM-sFUODai%3D6Z8a11xE3s4YRBeBK=
Y3tA%40mail.gmail.com

--=20
Regards,
Nazir Bilal Yavuz
Microsoft