On Mon, 8 Apr 2019 at 04:09, Tom Lane <tgl@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