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> <CAN55FZ0FRB2OD6-oEESLvgUT4bLZQVD72pAqUqzdw7Rx5cN0ig@mail.gmail.com>
In-Reply-To: <CAN55FZ0FRB2OD6-oEESLvgUT4bLZQVD72pAqUqzdw7Rx5cN0ig@mail.gmail.com>
From: KAZAR Ayoub <ma_kazar@esi.dz>
Date: Fri, 6 Feb 2026 19:11:37 +0100
Message-ID: <CA+K2Run1VdLnmp-5_Qv2Fax0KgT7LLJMH-uzjaaf-NZD1oU-=w@mail.gmail.com>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
To: Nazir Bilal Yavuz <byavuz81@gmail.com>
Cc: Neil Conway <neil.conway@gmail.com>, Manni Wood <manni.wood@enterprisedb.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="0000000000000d2451064a2bb96c"
Archived-At: <https://www.postgresql.org/message-id/CA%2BK2Run1VdLnmp-5_Qv2Fax0KgT7LLJMH-uzjaaf-NZD1oU-%3Dw%40mail.gmail.com>
Precedence: bulk

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

Hello,

On Fri, Feb 6, 2026 at 2:51=E2=80=AFPM Nazir Bilal Yavuz <byavuz81@gmail.co=
m> wrote:

> Hi,
>
> On Sat, 31 Jan 2026 at 19:21, KAZAR Ayoub <ma_kazar@esi.dz> wrote:
> >
> > On Wed, Jan 21, 2026 at 9:50=E2=80=AFPM Neil Conway <neil.conway@gmail.=
com>
> wrote:
> >>
> >> * I'm curious if we'll see better performance on large 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'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:
> > Workloadv3v3.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:
> > WorkloadMasterv3v3.1 (2k)v3.1 (4k)v3.1 (8k)v3.1 (16k)v3.1 (20k)v3.1 (28=
k)
> > text/none0.20%0.23%0.21%0.22%0.21%0.21%0.21%0.22%
> > text/esc0.21%0.22%0.22%0.22%0.22%0.21%0.22%0.22%
> > csv/none0.17%0.22%0.21%0.22%0.21%0.21%0.22%0.22%
> > csv/quote0.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
> but execution times are saying other things, doing the periodic filling o=
f
> line_buf seems good to do.
> > If Manni can rerun the benchmarks on these too, it would be nice to
> confirm this.
>
> I looked at this change and had a couple of points.
>
> We already have REFILL_LINEBUF at the start of the for loop in the
> CopyReadLineText() function (let=E2=80=99s call this refill #1). This ref=
ills
> when the input_buf_ptr >=3D copy_buf_len check is true. On my end,
> copy_buf_len stays at 8191 until the end of the input, and then it
> becomes the remaining amount. So when I set LINE_BUF_FLUSH_AFTER to
> 8192, the REFILL_LINEBUF you added shouldn=E2=80=99t be called; instead,
> refill #1 should be triggered.
>
> I verified this manually by adding some logging, and the results seem
> to confirm this behavior. Based on that, there shouldn=E2=80=99t be a
> performance difference when LINE_BUF_FLUSH_AFTER >=3D 8k.
>

> Could you please take a look and confirm whether you see the same behavio=
r?
>
So just to make sure i understand this correctly, line_buf holds processed
bytes of ONE line, so for the periodic flush that i did in:
input_buf_ptr - cstate->input_buf_index >=3D LINE_BUF_FLUSH_AFTER
if a line in the file is smaller than LINE_BUF_FLUSH_AFTER, the #2
REFILL_LINEBUF is never reached in a CopyReadLine entrance, as line_buf is
cleared after a line:

CopyReadLine(CopyFromState cstate, bool is_csv)
{
        bool result;
        resetStringInfo(&cstate->line_buf);
....
}

So my previous benchmarks (ones that have LINE_BUF_FLUSH_AFTER > 4096) are
wrong since I was working with lines of 4096 bytes.
If: Line length < LINE_BUF_FLUSH_AFTER < INPUT_BUF_SIZE : This neither hits
#1 REFILL_LINEBUF nor #2 PERIODIC REFILL_LINEBUF, this only reaches the
#3 REFILL_LINEBUF before CopyReadLineText returns.

So flushing here is just mitigating against l1d cache misses for long lines
(lines that occupy for example  > 70% input_buf size, maybe ?)
Does this make sense for your case too with 8k ?
So i propose removing REFILL_LINEBUF in #1, as it doesn't make sense
anymore since PERIODIC REFILL_LINEBUF already does the job for smaller
sizes than INPUT_BUF_SIZE (in accordance to most l1d cache sizes).
So basically it becomes
If (line length < LINE_BUF_FLUSH_AFTER) then flush at very end
if (line length > LINE_BUF_FLUSH_AFTER) flush (line length
/ LINE_BUF_FLUSH_AFTER) + leftover times


> Also, I noticed that json.c uses ESCAPE_JSON_FLUSH_AFTER set to 512,
> so it might be worth trying smaller values here as well.

If I'm correct about the usage of LINE_BUF_FLUSH_AFTER above, I think
smaller values would imply too many memory loads that are unnecessary, as
for 512 we aren't battling against l1d cache misses anymore, though I'll
try it in the next benchmark.
If this sounds right, I'll be re-benchmarking for multiple row sizes with
different LINE_BUF_FLUSH_AFTER sizes.

Regards,
Ayoub

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Hello,</div><br><div class=3D"gmail_=
quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Feb 6, 2026 at 2:51=E2=
=80=AFPM Nazir Bilal Yavuz &lt;<a href=3D"mailto:byavuz81@gmail.com" target=
=3D"_blank">byavuz81@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">Hi,<br>
<br>
On Sat, 31 Jan 2026 at 19:21, 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; On Wed, Jan 21, 2026 at 9:50=E2=80=AFPM Neil Conway &lt;<a href=3D"mai=
lto:neil.conway@gmail.com" target=3D"_blank">neil.conway@gmail.com</a>&gt; =
wrote:<br>
&gt;&gt;<br>
&gt;&gt; * I&#39;m curious if we&#39;ll see better performance on large inp=
uts if we flush to `line_buf` periodically (e.g., at least every few thousa=
nd bytes or so). Otherwise we might see poor data cache behavior if large i=
nputs with no control characters get evicted before we&#39;ve copied them o=
ver. See the approach taken in escape_json_with_len() in utils/adt/json.c<b=
r>
&gt;&gt;<br>
&gt; So i gave this a try, attached is the small patch that has v3 + the su=
ggestion added, here are the results with different threshold for line_buf =
refill:<br>
&gt;<br>
&gt; Execution time compared to master:<br>
&gt; Workloadv3v3.1 (2k)v3.1 (4k)v3.1 (8k)v3.1 (16k)v3.1 (20k)v3.1 (28k)<br=
>
&gt; text/none-16.5%-17.4%-14.3%-12.6%-13.6%-10.5%-16.3%<br>
&gt; text/esc+5.6%+11.1%+3.1%+7.6%+3.0%+4.9%+4.2%<br>
&gt; csv/none-31.0%-29.9%-26.7%-30.1%-27.9%-30.2%-29.6%<br>
&gt; csv/quote+0.2%-0.6%-0.4%-1.0%+0.1%+2.5%-1.0%<br>
&gt;<br>
&gt; L1d cache miss rates:<br>
&gt; WorkloadMasterv3v3.1 (2k)v3.1 (4k)v3.1 (8k)v3.1 (16k)v3.1 (20k)v3.1 (2=
8k)<br>
&gt; text/none0.20%0.23%0.21%0.22%0.21%0.21%0.21%0.22%<br>
&gt; text/esc0.21%0.22%0.22%0.22%0.22%0.21%0.22%0.22%<br>
&gt; csv/none0.17%0.22%0.21%0.22%0.21%0.21%0.22%0.22%<br>
&gt; csv/quote0.18%0.22%0.19%0.20%0.20%0.19%0.20%0.20%<br>
&gt;<br>
&gt; On my laptop I have 32KB L1 cache per core.<br>
&gt; Results are super close, it is hard to see in the cache misses numbers=
 but execution times are saying other things, doing the periodic filling of=
 line_buf seems good to do.<br>
&gt; If Manni can rerun the benchmarks on these too, it would be nice to co=
nfirm this.<br>
<br>
I looked at this change and had a couple of points.<br>
<br>
We already have REFILL_LINEBUF at the start of the for loop in the<br>
CopyReadLineText() function (let=E2=80=99s call this refill #1). This refil=
ls<br>
when the input_buf_ptr &gt;=3D copy_buf_len check is true. On my end,<br>
copy_buf_len stays at 8191 until the end of the input, and then it<br>
becomes the remaining amount. So when I set LINE_BUF_FLUSH_AFTER to<br>
8192, the REFILL_LINEBUF you added shouldn=E2=80=99t be called; instead,<br=
>
refill #1 should be triggered.<br>
<br>
I verified this manually by adding some logging, and the results seem<br>
to confirm this behavior. Based on that, there shouldn=E2=80=99t be a<br>
performance difference when LINE_BUF_FLUSH_AFTER &gt;=3D 8k.<br></blockquot=
e><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
Could you please take a look and confirm whether you see the same behavior?=
<br></blockquote><div><div><div>So just to make sure i understand this corr=
ectly, line_buf holds processed bytes of ONE line, so for the periodic flus=
h that i did in:<br></div><div>input_buf_ptr - cstate-&gt;input_buf_index &=
gt;=3D LINE_BUF_FLUSH_AFTER<br></div><div>if a line in the file is smaller =
than=C2=A0LINE_BUF_FLUSH_AFTER, the #2 REFILL_LINEBUF is never reached in a=
 CopyReadLine entrance, as line_buf is cleared after a line:<br><br>CopyRea=
dLine(CopyFromState cstate, bool is_csv)<br>{<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 bool result;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 resetStringInfo(&amp;cstate=
-&gt;line_buf);<br>....<br>}<br><br></div><div>So my previous benchmarks (o=
nes that have=C2=A0LINE_BUF_FLUSH_AFTER &gt; 4096) are wrong since I was wo=
rking with lines of 4096 bytes.<br></div><div>If: Line length &lt; LINE_BUF=
_FLUSH_AFTER &lt; INPUT_BUF_SIZE : This neither hits #1 REFILL_LINEBUF nor =
#2 PERIODIC REFILL_LINEBUF, this only reaches the #3=C2=A0REFILL_LINEBUF be=
fore CopyReadLineText returns.</div><div><br></div><div>So flushing here is=
 just mitigating against l1d cache misses for long lines (lines that occupy=
 for example=C2=A0 &gt; 70% input_buf size, maybe ?)</div><div>Does this ma=
ke sense for your case too with 8k ?<br></div><div>So i propose removing RE=
FILL_LINEBUF in #1, as it doesn&#39;t make sense anymore since=C2=A0PERIODI=
C REFILL_LINEBUF already does the job for smaller sizes than INPUT_BUF_SIZE=
 (in accordance to most l1d cache sizes).</div><div>So basically it becomes=
<br></div><div>If (line length &lt;=C2=A0LINE_BUF_FLUSH_AFTER) then flush a=
t very end<br></div><div>if (line length &gt;=C2=A0LINE_BUF_FLUSH_AFTER) fl=
ush (line length /=C2=A0LINE_BUF_FLUSH_AFTER)=C2=A0+ leftover times</div></=
div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
Also, I noticed that json.c uses ESCAPE_JSON_FLUSH_AFTER set to 512,<br>
so it might be worth trying smaller values here as well.=C2=A0</blockquote>=
<div>If I&#39;m correct about the usage of LINE_BUF_FLUSH_AFTER above, I th=
ink smaller values would imply too many memory loads that are unnecessary, =
as for 512 we aren&#39;t=C2=A0battling against l1d cache misses anymore, th=
ough I&#39;ll try it in the next benchmark.=C2=A0<br></div><div>If this sou=
nds right, I&#39;ll be re-benchmarking=C2=A0for multiple row sizes with dif=
ferent=C2=A0LINE_BUF_FLUSH_AFTER sizes.<br><br></div><div>Regards,<br></div=
><div>Ayoub</div></div></div>
</div>

--0000000000000d2451064a2bb96c--