Indexes are wide to increase IO stress and make index covering for a query. I need to revisit PG support in Linkbench to see if wide indexes there can use “include”.
Conversation
Replying to
Right -- the biggest LB index covers all columns in the table. Also, Postgres should have a storage param to force "split after new tuple" optimization, since the heuristics are too conservative to work with LinkBench despite clear benefits. (See git.postgresql.org/gitweb/?p=post)
1
1
Replying to
I expect to experiment with btree fill factor to determine whether that helps
1
2
Replying to
Check out pgstatindex() function from pgstattuple contrib extension for info on space utilization. Looks like Postgres gets classic "Natural logarithm of 2" space utilization with insert benchmark (i.e. ~69% full excluding the PK), so wouldn't expect FF to make a difference.
1
2
Replying to
I just learned of that via this post. Does it do a full index scan to compute the result or something faster?
1
Replying to
It just scans the whole index, but in physical order, without blowing out shared_buffers (it reuses a small, dedicated area of shared buffers). This seems to almost saturate I/O bandwidth. So it's very fast.
1
1
Replying to
Also observed that insert benchmark doesn't trigger a VACUUM, unless you opt to do UPDATEs/DELETEs. This affects ability to do index-only scans (VACUUM maintains visibility map). Not sure if you should consider that to be a problem in the benchmark, in Postgres, or in both.
1
Replying to
I will run linkbench soon which has insert, update and delete. Starting with insert only might have been good. Less configuration tuning to worry about while learning about PG. Can focus on checkpoint and worry about vacuum later.
1
Replying to
Makes sense. To improve space utilization for LinkBench, hack the function _bt_afternewitemoff() within nbtsplitloc.c (in Postgres 12+). Make it return true where safe, and rebuild. If you're interested I can send a patch (will take me about 3 minutes). Makes a big difference.
Replying to
Secondary indexes are ~2/3 full after inserting 5M rows with insert benchmark. This is a good & expected result given random insert order - gist.github.com/mdcallag/a96ea
1