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=3:
gin_vacuum (100k tuples) base= 1448.2ms patch= 572.5ms 2.53x
( 60.5%) (reads=175→104, io_time=1382.70→506.64ms)
gin_vacuum (300k tuples) base= 3728.0ms patch= 1332.0ms 2.80x
( 64.3%) (reads=486→293, io_time=3669.89→1266.27ms)
bloom_vacuum (100k tuples) base= 21826.8ms patch= 17220.3ms 1.27x
( 21.1%) (reads=485→117, io_time=4773.33→270.56ms)
bloom_vacuum (300k tuples) base= 67054.0ms patch= 53164.7ms 1.26x
( 20.7%) (reads=1431.5→327.5, io_time=13880.2→381.395ms)
- io_uring:
gin_vacuum (100k tuples) base= 1240.3ms patch= 360.5ms 3.44x
( 70.9%) (reads=175→104, io_time=1175.35→299.75ms)
gin_vacuum (300k tuples) base= 2829.9ms patch= 642.0ms 4.41x
( 77.3%) (reads=465.5→293, io_time=2768.46→579.04ms)
bloom_vacuum (100k tuples) base= 22121.7ms patch= 17532.3ms 1.26x
( 20.7%) (reads=485→117, io_time=4850.46→285.28ms)
bloom_vacuum (300k tuples) base= 67058.0ms patch= 53118.0ms 1.26x
( 20.8%) (reads=1431.5→327.5, io_time=13870.9→305.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
