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

2022-12-15 Thread James Pang (chaolpan)
Did you check pg_stat_statements ? looks like select better, but DML decreased, so average tps looks similar . -Original Message- From: David Rowley Sent: Thursday, December 15, 2022 6:42 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: DML sql exe

Re: time sorted UUIDs

2022-12-15 Thread Adrien Nayrat
Tomas Vondra made an extension to have sequential uuid: https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/ https://github.com/tvondra/sequential-uuids -- Adrien NAYRAT

Re: time sorted UUIDs

2022-12-15 Thread Laurenz Albe
On Thu, 2022-12-15 at 10:56 +1300, Tim Jones wrote: > could someone please comment on this article > https://vladmihalcea.com/uuid-database-primary-key/ > specifically re the comments (copied below) in regards to a Postgres database. > > ... > But, using a random UUID as a database table Primary

time sorted UUIDs

2022-12-15 Thread Tim Jones
Hi, could someone please comment on this article https://vladmihalcea.com/uuid-database-primary-key/ specifically re the comments (copied below) in regards to a Postgres database. ... But, using a random UUID as a database table Primary Key is a bad idea for multiple reasons. First, the

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

2022-12-15 Thread James Pang (chaolpan)
Did you check pg_stat_statements ? looks like select some better , but DML decreased. -Original Message- From: David Rowley Sent: Thursday, December 15, 2022 6:42 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject: Re: DML sql execution time slow down PGv

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

2022-12-15 Thread David Rowley
On Thu, 15 Dec 2022 at 21:12, James Pang (chaolpan) 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 > e

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

2022-12-15 Thread James Pang (chaolpan)
Actually, with our application that’s JDBC clients instead of pgbench , we saw similar DML exec_time increase too. From: James Pang (chaolpan) Sent: Thursday, December 15, 2022 4:45 PM To: Samed YILDIRIM Cc: pgsql-performance@lists.postgresql.org Subject: RE: DML sql execution time slow down PG

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

2022-12-15 Thread James Pang (chaolpan)
When pgbench -i , it did already done vacuuming just before pgbench tpc-b test, below is the output of init loading. Same postgresql.conf for both v14 and v13, please check attached. date;pgbench -i -s 6000 -F 85 -U pgbench --partitions 6 Fri Dec 9 05:54:17 GMT 2022 dropping old tables... crea

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

2022-12-15 Thread Samed YILDIRIM
Hello James, Could you please add configurations of your PostgreSQL installations too? I also wonder why you skip vacuuming (-n parameter) before starting of tests. Best regards. Samed YILDIRIM On Thu, 15 Dec 2022 at 10:22, James Pang (chaolpan) wrote: > Hi, > >We had some load test ( DML

DML sql execution time slow down PGv14 compared with PGv13

2022-12-15 Thread James Pang (chaolpan)
Hi, We had some load test ( DML inserts/deletes/updates) 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 20%-30% with same SQL statements. Bot

DML sql execution time slow down PGv14 compared with PGv13

2022-12-15 Thread James Pang (chaolpan)
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%-