MIME-Version: 1.0
References: 
 <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>
 <CA+K2Ru=jHuz_Wpgar4Sobtxeb33qxx=o59ToOhZ=vpmkMqErnA@mail.gmail.com>
 <CAN55FZ1J+6eM=F5GreWEBMJcNV_gifYyYY1b6xpYzun=nWPhMQ@mail.gmail.com>
 <CAN55FZ109W90Ux_EBEqkkU2TyNqBNhdhN_1XPRGo3iiZ2L9b=A@mail.gmail.com>
 <8615c983-1662-43b4-b0c9-49d194ac33aa@dunslane.net>
 <CAN55FZ1KF7XNpm2XyG=M-sFUODai=6Z8a11xE3s4YRBeBKY3tA@mail.gmail.com>
 <673d92f7-2489-475f-a208-9414ea35d4d8@dunslane.net> <aPZrg6lxb5bgy_px@nathan>
 <8e045899-2023-48b1-bd91-f8cdffeb511d@dunslane.net>
In-Reply-To: <8e045899-2023-48b1-bd91-f8cdffeb511d@dunslane.net>
From: Nazir Bilal Yavuz <byavuz81@gmail.com>
Date: Tue, 21 Oct 2025 00:09:27 +0300
Message-ID: 
 <CAN55FZ2GonAeSJHn-c2nJgUO-v6sDMOQzn97evVdZbcHeu3ihw@mail.gmail.com>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Nathan Bossart <nathandbossart@gmail.com>, KAZAR Ayoub <ma_kazar@esi.dz>,
	Shinya Kato <shinya11.kato@gmail.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAN55FZ2GonAeSJHn-c2nJgUO-v6sDMOQzn97evVdZbcHeu3ihw%40mail.gmail.com>
Precedence: bulk

Hi,

On Mon, 20 Oct 2025 at 23:32, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>
> On 2025-10-20 Mo 1:04 PM, Nathan Bossart wrote:
>
> On Mon, Oct 20, 2025 at 10:02:23AM -0400, Andrew Dunstan wrote:
>
> On 2025-10-16 Th 10:29 AM, Nazir Bilal Yavuz wrote:
>
> With this heuristic the regression is limited by %2 in the worst case.
>
> My worry is that the worst case is actually quite common. Sparse data set=
s
> dominated by a lot of null values (and hence lots of special characters) =
are
> very common. Are people prepared to accept a 2% regression on load times =
for
> such data sets?
>
> Without knowing how common it is, I think it's difficult to judge whether
> 2% is a reasonable trade-off.  If <5% of workloads might see a small
> regression while the other >95% see double-digit percentage improvements,
> then I might argue that it's fine.  But I'm not sure we have any way to
> know those sorts of details at the moment.
>
>
> I guess what I don't understand is why we actually need to do the test co=
ntinuously, even using an adaptive algorithm. Data files in my experience u=
sually have lines with fairly similar shapes. It's highly unlikely that you=
 will get the the first 1000 (say) lines of a file that are rich in special=
 characters and then some later significant section that isn't, or vice ver=
sa. Therefore, doing the test once should yield the correct answer that can=
 be applied to the rest of the file. That should reduce the worst case regr=
ession to ~0% without sacrificing any of the performance gains. I appreciat=
e the elegance of what Bilal has done here, but it does seem like overkill.

I think the problem is deciding how many lines to process before
deciding for the rest. 1000 lines could work for the small sized data
but it might not work for the big sized data. Also, it might cause a
worse regressions for the small sized data. Because of this reason, I
tried to implement a heuristic that will work regardless of the size
of the data. The last heuristic I suggested will run SIMD for
approximately (#number_of_lines / 1024 [1024 is the max number of
lines to sleep before running SIMD again]) lines if all characters in
the data are special characters.

--=20
Regards,
Nazir Bilal Yavuz
Microsoft