DML sql execution time slow down PGv14 compared with PGv13

["James Pang (chaolpan)" <chaolpan@xxxxxxxxx>]

Hi,

   We had some load test ( DML inserts/deletes/updates/ on tens of hash partition tables)  and found that PGV14 slow down 10-15% compared with PGV13.  Same test server, same schema tables and data. From pg_stat_statements, sql exec_time, we did found similar mean_exec_time increased from 5%-25% with same SQL statements. Both v14 and v13 give very fast sql response time, just compare the %diff from sql statements mean_exec_time.

   Now, I get a pgbench test in same server, the steps as below, it’s similar as our application workload test, small sql statement running very fast but did see v14 slow down 5-10% for DML,compared with v13.

   1.date;pgbench -i -s 6000 -F 85 -U pgbench --partition-method=hash --partitions=32

   2.reboot OS to refresh buffer

   3.run four rounds of test:  date;pgbench -c 10 -j 10 -n -T 180 -U pgbench -M prepared

 

Compare 14.6 and 13.9 on RHEL8.4,  the “add primary key” step 14.6 much fast than 13.9, but most of insert/updates slow down 5-10%.  The table is very simple and sql should be same, no idea what contribute to the sql exec_time difference?  Attached please find sql exec_time.

 

I copy the sql here too,

   

version	min_exec_time	max_exec_time	mean_exec_time	calls	SQL
13.9	0.002814	1.088559	0.004214798	3467468	INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)
14.6	0.003169	0.955241	0.004482497	3466665	INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP)
%diff	12.61549396		6.351410351
13.9	0.013449	15.638027	1.18372356	3467468	UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
14.6	0.016109	133.106913	1.228978518	3466665	UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
%diff	19.77842219		3.823101875
13.9	0.005433	2.051736	0.008532748	3467468	UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
14.6	0.00625	1.847688	0.009062454	3466665	UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
%diff	15.03773238		6.207914363

 

Thanks,

 

James

Attachment: sqlstats_v13_v14_pgbench.xlsx
Description: sqlstats_v13_v14_pgbench.xlsx