Did you check pg_stat_statements ? looks like select some better , but DML decreased.
-----Original Message-----
From: David Rowley <dgrowleyml@gmail.com>
Sent: Thursday, December 15, 2022 6:42 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.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@cisco.com> wrote:
> We had some load test ( DML inserts/deletes/updates/ on tens of hash partition tables) and found that PGV14 slow
down10-15% compared with PGV13. Same test server, same schema tables and data. From pg_stat_statements, sql exec_time,
wedid found similar mean_exec_time increased from 5%-25% with same SQL statements. Both v14 and v13 give very fast sql
responsetime, 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
thesame 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
timesand see if the plan changes on the 6th execution. It's likely not impossible that PG14 is using custom plans,
whereasPG13 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
therethat the custom/generic plan choice might differ. I see no reason why INSERT would have become slower. Both the
queryplanning 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.
