Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 12 Mar 2026 12:42:28 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Xuneng Zhou <xunengzhou@gmail.com>
Cc: Andres Freund <andres@anarazel.de>,
	pgsql-hackers <pgsql-hackers@lists.postgresql.org>,
	Nazir Bilal Yavuz <byavuz81@gmail.com>
Subject: Re: Streamify more code paths
Message-ID: <abI2JBc5J_Erlgqr@paquier.xyz>
References: 
 <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>
 <dmf5ladi2amq656myv7zjl4pj4u3v2cp3azteliauifxizljej@bmwabkp5hdpi>
 <CABPTF7XFEOHpbju_pjCFHDffP_rWJU-405c6aoQdx4JjCOBimA@mail.gmail.com>
 <CABPTF7X3RBkmOnQAoLbK-tr6o-+27fNpKPgzYZHhQCuYbP=rGA@mail.gmail.com>
 <abHflCRN5CM0d8Dc@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="BAMQTcWBw/q4CS9s"
Content-Disposition: inline
In-Reply-To: <abHflCRN5CM0d8Dc@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/abI2JBc5J_Erlgqr%40paquier.xyz>
Precedence: bulk


--BAMQTcWBw/q4CS9s
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Thu, Mar 12, 2026 at 06:33:08AM +0900, Michael Paquier wrote:
> Thanks for doing that.  On my side, I am going to look at the gin and
> hash vacuum paths first with more testing as these don't use a custom
> callback.  I don't think that I am going to need a lot of convincing,
> but I'd rather produce some numbers myself because doing something.
> I'll tweak a mounting point with the delay trick, as well.

While debug_io_direct has been helping a bit, the trick for the delay
to throttle the IO activity has helped much more with my runtime
numbers.  I have mounted a separate partition with a delay of 5ms,
disabled checkums (this part did not make a real difference), and
evicted shared buffers for relation and indexes before the VACUUM.

Then I got better numbers.  Here is an extract:
- worker=3D3:
gin_vacuum (100k tuples)   base=3D  1448.2ms  patch=3D   572.5ms   2.53x
( 60.5%)  (reads=3D175=E2=86=92104, io_time=3D1382.70=E2=86=92506.64ms)
gin_vacuum (300k tuples)   base=3D  3728.0ms  patch=3D  1332.0ms   2.80x
( 64.3%)  (reads=3D486=E2=86=92293, io_time=3D3669.89=E2=86=921266.27ms)
bloom_vacuum (100k tuples) base=3D 21826.8ms  patch=3D 17220.3ms   1.27x
( 21.1%)  (reads=3D485=E2=86=92117, io_time=3D4773.33=E2=86=92270.56ms)
bloom_vacuum (300k tuples) base=3D 67054.0ms  patch=3D 53164.7ms   1.26x
( 20.7%)  (reads=3D1431.5=E2=86=92327.5, io_time=3D13880.2=E2=86=92381.395m=
s)
- io_uring:
gin_vacuum (100k tuples)   base=3D  1240.3ms  patch=3D   360.5ms   3.44x
( 70.9%)  (reads=3D175=E2=86=92104, io_time=3D1175.35=E2=86=92299.75ms)=20
gin_vacuum (300k tuples)   base=3D  2829.9ms  patch=3D   642.0ms   4.41x
( 77.3%)  (reads=3D465.5=E2=86=92293, io_time=3D2768.46=E2=86=92579.04ms)
bloom_vacuum (100k tuples) base=3D 22121.7ms  patch=3D 17532.3ms   1.26x
( 20.7%)  (reads=3D485=E2=86=92117, io_time=3D4850.46=E2=86=92285.28ms)
bloom_vacuum (300k tuples) base=3D 67058.0ms  patch=3D 53118.0ms   1.26x
( 20.8%)  (reads=3D1431.5=E2=86=92327.5, io_time=3D13870.9=E2=86=92305.44ms)

The higher the number of tuples, the better the performance for each
individual operation, but the tests take a much longer time (tens of
seconds vs tens of minutes).  For GIN, the numbers can be quite good
once these reads are pushed.  For bloom, the runtime is improved, and
the IO numbers are much better.

At the end, I have applied these two parts.  Remains now the hash
vacuum and the two parts for pgstattuple.
--
Michael

--BAMQTcWBw/q4CS9s
Content-Type: application/pgp-signature; name=signature.asc

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmmyNiQACgkQnvQgOdby
QH2oHA/9F3CfP4ImioA5ioftDzesjiqMxhhf2QGLPPXwewPgwVZHvFssu3SWTNr2
p408ArD1IW3BCkF03eys3vLFDUWBrBuaYzJ10PJMLTNLEuTnCmQSd7Z9XC7D1aDT
gAZaPudyHSAGc1PKAKX8ZwghIvVbwnTraBHG/i+MKS6QlRk6lJn7DYIXmA/4+p0M
3BaMNi9e6m1xKLFXJ5jv9lW+N25y5UrdBu9Ao/9BQS2zkIUcV+j50yhAr8KpGksw
+VcB148PezlobbFMJ8z0MWoNPrJZ0Jv4meyyOgNNcm/yUrtJeFLE0PmAJukZAuWS
byek0QQSfkDnvqYHG8LUCcOdTjzhQSSd/xjsuYmoWlsACkkLO7T3O/6itTTwjhit
iVSwHVguMFGCG0i3Fu6tkikO2yYas4MctZqV6CoeouLr+9PuuVqsCB5BRAcfBT1m
4g9V+vAt/WINhG3NtnfpbGR5WKTMM3xm05SA3TajNfu628ifPZ2PwCCsE1rQZXXO
d+lul7AnvjuVeVkwCHWOOMkdc29XE7ZbhSUdQFLLD7THaH2VY98s61K6obWM5o9i
U6Q44ZSKk986/YT3qQuO/Hr/Xi6Dw1CmItzNRUgh/vOLQsdFfkN87Na7bZ8MxWLC
3v+gzTVqrdAuHxI3akEd/AR89pkYkYaYfOIKQhWlF2k3pO8gFPU=
=TrQ/
-----END PGP SIGNATURE-----

--BAMQTcWBw/q4CS9s--