On Sat, 6 Apr 2019 at 16:03, Tom Lane <t...@sss.pgh.pa.us> wrote: > I'd also point out that this is hardly the only place where we've > seen hash_seq_search on nearly-empty hash tables become a bottleneck. > So I'm not thrilled about attacking that with one-table-at-time patches. > I'd rather see us do something to let hash_seq_search win across > the board.
Rewinding back to mid-Feb: You wrote: > My own thought about how to improve this situation was just to destroy > and recreate LockMethodLocalHash at transaction end (or start) > if its size exceeded $some-value. Leaving it permanently bloated seems > like possibly a bad idea, even if we get rid of all the hash_seq_searches > on it. Which I thought was an okay idea. I think the one advantage that would have over making hash_seq_search() faster for large and mostly empty tables is that over-sized hash tables are just not very cache efficient, and if we don't need it to be that large then we should probably consider making it smaller again. I've had a go at implementing this and using Amit's benchmark the performance looks pretty good. I can't detect any slowdown for the general case. master: plan_cache_mode = auto: $ pgbench -n -M prepared -T 60 -f select.sql postgres tps = 9373.698212 (excluding connections establishing) tps = 9356.993148 (excluding connections establishing) tps = 9367.579806 (excluding connections establishing) plan_cache_mode = force_custom_plan: $ pgbench -n -M prepared -T 60 -f select.sql postgres tps = 12863.758185 (excluding connections establishing) tps = 12787.766054 (excluding connections establishing) tps = 12817.878940 (excluding connections establishing) shrink_bloated_locallocktable.patch: plan_cache_mode = auto: $ pgbench -n -M prepared -T 60 -f select.sql postgres tps = 12756.021211 (excluding connections establishing) tps = 12800.939518 (excluding connections establishing) tps = 12804.501977 (excluding connections establishing) plan_cache_mode = force_custom_plan: $ pgbench -n -M prepared -T 60 -f select.sql postgres tps = 12763.448836 (excluding connections establishing) tps = 12901.673271 (excluding connections establishing) tps = 12856.512745 (excluding connections establishing) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
shrink_bloated_locallocktable.patch
Description: Binary data
