From: Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>
To: pgsql-performance@lists.postgresql.org
Subject: Performance of UPDATE operation
Date: Mon, 13 Feb 2023 16:09:27 +0100 (CET)
Message-ID: <852709814.33938384.1676300967338.JavaMail.zimbra@desy.de> (raw)
Dear Postgres Folks,
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?
Best regards,
Tigran.
Attachments:
[application/pkcs7-signature] smime.p7s (2.2K, ../852709814.33938384.1676300967338.JavaMail.zimbra@desy.de/2-smime.p7s)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tigran.mkrtchyan@desy.de, pgsql-performance@lists.postgresql.org
Subject: Re: Performance of UPDATE operation
In-Reply-To: <852709814.33938384.1676300967338.JavaMail.zimbra@desy.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox