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>
 <CAN55FZ0Nd9FL=aDSjOTJTeFAn8VNrZgWG+WbcHR+R7GkDMvUyw@mail.gmail.com>
 <CAN55FZ1fwKgGo2wEie1w2M2jzJko6cMi1NWD05Xm47_L9a3D+g@mail.gmail.com>
 <CAKWEB6oZdQhhBV3ojHLBwjQgKzfDw0fkqncurt9oi7vNsq41ww@mail.gmail.com>
 <CAN55FZ1p5UyUdTRO7iWR_ukjhJDOnpOR2rYNOq=+hcC45OuahQ@mail.gmail.com>
 <CAOW5sYZEx=fPw2wp7y2nK_-ifXFeYW4CTmFx_OQeoHFjG7rbHw@mail.gmail.com> <CA+K2Ru=C_woAnd-3-pGHoNSTR8FOf=7eeSWE1xaLt9ojVWndVg@mail.gmail.com>
In-Reply-To: <CA+K2Ru=C_woAnd-3-pGHoNSTR8FOf=7eeSWE1xaLt9ojVWndVg@mail.gmail.com>
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Mon, 2 Feb 2026 08:17:55 -0600
Message-ID: <CAKWEB6p5+3VL4s61=zD4UBFp4ybNo1NrBnBw+avXsxgjBREqew@mail.gmail.com>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
To: KAZAR Ayoub <ma_kazar@esi.dz>
Cc: Neil Conway <neil.conway@gmail.com>, Nazir Bilal Yavuz <byavuz81@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="000000000000ef79030649d7fddd"
Archived-At: <https://www.postgresql.org/message-id/CAKWEB6p5%2B3VL4s61%3DzD4UBFp4ybNo1NrBnBw%2BavXsxgjBREqew%40mail.gmail.com>
Precedence: bulk

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

On Sat, Jan 31, 2026 at 10:21=E2=80=AFAM KAZAR Ayoub <ma_kazar@esi.dz> wrot=
e:

> Hello,
>
> On Wed, Jan 21, 2026 at 9:50=E2=80=AFPM Neil Conway <neil.conway@gmail.co=
m> wrote:
>
>> A few suggestions:
>>
>> * I'm curious if we'll see better performance on large inputs if we flus=
h
>> to `line_buf` periodically (e.g., at least every few thousand bytes or s=
o).
>> Otherwise we might see poor data cache behavior if large inputs with no
>> control characters get evicted before we've copied them over. See the
>> approach taken in escape_json_with_len() in utils/adt/json.c
>>
>> So i gave this a try, attached is the small patch that has v3 + the
> suggestion added, here are the results with different threshold for
> line_buf refill:
>
> Execution time compared to master:
> Workload v3 v3.1 (2k) v3.1 (4k) v3.1 (8k) v3.1 (16k) v3.1 (20k) v3.1 (28k=
)
> text/none -16.5% -17.4% -14.3% -12.6% -13.6% -10.5% -16.3%
> text/esc +5.6% +11.1% +3.1% +7.6% +3.0% +4.9% +4.2%
> csv/none -31.0% -29.9% -26.7% -30.1% -27.9% -30.2% -29.6%
> csv/quote +0.2% -0.6% -0.4% -1.0% +0.1% +2.5% -1.0%
>
> L1d cache miss rates:
> Workload Master v3 v3.1 (2k) v3.1 (4k) v3.1 (8k) v3.1 (16k) v3.1 (20k) v3=
.1
> (28k)
> text/none 0.20% 0.23% 0.21% 0.22% 0.21% 0.21% 0.21% 0.22%
> text/esc 0.21% 0.22% 0.22% 0.22% 0.22% 0.21% 0.22% 0.22%
> csv/none 0.17% 0.22% 0.21% 0.22% 0.21% 0.21% 0.22% 0.22%
> csv/quote 0.18% 0.22% 0.19% 0.20% 0.20% 0.19% 0.20% 0.20%
> On my laptop I have 32KB L1 cache per core.
> Results are super close, it is hard to see in the cache misses numbers bu=
t
> execution times are saying other things, doing the periodic filling of
> line_buf seems good to do.
> If Manni can rerun the benchmarks on these too, it would be nice to
> confirm this.
>
>
> Regards,
> Ayoub
>

Hello, All!

Ayoub, I will try to benchmark v3.1 this week on my standalone x86 and arm
PCs. Sadly, other work has been taking priority these last couple weeks,
but I will carve out some time.

Neil, thanks so much for looking at this patch!

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

--000000000000ef79030649d7fddd
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 Sat, Jan 31,=
 2026 at 10:21=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);p=
adding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div>Hello,</div><br><di=
v class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Jan 2=
1, 2026 at 9:50=E2=80=AFPM Neil Conway &lt;<a href=3D"mailto:neil.conway@gm=
ail.com" target=3D"_blank">neil.conway@gmail.com</a>&gt; wrote:<br></div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">A few sugg=
estions:<br><br>* I&#39;m curious if we&#39;ll see better performance on la=
rge inputs if we flush to `line_buf` periodically (e.g., at least every few=
 thousand bytes or so). Otherwise we might see poor data cache behavior if =
large inputs with no control characters get evicted before we&#39;ve copied=
 them over. See the approach taken in=C2=A0escape_json_with_len() in utils/=
adt/json.c<div><br></div></div></blockquote><div>So i gave this a try, atta=
ched is the small patch that has v3 + the suggestion added, here are the re=
sults with different threshold for line_buf refill:</div><div><br></div><di=
v>Execution time compared to master:<table><thead><tr><th>Workload</th><th>=
v3</th><th>v3.1 (2k)</th><th>v3.1 (4k)</th><th>v3.1 (8k)</th><th>v3.1 (16k)=
</th><th>v3.1 (20k)</th><th>v3.1 (28k)</th></tr></thead><tbody><tr><td>text=
/none</td><td>-16.5%</td><td>-17.4%</td><td>-14.3%</td><td>-12.6%</td><td>-=
13.6%</td><td>-10.5%</td><td>-16.3%</td></tr><tr><td>text/esc</td><td>+5.6%=
</td><td>+11.1%</td><td>+3.1%</td><td>+7.6%</td><td>+3.0%</td><td>+4.9%</td=
><td>+4.2%</td></tr><tr><td>csv/none</td><td>-31.0%</td><td>-29.9%</td><td>=
-26.7%</td><td>-30.1%</td><td>-27.9%</td><td>-30.2%</td><td>-29.6%</td></tr=
><tr><td>csv/quote</td><td>+0.2%</td><td>-0.6%</td><td>-0.4%</td><td>-1.0%<=
/td><td>+0.1%</td><td>+2.5%</td><td>-1.0%</td></tr></tbody></table></div><d=
iv><br></div><div>L1d cache miss rates:</div><div><table><thead><tr><th>Wor=
kload</th><th>Master</th><th>v3</th><th>v3.1 (2k)</th><th>v3.1 (4k)</th><th=
>v3.1 (8k)</th><th>v3.1 (16k)</th><th>v3.1 (20k)</th><th>v3.1 (28k)</th></t=
r></thead><tbody><tr><td>text/none</td><td>0.20%</td><td>0.23%</td><td>0.21=
%</td><td>0.22%</td><td>0.21%</td><td>0.21%</td><td>0.21%</td><td>0.22%</td=
></tr><tr><td>text/esc</td><td>0.21%</td><td>0.22%</td><td>0.22%</td><td>0.=
22%</td><td>0.22%</td><td>0.21%</td><td>0.22%</td><td>0.22%</td></tr><tr><t=
d>csv/none</td><td>0.17%</td><td>0.22%</td><td>0.21%</td><td>0.22%</td><td>=
0.21%</td><td>0.21%</td><td>0.22%</td><td>0.22%</td></tr><tr><td>csv/quote<=
/td><td>0.18%</td><td>0.22%</td><td>0.19%</td><td>0.20%</td><td>0.20%</td><=
td>0.19%</td><td>0.20%</td><td>0.20%</td></tr></tbody></table><br></div><di=
v>On my=C2=A0laptop I have=C2=A032KB L1 cache per core.</div><div>Results a=
re super close, it is hard to see in the cache misses=C2=A0numbers but exec=
ution times are saying other things, doing the periodic filling of line_buf=
 seems good to do.<br>If Manni can rerun the benchmarks on these too, it wo=
uld be nice to confirm this.</div><div><br></div><div><br></div><div>Regard=
s,<br></div><div>Ayoub</div></div></div>
</div>
</blockquote></div><div><br clear=3D"all"></div><div>Hello, All!</div><div>=
<br></div><div>Ayoub, I will try to benchmark v3.1 this week on my standalo=
ne x86 and arm PCs. Sadly, other work has been taking priority these last c=
ouple weeks, but I will carve out some time.</div><div><br></div><div>Neil,=
 thanks so much for looking at this patch!</div><div><br></div><div>-Manni<=
/div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" =
class=3D"gmail_signature"><div dir=3D"ltr"><span style=3D"color:rgb(29,28,2=
9);font-family:Monaco,Menlo,Consolas,&quot;Courier New&quot;,monospace;font=
-size:12px;background-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>

--000000000000ef79030649d7fddd--