MIME-Version: 1.0
References: 
 <CABPTF7V3+QGC+0W9ERCcAY14jq_w_XvmwrRs9vXbi_oqv4FnTQ@mail.gmail.com>
 <CABPTF7VyePb8O-WDgs2hCCXYhZzGzdjg0N3NkxojZ=ke4SB3pA@mail.gmail.com>
 <CAN55FZ39HSsXKTSi66ASq+i4Ed5FuGXD11hmJ+8c0F0O0+ozew@mail.gmail.com>
 <CABPTF7Vd4JWSHi9N7pGTzn6xmOdtAToCe1NGbZAH8U9_mXOqpw@mail.gmail.com>
 <CABPTF7W-f_zPN442FCp4Xaopi721oDmGYimq=VhAk=F7jwYZDQ@mail.gmail.com>
 <CABPTF7VUaRnvsXqa+628YkuR4oPVRr1mR2seXTkxabfiqQ3NHw@mail.gmail.com>
 <CABPTF7VtSYmC5LZSnkJWYn9PCkxgOJd9QbtAM79qftBK-fbA4w@mail.gmail.com>
 <CABPTF7UVCkub6jFXVk-qrYd4xjgiwRt1FTFL2=rBVV9SYcgfkQ@mail.gmail.com>
 <aa_yTTUpxOCFdQ2x@paquier.xyz>
 <CABPTF7XD51Qx2043p80npKmYEd67qMagK5AW=s6LNXyZt5s2nw@mail.gmail.com>
 <6vfwrw7xci7pccrfcne5ekvjw5l2uvjsjzpmgi356h7y2rkojx@6ogr34zctcue>
In-Reply-To: <6vfwrw7xci7pccrfcne5ekvjw5l2uvjsjzpmgi356h7y2rkojx@6ogr34zctcue>
From: Xuneng Zhou <xunengzhou@gmail.com>
Date: Wed, 11 Mar 2026 10:13:02 +0800
Message-ID: 
 <CABPTF7WSDXLyX7BbWRHp2CYsyrhbZx4CEFiwP5fif4KN6B7rpA@mail.gmail.com>
Subject: Re: Streamify more code paths
To: Andres Freund <andres@anarazel.de>
Cc: Michael Paquier <michael@paquier.xyz>,
 pgsql-hackers <pgsql-hackers@lists.postgresql.org>,
	Nazir Bilal Yavuz <byavuz81@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABPTF7WSDXLyX7BbWRHp2CYsyrhbZx4CEFiwP5fif4KN6B7rpA%40mail.gmail.com>
Precedence: bulk

Hi,

On Wed, Mar 11, 2026 at 7:28=E2=80=AFAM Andres Freund <andres@anarazel.de> =
wrote:
>
> Hi,
>
> On 2026-03-10 21:23:26 +0800, Xuneng Zhou wrote:
> > On Tue, Mar 10, 2026 at 6:28=E2=80=AFPM Michael Paquier <michael@paquie=
r.xyz> wrote:
> > Thanks for running the benchmarks! The performance gains for hash,
> > gin, bloom_vacuum, and wal_logging is insignificant, likely because
> > these workloads are not I/O-bound. The default number of I/O workers
> > is three, which is fairly conservative. When I ran the benchmark
> > script with a higher number of I/O workers, some runs showed improved
> > performance.
>
> FWIW, another thing that may be an issue is that you're restarting postgr=
es
> all the time, as part of drop_caches().  That means we'll spend time relo=
ading
> catalog metadata and initializing shared buffers (the first write to a sh=
ared
> buffers page is considerably more expensive than later ones, as the backi=
ng
> memory needs to be initialized first).
>
> I found it useful to use the pg_buffercache extension (specifically
> pg_buffercache_evict_relation()) to just drop the relation that is going =
to be
> tested from shared_buffers.

Good point. I'll switch to using pg_buffercache_evict_relation() to
evict only the target relation, keeping the cluster running. That
should reduce measurement noise to some extend.

>
> > > pgstattuple_large          base=3D 12429.3ms  patch=3D 11916.8ms   1.=
04x
> > > (  4.1%)  (reads=3D206945->12983, io_time=3D6501.91->32.24ms)
> >
> > > pgstattuple_large          base=3D 12642.9ms  patch=3D 11873.5ms   1.=
06x
> > > (  6.1%)  (reads=3D206945->12983, io_time=3D6516.70->143.46ms)
> >
> > Yeah, this looks somewhat strange. The io_time has been reduced
> > significantly, which should also lead to a substantial reduction in
> > runtime.
>
> It's possible that the bottleneck just moved, e.g to the checksum computa=
tion,
> if you have data checksums enabled.
>
> It's also worth noting that likely each of the test reps measures
> something different, as likely
>   psql_run "$ROOT" "$PORT" -c "UPDATE heap_test SET data =3D data || '!' =
WHERE id % 5 =3D 0;"
>
> leads to some out-of-page updates.
>
> You're probably better off deleting some of the data in a transaction tha=
t is
> then rolled back. That will also unset all-visible, but won't otherwise c=
hange
> the layout, no matter how many test iterations you run.
>
>
> I'd also guess that you're seeing a relatively small win because you're
> updating every page. When reading every page from disk, the OS can do
> efficient readahead.  If there are only occasional misses, that does not =
work.
>

Yeah, the repeated UPDATE changes the table layout across reps. I'll switch=
 to:

BEGIN;
DELETE FROM heap_test WHERE id % N =3D 0;
ROLLBACK;

This clears the visibility map bits without altering the physical
layout, so every rep measures the same table state.

>
> > method=3Dio_uring
> > pgstattuple_large          base=3D  5551.5ms  patch=3D  3498.2ms   1.59=
x
> > ( 37.0%)  (reads=3D206945=E2=86=9212983, io_time=3D2323.49=E2=86=92207.=
14ms)
> >
> > I ran the benchmark for this test again with io_uring, and the result
> > is consistent with previous runs. I=E2=80=99m not sure what might be
> > contributing to this behavior.
>
> What does a perf profile show?  Is the query CPU bound?

The runtime in my run of pgstattuple was reduced significantly due to
the reduction in I/O time. I don=E2=80=99t think running perf on my setup
would reveal anything particularly meaningful. The script has an
option to run with perf, so perhaps Michael could try it to see
whether the query becomes CPU-bound, if he=E2=80=99s interested and has tim=
e.

> > Another code path that showed significant performance improvement is
> > pgstatindex [1]. I've incorporated the test into the script too. Here
> > are the results from my testing:
> >
> > method=3Dworker io-workers=3D12
> > pgstatindex_large          base=3D   233.8ms  patch=3D    54.1ms   4.32=
x
> > ( 76.8%)  (reads=3D27460=E2=86=921757, io_time=3D213.94=E2=86=926.31ms)
> >
> > method=3Dio_uring
> > pgstatindex_large          base=3D   224.2ms  patch=3D    56.4ms   3.98=
x
> > ( 74.9%)  (reads=3D27460=E2=86=921757, io_time=3D204.41=E2=86=924.88ms)
>
> Nice!
>
>
> Greetings,
>
> Andres Freund


--
Best,
Xuneng