Simple update query is slow

From: Nandakumar M <m(dot)nanda92(at)gmail(dot)com>
To: Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Simple update query is slow
Date: 2020-11-21 20:27:57
Message-ID: CANcFUu6GG_i9g_cF4nHd3DNw16MAR-4Kx360-YD8E8Bb4veOgw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I noticed something strange in our PG server. I have a table named
'timetable' that has only one bigint column and one row.

Once in every 5 seconds this row is updated to the current time epoch
value in milliseconds.

The update query seems to be taking considerable time (avg 50
milliseconds). When I tried generating the explain (analyze,buffers)
for the query, the planning time + execution time is always less than
0.1 millisecond. However the query time as shown when /timing of psql
is enabled shows approx 30 milliseconds (I am connecting via psql from
the localhost).

Please find the details below.

postgres=> select version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.15 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

Time: 0.572 ms

postgres=> \d+ timetable
Table "public.timetable"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------+-----------+---------+--------------+-------------
time | bigint | | plain | |

postgres=> table timetable ;
time
------------
1605988584
(1 row)

Time: 0.402 ms

postgres=> explain (analyze,buffers,verbose) update timetable set time = time+0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Update on public.timetable (cost=0.00..4.01 rows=1 width=14) (actual
time=0.064..0.064 rows=0 loops=1)
Buffers: shared hit=5
-> Seq Scan on public.timetable (cost=0.00..4.01 rows=1 width=14)
(actual time=0.029..0.029 rows=1 loops=1)
Output: ("time" + 0), ctid
Buffers: shared hit=4
Planning time: 0.054 ms
Execution time: 0.093 ms
(7 rows)

Time: 27.685 ms

Sometimes this shoots up to even a few hundred milliseconds.

postgres=> explain (analyze,buffers,verbose) update timetable set time = time+0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Update on public.timetable (cost=0.00..4.01 rows=1 width=14) (actual
time=0.048..0.048 rows=0 loops=1)
Buffers: shared hit=5
-> Seq Scan on public.timetable (cost=0.00..4.01 rows=1 width=14)
(actual time=0.027..0.028 rows=1 loops=1)
Output: ("time" + 0), ctid
Buffers: shared hit=4
Planning time: 0.063 ms
Execution time: 0.084 ms
(7 rows)

Time: 291.090 ms

I guess the problem here may somehow be linked to frequent updates to
the one row. However I want to understand what exactly is going wrong
here. Also I don't understand the discrepancy between planning +
execution time from explain analyze and the time taken by the query as
reported in pg log and in psql console.

Kindly help me on this.

Regards,
Nanda

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Nandakumar M 2020-11-21 20:48:10 Re: Simple update query is slow
Previous Message Alexey Bashtanov 2020-11-19 13:38:46 Re: How to prioritise walsender reading from pg_wal over WAL writes?