Re: Delay locking partitions during INSERT and UPDATE

The following review has been posted through the commitfest application:
make installcheck-world: tested, failed
Implements feature: tested, failed
Spec compliant: tested, failed
Documentation: tested, failed

Hi,

Increasing the number of clients, I benchmarked with a table partitioned into 1k partition.
I confirmed that this patch improve performance by 10 times or more.

master (commit: 90525d7b4e Date: Tue Jan 15 12:19:21 2019 -0800)

cpu:
Xeon(R) CPU E5-2667 v3 * 2

setup:
create table history(aid int, delta int, mtime timestamp without time zone) partition by range(aid);
\o /dev/null
select 'create table history_' || x || ' partition of history for values from(' || x ||') to(' || x+1 || ')' from generate_series(1, 1000) x;
\gexec

benchmark.sql:
\set aid random(1, 1000)
\set delta random(-5000, 5000)
INSERT INTO history VALUES (:aid, :delta, CURRENT_TIMESTAMP);

command line:
pgbench -d testdb -f benchmark.sql -c number_of_clients -T 60 -r -n

Results:

clients | tps_patched | tps_unpatched | tps_unpatched / tps_patched
---------+-------------+---------------+-----------------------------
1 | 8890 | 841 | 11
2 | 17484 | 1470 | 12
4 | 29218 | 2474 | 12
8 | 48789 | 3876 | 13
16 | 68794 | 4030 | 17
32 | 69550 | 2888 | 24
64 | 71196 | 2555 | 28
128 | 71739 | 2295 | 31
256 | 66548 | 2105 | 32

I wonder why performance does not increase much when the number of clients exceeds 16.
Even though it is caused by competition of lightweight locks I thought 16 clients are small.

Also, I did make installcheck world, but test partition_prune failed.
However, this test case failed even before applying a patch, so this patch is not a problem.
One of the results is as follows.

explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Append (actual rows=0 loops=1)
- Subplans Removed: 6
-> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
-> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+ Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
-> Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-(8 rows)
+ Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
+(7 rows)

regards,
sho kato