And btw, IOS can work for an expression index – but you need to extend the index and use INCLUDE with columns used in the expression
Conversation
Replying to
Overall, great analysis! I'd love to see real-life plans with the BUFFERS numbers as well, it could improve understanding of what happened (I'm glad is adding BUFFER numbers analysis to )
1
Yeah I'm glad too, really happy with the new improvements around buffers — thanks for the encouragement there!
Regarding index-only scans for expression indexes, Zeke is planning to try it: twitter.com/ezekgabrielse/
😄
This Tweet is unavailable.
1
This Tweet was deleted by the Tweet author. Learn more
Super interesting! I’m not sure why it’s twice(?) as slow, buffers might show something interesting, but from the timings it looks like the scans are taking longer than the aggregation. Thanks again for sharing
1
1
Ah, and turn off JIT unless you have analytical type of workload, not OLTP
1
Plus, if you want to benefit from index only scans, definitely you need to keep visibility maps up to date -- run VACUUM on the table (and for persistent effect, tune autovacuum to run more often)
2
Postgres 15 will finally show how many heap pages VACUUM scanned in av log output (both as an absolute count of pages, and as a percentage of total heap pages for the table). This is very basic info, but may still give useful feedback on how the VM gets set over time.
1
1
4
Cool!
Wasn't it ever discussed to log the moment when autovacuum *starts* to process some table/indexes and write details of what it expects to do with it/them?
2
1
Related pg 15 news: VACUUM VERBOSE output looks like autovacuum log output now (same information appears as INFO messages in psql, not in the server log). This is far nicer to work with, especially when figuring things out on a test DB. Has WAL record info, I/O timing info, etc.
Unfortunately we're still missing basic info that you'd really expect here for VM. There is still no information about the total number of VM bits now set at the point that VACUUM ends. It wouldn't be hard, but it still didn't happen in 15.
1