On Wed, Jul 12, 2017 at 4:28 AM, Alik Khilazhev <a.khilaz...@postgrespro.ru> wrote: > I am attaching results of query that you sent. It shows that there is > nothing have changed after executing tests.
But something did change! In the case where performance was good, all internal pages on the level above the leaf level have exactly 285 items, excluding the rightmost page, which unsurprisingly didn't fill. However, after increasing client count to get the slow case, the "hot" part of the keyspace (the leaf pages owned by the first/leftmost internal page on that level) has 353 items rather than 285. Now, that might not seem like that much of a difference, but if you consider how duplicates are handled in the B-Tree code, and how unique index enforcement works, I think it could be. It could lead to heavy buffer lock contention, because we sometimes do a lot of work with an exclusive buffer lock held. This is something that I go into a lot of detail on in the Wiki page on Key normalization: https://wiki.postgresql.org/wiki/Key_normalization#Avoiding_unnecessary_unique_index_enforcement Now, I'm not saying that I know for sure that that's what it is. It seems like a good area to investigate, though. Even if it wasn't buffer lock contention, we're still talking about the difference between the hot part of the B-Tree being about 353 pages, versus 285. Buffer lock contention could naturally limit the growth in size to "only" 353, by slowing everything down. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers