On Mon, 8 Apr 2019 at 04:09, Tom Lane <t...@sss.pgh.pa.us> wrote: > Um ... I don't see where you're destroying the old hash?
In CreateLocalLockHash. > Also, I entirely dislike wiring in assumptions about hash_seq_search's > private state structure here. I think it's worth having an explicit > entry point in dynahash.c to get the current number of buckets. Okay. Added hash_get_max_bucket() > Also, I would not define "significantly bloated" as "the table has > grown at all". I think the threshold ought to be at least ~100 > buckets, if we're starting at 16. I wouldn't either. I don't think the comment says that. It says there can be slowdowns when its significantly bloated, and then goes on to say that we just resize when it's bigger than standard. > Probably we ought to try to gather some evidence to inform the > choice of cutoff here. Maybe instrument the regression tests to > see how big the table typically gets? In partition_prune.sql I see use of a bucket as high as 285 on my machine with: drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; I've not added any sort of cut-off though as I benchmarked it and surprisingly I don't see any slowdown with the worst case. So I'm thinking there might not be any point. alter system set plan_cache_mode = 'force_generic_plan'; create table hp (a int primary key) partition by hash (a); select 'create table hp' || x::text || ' partition of hp for values with (modulus 32, remainder ' || (x)::text || ');' from generate_series(0,31) x; \gexec select.sql \set p 1 select * from hp where a = :p Master $ pgbench -n -M prepared -f select.sql -T 60 postgres tps = 11834.764309 (excluding connections establishing) tps = 12279.212223 (excluding connections establishing) tps = 12007.263547 (excluding connections establishing) Patched: $ pgbench -n -M prepared -f select.sql -T 60 postgres tps = 13380.684817 (excluding connections establishing) tps = 12790.999279 (excluding connections establishing) tps = 12568.892558 (excluding connections establishing) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
shrink_bloated_locallocktable_v3.patch
Description: Binary data
