On Tue, Feb 13, 2018 at 7:47 PM, Thomas Munro <thomas.mu...@enterprisedb.com> wrote: > One way to avoid all that might be to use pseudo page numbers that > don't suffer from splits. I don't know how you'd choose the > constant, but it could be something like pseudo page number = hash > value % 1024. In other words, you'd use the full hash value for the > 'tuple' part of the predicate lock tag, and a shorter hash value for > the 'page' part of the predicate lock tag, so you'd never have to > handle split, and promotion from fine grained 'tuple' (= hash value) > locks to coarse grained 'page' = (short hash value) locks would still > work automatically when space runs out.
Thinking about how to tune that got me thinking about a simple middle way we could perhaps consider... What if we just always locked pseudo page numbers using hash_value % max_predicate_locks_per_relation (= effectively 31 by default)? Then you'd have lower collision rates on small hash indexes, you'd never have to deal with page splits, and you'd never exceed max_predicate_locks_per_relation so you'd never escalate to relation level locks on busy systems. On the downside, you'd have eg ~3% chance of collision instead of a 1/hash_maxbucket chance of collision, so it gets a bit worse for large indexes on systems that are not busy enough to exceed max_predicate_locks_per_relation. You win some, you lose some... -- Thomas Munro http://www.enterprisedb.com