RE: DML sql execution time slow down PGv14 compared with PGv13

Did you check pg_stat_statements ? looks like select better, but DML decreased, so average tps looks similar .

-----Original Message-----
From: David Rowley <dgrowleyml(at)gmail(dot)com>
Sent: Thursday, December 15, 2022 6:42 PM
To: James Pang (chaolpan) <chaolpan(at)cisco(dot)com>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: DML sql execution time slow down PGv14 compared with PGv13

On Thu, 15 Dec 2022 at 21:12, James Pang (chaolpan) <chaolpan(at)cisco(dot)com> wrote:
> 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.

I tried this out on the tip of the PG13 and PG14 branch with the same scale of pgbench as you mentioned and I don't see the same slowdown as you do.

PG13:
tps = 1711.980109 (excluding connections establishing)

PG14:
tps = 1736.466835 (without initial connection time)

As for why yours might be slower. You might want to have a look at the EXPLAIN ANALYZE output for the UPDATE statements. You can recreate the -M prepared by using PREPARE and EXECUTE. You might want to execute the statements 6 times and see if the plan changes on the 6th execution. It's likely not impossible that PG14 is using custom plans, whereas PG13 might be using generic plans for these updates.
There were some quite significant changes made to the query planner in
PG14 that changed how planning works for UPDATEs and DELETEs from partitioned tables. Perhaps there's some reason there that the custom/generic plan choice might differ. I see no reason why INSERT would have become slower. Both the query planning and execution is very different for INSERT.

You might also want to have a look at what perf says. If you have the debug symbols installed, then you could just watch "perf top --pid=<pg backend running the pgbench workload>". Maybe that will show you something interesting.