On Mon, 2023-02-13 at 16:09 +0100, Mkrtchyan, Tigran wrote: > Typically we expect that UPDATE is a slow operation in PostgreSQL, however, > there are cases where it's hard to understand why. In particular, I have a table like > > ``` > CREATE SEQUENCE t_inodes_inumber_seq > START WITH 1 > INCREMENT BY 1 > NO MINVALUE > NO MAXVALUE > CACHE 1; > > > CREATE TABLE t_inodes ( > inumber bigint PRIMARY KEY, > icrtime timestamp with time zone NOT NULL, > igeneration bigint NOT NULL > ); > ``` > > and a transaction that inserts and update an entry in that table: > > ``` > BEGIN; > INSERT INTO t_inodes (inumber, icrtime, igeneration) > VALUES (nextval('t_inodes_inumber_seq'), now(), 0) RETURNING inumber \gset > > UPDATE t_inodes SET igeneration = igeneration + 1 where inumber = :inumber; > END; > ``` > > The pgbench shows the following result: > > ``` > $ pgbench -h localhost -n -r -f update.sql -t 10000 -c 64 -j 64 testdb > pgbench (15.0 (Debian 15.0-1.pgdg110+1)) > transaction type: update.sql > scaling factor: 1 > query mode: simple > number of clients: 64 > number of threads: 64 > maximum number of tries: 1 > number of transactions per client: 10000 > number of transactions actually processed: 640000/640000 > number of failed transactions: 0 (0.000%) > latency average = 11.559 ms > initial connection time = 86.038 ms > tps = 5536.736898 (without initial connection time) > statement latencies in milliseconds and failures: > 0.524 0 BEGIN; > 0.819 0 INSERT INTO t_inodes (inumber, icrtime, igeneration) > 0.962 0 UPDATE t_inodes SET igeneration = igeneration + 1 where inumber = :inumber; > 9.203 0 END; > ``` > > My naive expectation will be that updating the newly inserted record should cost nothing... Are there ways > to make it less expensive? Updating a newly inserted row is about as expensive as inserting the row in the first place. You can reduce the overall impact somewhat by creating the table with a "fillfactor" below 100, in your case 90 would probably be enough. That won't speed up the UPDATE itself, but it should greatly reduce the need for VACUUM. Yours, Laurenz Albe