MIME-Version: 1.0
References: 
 <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>
 <CAN55FZ0Nd9FL=aDSjOTJTeFAn8VNrZgWG+WbcHR+R7GkDMvUyw@mail.gmail.com>
 <CAN55FZ1fwKgGo2wEie1w2M2jzJko6cMi1NWD05Xm47_L9a3D+g@mail.gmail.com>
 <aTx-LDyiHV-7wfOP@ltdrgnflg2>
 <CAKWEB6r=axZsG-s7zyWURZ-s9-s1dTV9ohkZXO0ynfLEU5ha3Q@mail.gmail.com>
 <CAN55FZ2DE2XSrFUhsOqbpBo+BtzTwsJWOD0MffvdGnHtbsPRuw@mail.gmail.com>
 <CA+K2RumOaH-daBGN6uTo6+_0XSg7HQ10Na8OzScCV5j6eKkFgA@mail.gmail.com>
In-Reply-To: 
 <CA+K2RumOaH-daBGN6uTo6+_0XSg7HQ10Na8OzScCV5j6eKkFgA@mail.gmail.com>
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Mon, 29 Dec 2025 11:03:17 -0600
Message-ID: 
 <CAKWEB6qa4V+aU5-S_Eq=J2o09xp=3e-iLFVqimB0Zu6iq3GKdw@mail.gmail.com>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
To: KAZAR Ayoub <ma_kazar@esi.dz>
Cc: Nazir Bilal Yavuz <byavuz81@gmail.com>, Mark Wong <markwkm@gmail.com>,
	Nathan Bossart <nathandbossart@gmail.com>,
 Andrew Dunstan <andrew@dunslane.net>,
	Shinya Kato <shinya11.kato@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e2a83806471a3893"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKWEB6qa4V%2BaU5-S_Eq%3DJ2o09xp%3D3e-iLFVqimB0Zu6iq3GKdw%40mail.gmail.com>
Precedence: bulk

--000000000000e2a83806471a3893
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Dec 24, 2025 at 9:08=E2=80=AFAM KAZAR Ayoub <ma_kazar@esi.dz> wrote=
:

> Hello,
> Following the same path of optimizing COPY FROM using SIMD, i found that
> COPY TO can also benefit from this.
>
> I attached a small patch that uses SIMD to skip data and advance as far a=
s
> the first special character is found, then fallback to scalar processing
> for that character and re-enter the SIMD path again...
> There's two ways to do this:
> 1) Essentially we do SIMD until we find a special character, then continu=
e
> scalar path without re-entering SIMD again.
> - This gives from 10% to 30% speedups depending on the weight of special
> characters in the attribute, we don't lose anything here since it advance=
s
> with SIMD until it can't (using the previous scripts: 1/3, 2/3 specials
> chars).
>
> 2) Do SIMD path, then use scalar path when we hit a special character,
> keep re-entering the SIMD path each time.
> - This is equivalent to the COPY FROM story, we'll need to find the same
> heuristic to use for both COPY FROM/TO to reduce the regressions (same
> regressions: around from 20% to 30% with 1/3, 2/3 specials chars).
>
> Something else to note is that the scalar path for COPY TO isn't as heavy
> as the state machine in COPY FROM.
>
> So if we find the sweet spot for the heuristic, doing the same for COPY T=
O
> will be trivial and always beneficial.
> Attached is 0004 which is option 1 (SIMD without re-entering), 0005 is th=
e
> second one.
>
>
> Regards,
> Ayoub
>

Hello, Nazir and Ayoub!

Nazir, sorry for the late reply, I am on holiday. :-) I wanted to thank you
for the tips on using cpupower to get less variance in my test results.

Ayoub, I suppose it was inevitable the SIMD patch would work for copying
out as well as copying in!

I am back at work on 5 Jan 2026, so I till try to carve out time to test
this then, using Nazir's tips.

Happy Holidays!

-Manni
--=20
-- Manni Wood EDB: https://www.enterprisedb.com

--000000000000e2a83806471a3893
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Dec 24,=
 2025 at 9:08=E2=80=AFAM KAZAR Ayoub &lt;<a href=3D"mailto:ma_kazar@esi.dz"=
>ma_kazar@esi.dz</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex"><div dir=3D"ltr"><div>Hello,<br></div><div>Following the sa=
me path of optimizing COPY FROM using SIMD, i found that COPY TO can also b=
enefit from this.</div><div><br></div><div>I attached a small patch that us=
es SIMD to skip data and advance as far as the first special character is f=
ound, then fallback to scalar processing for that character and re-enter th=
e SIMD path again...<br></div><div>There&#39;s two ways to do this:<br></di=
v><div>1) Essentially we do SIMD until we find a special character, then co=
ntinue scalar path without re-entering SIMD again.<br></div><div>- This giv=
es from 10% to 30% speedups depending on the weight of special characters i=
n the attribute, we don&#39;t lose anything here since it advances with SIM=
D until it can&#39;t (using the previous scripts: 1/3, 2/3 specials chars).=
</div><div><br></div><div>2) Do SIMD path, then use scalar path when we hit=
 a special character, keep re-entering the SIMD path each time.<br></div><d=
iv>- This is equivalent to the COPY FROM story, we&#39;ll need to find the =
same heuristic to use for both COPY FROM/TO to reduce the regressions (same=
 regressions: around from 20% to 30% with 1/3, 2/3 specials chars).</div><d=
iv><br></div><div>Something else to note is that the scalar path for COPY T=
O isn&#39;t as heavy as the state machine in COPY FROM.</div><div><br></div=
><div>So if we find the sweet spot for the heuristic, doing the same for CO=
PY TO will be trivial and always beneficial.</div><div>Attached is 0004 whi=
ch is option 1 (SIMD without re-entering),=C2=A00005 is the second one.<br>=
<br><br>Regards,<br></div><div>Ayoub</div></div>
</blockquote></div><div><br clear=3D"all"></div><div>Hello, Nazir and Ayoub=
!<div><br></div><div>Nazir, sorry for the late reply, I am on holiday. :-) =
I wanted to thank you for the tips on using cpupower to get less variance i=
n my test results.</div><div><br></div><div>Ayoub, I suppose it was inevita=
ble the SIMD patch would work for copying out as well as copying in!</div><=
div><br></div><div>I am back at work on 5 Jan 2026, so I till try to carve =
out time to test this then, using Nazir&#39;s tips.</div><div><br></div><di=
v>Happy Holidays!</div><div><br></div><div>-Manni</div></div><span class=3D=
"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_sign=
ature"><div dir=3D"ltr"><span style=3D"color:rgb(29,28,29);font-family:Mona=
co,Menlo,Consolas,&quot;Courier New&quot;,monospace;font-size:12px;backgrou=
nd-color:rgba(29,28,29,0.04)">--
Manni Wood
EDB: </span><a href=3D"https://www.enterprisedb.com/" rel=3D"noopener noref=
errer" style=3D"font-family:Monaco,Menlo,Consolas,&quot;Courier New&quot;,m=
onospace;font-size:12px;background-color:rgba(29,28,29,0.04)" target=3D"_bl=
ank">https://www.enterprisedb.com</a></div></div></div>

--000000000000e2a83806471a3893--