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>
In-Reply-To: 
 <CAKWEB6r=axZsG-s7zyWURZ-s9-s1dTV9ohkZXO0ynfLEU5ha3Q@mail.gmail.com>
From: Nazir Bilal Yavuz <byavuz81@gmail.com>
Date: Thu, 18 Dec 2025 10:35:44 +0300
Message-ID: 
 <CAN55FZ2DE2XSrFUhsOqbpBo+BtzTwsJWOD0MffvdGnHtbsPRuw@mail.gmail.com>
Subject: Re: Speed up COPY FROM text/CSV parsing using SIMD
To: Manni Wood <manni.wood@enterprisedb.com>
Cc: Mark Wong <markwkm@gmail.com>, KAZAR Ayoub <ma_kazar@esi.dz>,
	Nathan Bossart <nathandbossart@gmail.com>,
 Andrew Dunstan <andrew@dunslane.net>,
	Shinya Kato <shinya11.kato@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAN55FZ2DE2XSrFUhsOqbpBo%2BBtzTwsJWOD0MffvdGnHtbsPRuw%40mail.gmail.com>
Precedence: bulk

Hi,

On Sat, 13 Dec 2025 at 02:09, Manni Wood <manni.wood@enterprisedb.com> wrot=
e:
>
> Hello, Everyone!
>
> I have attached two files. 1) the shell script that Mark and I have been =
using to get our test results, and 2) a screenshot of a spreadsheet of my l=
atest test results. (Please let me know if there's a different format than =
a screenshot that I could share my spreadsheet in.)
>
> I took greater care this time to compile all three variants of Postgres (=
master at bfb335df, master at bfb335df with v4.2 patches installed, master =
at bfb335df with v3 patches installed) with the same gcc optimization flags=
 that would be used to build Postgres packages. To the best of my knowledge=
, the two gcc flags of greatest interest would be -g and -O2. I built all t=
hree variants of Postgres using meson like so:
>
> BRANCH=3D$(git branch --show-current)
> meson setup build --prefix=3D/home/mwood/compiled-pg-instances/${BRANCH} =
--buildtype=3Ddebugoptimized
>
> It occurred to me that in addition to end users only caring about 1) wall=
 clock time (is the speedup noticeable in "real time" or just technically f=
aster / uses less CPU?) and 2) Postgres binaries compiled with the same opt=
imization level one would get when installing Postgres from packages like .=
deb or .rpm; in other words, will the user see speedups without having do m=
anually compile postgres.
>
> My interesting finding, on my laptop (ThinkPad P14s Gen 1 running Ubuntu =
24.04.3), is different from Mark Wong's. On my laptop, using three Postgres=
 installations all compiled with the -O2 optimization flag, I see speedups =
with the v4.2 patch except for a 2% slowdown with CSV with 1/3rd quotes (a =
2% slowdown). But with Nazir's proposed v3 patch, I see improvements across=
 the board. So even for a text file with 1/3rd escape characters, and even =
with a CSV file with 1/3rd quotes, I see speedups of 11% and 26% respective=
ly.
>
> The format of these test files originally comes from Ayoub Kazar's test s=
cripts; all Mark and I have done in playing with them is make them much lar=
ger: 5,000,000 rows, based on the assumption that longer tests are better t=
ests.
>
> I find my results interesting enough that I'd be curious to know if anybo=
dy else can reproduce them. It is very interesting that Mark's results are =
noticeably different from mine.

Thank you for sharing the benchmark script! I ran the benchmarks using
your script with --buildtype=3Ddebugoptimized. My results are below:

master: 85ddcc2f4c

text, no special: 102294
text, 1/3 special: 108946
csv, no special: 121831
csv, 1/3 special: 140063

v3

text, no special: 88890 (13.1% speedup)
text, 1/3 special: 110463 (1.4% regression)
csv, no special: 89781 (26.3% speedup)
csv, 1/3 special: 147094 (5.0% regression)

v4.2

text, no special: 87785 (14.2% speedup)
text, 1/3 special: 127008 (16.6% regression)
csv, no special: 88093 (27.7% speedup)
csv, 1/3 special: 164487 (17.4% regression)

One thing I noticed is that your benchmark timings appear to have some
variance. In my runs, I did not observe differences greater than one
second between runs. It is possible that this variance is affecting
your results.

Before running the benchmarks, I use the these commands [1] to improve
result stability; they might be helpful if you are not already using
something similar:

I did this benchmark on my local and my specs are Intel i5 13600k,
32GB Memory and SATA SSD.

[1]
sudo cpupower frequency-set --governor=3Dperformance
sudo cpupower idle-set -D 0 # disable idle
echo "1" | sudo tee /sys/devices/system/cpu/intel_pstate/no_turbo (intel on=
ly)

--=20
Regards,
Nazir Bilal Yavuz
Microsoft