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 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.