Performance of UPDATE operation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux