MIME-Version: 1.0
References: 
 <CAOzEurSW8cNr6TPKsjrstnPfhf4QyQqB4tnPXGGe8N4e_v7Jig@mail.gmail.com>
 <CAN55FZ247JdiT8Sd1SRiyOJxk3Ei=pDCL4kpdP=HqLRjOhKf1Q@mail.gmail.com>
 <CAN55FZ2AxiwSah7TiQoMB==r=JKT0bOtooCB7ov4xRrGkVmJ1A@mail.gmail.com>
 <CAOzEurR5nFt=-SijfU7y0BHVcrT6RG9ovvdVfKt_uBZfEQew9w@mail.gmail.com>
 <CAOzEurSqgA69er9SzhPnXwmsVpO7-piUOuOy3dXcHOi__nSQcg@mail.gmail.com>
 <CA+K2RumC79NwWxBdofHOYo8SCSs0YCJic05Du=xOszRmoPf9FA@mail.gmail.com>
 <CAN55FZ0houfWHn8_MEEefhprZvc33jr07GrBYo+Bp2yw=TVnKA@mail.gmail.com>
In-Reply-To: 
 <CAN55FZ0houfWHn8_MEEefhprZvc33jr07GrBYo+Bp2yw=TVnKA@mail.gmail.com>
From: KAZAR Ayoub <ma_kazar@esi.dz>
Date: Thu, 14 Aug 2025 15:59:55 +0100
Message-ID: 
 <CA+K2Ru=jHuz_Wpgar4Sobtxeb33qxx=o59ToOhZ=vpmkMqErnA@mail.gmail.com>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
To: Nazir Bilal Yavuz <byavuz81@gmail.com>
Cc: Shinya Kato <shinya11.kato@gmail.com>, pgsql-hackers@postgresql.org
Content-Type: multipart/mixed; boundary="0000000000007ff47f063c548798"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BK2Ru%3DjHuz_Wpgar4Sobtxeb33qxx%3Do59ToOhZ%3DvpmkMqErnA%40mail.gmail.com>
Precedence: bulk

--0000000000007ff47f063c548798
Content-Type: multipart/alternative; boundary="0000000000007ff47e063c548796"

--0000000000007ff47e063c548796
Content-Type: text/plain; charset="UTF-8"

> Hi,
>
> On Thu, 14 Aug 2025 at 05:25, KAZAR Ayoub <ma_kazar@esi.dz> wrote:
> >
> > Following Nazir's findings about 4096 bytes being the performant line
> length, I did more benchmarks from my side on both TEXT and CSV formats
> with two different cases of normal data (no special characters) and data
> with many special characters.
> >
> > Results are con good as expected and similar to previous benchmarks
> >  ~30.9% faster copy in TEXT format
> >  ~32.4% faster copy in CSV format
> > 20%-30% reduces cycles per instructions
> >
> > In the case of doing a lot of special characters in the lines (e.g.,
> tables with large numbers of columns maybe), we obviously expect
> regressions here because of the overhead of many fallbacks to scalar
> processing.
> > Results for a 1/3 of line length of special characters:
> > ~43.9% slower copy in TEXT format
> > ~16.7% slower copy in CSV format
> > So for even less occurrences of special characters or wider distance
> between there might still be some regressions in this case, a
> non-significant case maybe, but can be treated in other patches if we
> consider to not use SIMD path sometimes.
> >
> > I hope this helps more and confirms the patch.
>
> Thanks for running that benchmark! Would you mind sharing a reproducer
> for the regression you observed?
>
> --
> Regards,
> Nazir Bilal Yavuz
> Microsoft


Of course, I attached the sql to generate the text and csv test files.
If having a 1/3 of line length of special characters can be an
exaggeration, something lower might still reproduce some regressions of
course for the same idea.

Best regards,
Ayoub Kazar

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr"><br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">Hi,<br>
<br>
On Thu, 14 Aug 2025 at 05:25, KAZAR Ayoub &lt;<a href=3D"mailto:ma_kazar@es=
i.dz" target=3D"_blank">ma_kazar@esi.dz</a>&gt; wrote:<br>
&gt;<br>
&gt; Following Nazir&#39;s findings about 4096 bytes being the performant=
=20
line length, I did more benchmarks from my side on both TEXT and CSV=20
formats with two different cases of normal data (no special characters)=20
and data with many special characters.<br>
&gt;<br>
&gt; Results are con good as expected and similar to previous benchmarks<br=
>
&gt;=C2=A0 ~30.9% faster copy in TEXT format<br>
&gt;=C2=A0 ~32.4% faster copy in CSV format<br>
&gt; 20%-30% reduces cycles per instructions<br>
&gt;<br>
&gt; In the case of doing a lot of special characters in the lines=20
(e.g., tables with large numbers of columns maybe), we obviously expect=20
regressions here because of the overhead of many fallbacks to scalar=20
processing.<br>
&gt; Results for a 1/3 of line length of special characters:<br>
&gt; ~43.9% slower copy in TEXT format<br>
&gt; ~16.7% slower copy in CSV format<br>
&gt; So for even less occurrences of special characters or wider=20
distance between there might still be some regressions in this case, a=20
non-significant case maybe, but can be treated in other patches if we=20
consider to not use SIMD path sometimes.<br>
&gt;<br>
&gt; I hope this helps more and confirms the patch.<br>
<br>
Thanks for running that benchmark! Would you mind sharing a reproducer<br>
for the regression you observed?<br>
<br>
--<br>
Regards,<br>
Nazir Bilal Yavuz<br>
Microsoft</blockquote></div><br><div>Of course, I attached the sql to gener=
ate the text and csv test files.<br></div><div>If
 having a 1/3 of line length of special characters can be an=20
exaggeration, something lower might still reproduce some regressions of cou=
rse for the same idea.<br><br></div><div>Best regards,<br></div><div>Ayoub =
Kazar</div></div></div>

--0000000000007ff47e063c548796--

--0000000000007ff47f063c548798
Content-Type: application/sql; name="simd-copy-from-bench.sql"
Content-Disposition: attachment; filename="simd-copy-from-bench.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mebiyt0i0>
X-Attachment-Id: f_mebiyt0i0

RFJPUCBUQUJMRSBJRiBFWElTVFMgdDsKQ1JFQVRFIFVOTE9HR0VEIFRBQkxFIHQgKGlkIElOVCBQ
UklNQVJZIEtFWSwgZmlsbGVyIFRFWFQpOwoKLS0gVGV4dCBhbmQgQ1NWLCBubyBzcGVjaWFsIGNo
YXJhY3RlcnMKSU5TRVJUIElOVE8gdApTRUxFQ1QgcywgcmVwZWF0KCdBJywgNDA5NikKRlJPTSBn
ZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDAwKSBBUyBzOwpDT1BZIHQgVE8gJ34vY29kaW5nL3Bvc3Rn
cmVzL3RfNDA5Nl9ub25lLnR4dCcgKEZPUk1BVCB0ZXh0KTsKQ09QWSB0IFRPICd+L2NvZGluZy9w
b3N0Z3Jlcy90XzQwOTZfbm9uZS5jc3YnIChGT1JNQVQgY3N2LCBRVU9URSAnIicpOwoKLS0gVGV4
dCwgd2l0aCB+MS8zIGVzY2FwZXMgKFwpClRSVU5DQVRFIHQ7CklOU0VSVCBJTlRPIHQKU0VMRUNU
IHMsIHJlcGVhdCgnQVxBJywgMTM2NSkgLS0gMTM2NSAqIDMgPSA0MDk1IGJ5dGVzLCAxMzY1IFwg
Y2hhcnMKRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDAwKSBBUyBzOwpDT1BZIHQgVE8gJ34v
Y29kaW5nL3Bvc3RncmVzL3RfNDA5Nl9lc2NhcGUudHh0JyAoRk9STUFUIHRleHQpOwoKLS0gQ1NW
LCB3aXRoIH4xLzMgcXVvdGVzICgiKQpUUlVOQ0FURSB0OwpJTlNFUlQgSU5UTyB0ClNFTEVDVCBz
LCByZXBlYXQoJ0EiQScsIDEzNjUpIC0tIDEzNjUgKiAzID0gNDA5NSBieXRlcywgMTM2NSAiIGNo
YXJzCkZST00gZ2VuZXJhdGVfc2VyaWVzKDEsIDEwMDAwMCkgQVMgczsKQ09QWSB0IFRPICd+L2Nv
ZGluZy9wb3N0Z3Jlcy90XzQwOTZfcXVvdGUuY3N2JyAoRk9STUFUIGNzdiwgUVVPVEUgJyInKTsK
CkRST1AgVEFCTEUgdDs=
--0000000000007ff47f063c548798--