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
No, but that sounds like a good idea to me