RE: Locking B-tree leafs immediately in exclusive mode

On Mon, July 9, 2018 at 5:25 PM, Simon Riggs wrote:
> Please can you check insertion with the index on 2 keys
> 1st key has 10,000 values
> 2nd key has monotonically increasing value from last 1st key value
> 
> So each session picks one 1st key value
> Then each new INSERTion is a higher value of 2nd key
> so 1,1, then 1,2 then 1,3 etc
> 
> Probably easier to do this with a table like this
> 
> CREATE UNLOGGED TABLE ordered2 (id integer, logdate timestamp default
> now(), value text not null, primary key (id, logdate));
> 
> # script_ordered2.sql
> \set i random(1, 10000)
> INSERT INTO ordered2 (id, value) VALUES (:i, 'abcdefghijklmnoprsqtuvwxyz');
> 
> Thanks
I tried to do this, but I might be mistaken your intention, so please specify if I am wrong.

While script_ordered.sql supposes that there is one contention point on the most right leaf node,
script_ordered2.sql supposes that there are some contention points on some leaf nodes, is it right?
I experimented with key1 having 10000 values, but there are no difference in the results compared to unordered.sql one, so I experimented with key1 having 1, 2, 3, 5, 10, and 100 values.
Also, If I created primary key, "ERROR:  duplicate key value violates unique constraint "ordered2_pkey" happened, so I created non-unique key.

#DDL
CREATE UNLOGGED TABLE ordered2 (id integer, logdate timestamp default now(), value text not null);
CREATE INDEX ordered2_key ON ordered2 (id, logdate);

# script_ordered2.sql
\set i random(1, 100)  #second value is 1, 2, 3, 5, 10, or 100
INSERT INTO ordered2 (id, value) VALUES (:i, 'abcdefghijklmnoprsqtuvwxyz');

# ordered2 results, key1 having 1, 2, 3, 5, 10, and 100 values
master,  key1 with 1 values:  236428
master,  key1 with 2 values:  292248
master,  key1 with 3 values:  340980
master,  key1 with 5 values:  362808
master,  key1 with 10 values: 379525
master,  key1 with 100 values: 405265

patched, key1 with 1 values:  295862
patched, key1 with 2 values:  339538
patched, key1 with 3 values:  355793
patched, key1 with 5 values:  371332
patched, key1 with 10 values: 387731
patched, key1 with 100 values: 405115


From an attached graph("some_contention_points_on_leaf_nodes.png"), as contention points dispersed, we can see that TPS is increased and TPS difference between master and patched version becomes smaller.


Yoshikazu Imai