MIME-Version: 1.0
References: 
 <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>
 <r2lwb3342lwrvyoz4fuwctfgfx776rm22ttaytvlinbkarakmp@gujbzwverhra>
In-Reply-To: <r2lwb3342lwrvyoz4fuwctfgfx776rm22ttaytvlinbkarakmp@gujbzwverhra>
From: Xuneng Zhou <xunengzhou@gmail.com>
Date: Wed, 11 Mar 2026 10:23:36 +0800
Message-ID: 
 <CABPTF7Us4JtnkDr31khm0=Jn8OxKbbhKC3A0SK9dS+pwDdWSjw@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/CABPTF7Us4JtnkDr31khm0%3DJn8OxKbbhKC3A0SK9dS%2BpwDdWSjw%40mail.gmail.com>
Precedence: bulk

Hi,

On Wed, Mar 11, 2026 at 8:16=E2=80=AFAM Andres Freund <andres@anarazel.de> =
wrote:
>
> Hi,
>
> On 2026-03-10 19:27:59 -0400, Andres Freund wrote:
> > > > 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 compu=
tation,
> > 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 t=
hat is
> > then rolled back. That will also unset all-visible, but won't otherwise=
 change
> > 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 no=
t work.
>
> I think that last one is a big part - if I use
>   BEGIN; DELETE FROM heap_test WHERE id % 500 =3D 0; ROLLBACK;
> (which leaves a lot of
>
> I see much bigger wins due to the pgstattuple changes.
>
>                        time buffered          time DIO
> w/o read stream        2222.078 ms            2090.239 ms
> w   read stream         299.455 ms             155.124 ms
>
> That's with local storage. io_uring, but numbers with worker are similar.
>

The results look great and interesting. This looks far better than
what I observed in my earlier tests. I=E2=80=99ll run perf for pgstattuple
without the switching to see what is keeping the CPU busy.

--=20
Best,
Xuneng