pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of UPDATE operation
4+ messages / 4 participants
[nested] [flat]
* Performance of UPDATE operation
@ 2023-02-13 15:09 Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>
2023-02-13 17:47 ` Re: Performance of UPDATE operation Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-13 21:49 ` Re: Performance of UPDATE operation Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Mkrtchyan, Tigran @ 2023-02-13 15:09 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of UPDATE operation
2023-02-13 15:09 Performance of UPDATE operation Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>
@ 2023-02-13 17:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-13 20:52 ` Re: Performance of UPDATE operation Oluwatobi Ogunsola <tobfis@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2023-02-13 17:47 UTC (permalink / raw)
To: Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of UPDATE operation
2023-02-13 15:09 Performance of UPDATE operation Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>
2023-02-13 17:47 ` Re: Performance of UPDATE operation Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-02-13 20:52 ` Oluwatobi Ogunsola <tobfis@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Oluwatobi Ogunsola @ 2023-02-13 20:52 UTC (permalink / raw)
To: Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance@lists.postgresql.org
Maybe reconsider your expectation.
Note: Every “update” have to “select” before modifying data.
Even if the page is in memory, there still work…reading ,acquiring lock, modifying and request to write to disk.
Regards,
Tobi
> On 13 Feb 2023, at 18:48, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> 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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance of UPDATE operation
2023-02-13 15:09 Performance of UPDATE operation Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>
@ 2023-02-13 21:49 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2023-02-13 21:49 UTC (permalink / raw)
To: Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, Feb 13, 2023 at 10:09 AM Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>
wrote:
>
> 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
It takes less than 1/10 of the total time. That is pretty close to
nothing. Why would you expect it to be truly free?
> ... Are there ways
> to make it less expensive?
>
Obviously here you could just insert the correct value in the first place
and not do the update at all.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-02-13 21:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-13 15:09 Performance of UPDATE operation Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>
2023-02-13 17:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-02-13 20:52 ` Oluwatobi Ogunsola <tobfis@yahoo.com>
2023-02-13 21:49 ` Jeff Janes <jeff.janes@gmail.com>
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