On Tue, Feb 13, 2018 at 7:47 PM, Thomas Munro
<thomas.munro@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