8

i have a simple update query (foo column type is BOOLEAN (default false)):

update tablename set foo = true where id = 234;

which "id" is set to (primary) key, and if i run "explain analyze" i got:

Index Cond: (id = 234)
Total runtime: 0.358 ms

but still, i have plenty of unexplained queries at slow log (pgfouine), which took more than 200s (?!):

Times executed: 99, Av. duration (s): 70

can anyone please explain, whats the reason for that? (1.5 mio rows in table, postgresql 8.4)

matija
  • 173
  • 2
  • 5
  • If `id` is PK (so there is at most one updated row) this should be practically instantaneous. Either you have some problem (run vacuum analyze verbose) or some other query is locking the whole table... Can't you ideintify those 200s queries in your postgresql log ? Did you set the "log_duration" parameter in your config ? – leonbloy May 25 '10 at 01:02
  • yes, i set "log_min_duration_statement" to 1000, thats why i got those queries in slowlog file. identified those 200s queries from that log file and are just these simple updates. in which case postgresql lock whole table, isnt this managed by row level locking? is there any way to identify "who" locks this? is it possible that this slow query is cause of newly created row? just guessing, i have no other idea – matija May 25 '10 at 06:56
  • in postgresql, updates don't lock out readers at all, so locking may not be the issue at all. have you tuned parameters like shared_buffers? do the queries that unexepectedly run slowly happen at particular times? do they happen several together or just one on its own occasionally? – araqnid May 25 '10 at 12:46
  • try doing an `explain analyze` when it's slow... – rogerdpack Nov 23 '17 at 20:13

2 Answers2

3

My first guess would be that you have some other query locking the whole table or the rows that are being updated. Your simple update is the forced to wait for that other operation to complete.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
1

Check, that you don't have any index or constraint on the updated column. If so, then the database might be doing index recalculation or constraint evaluation. These additional tasks are not included in the EXPLAIN ANALYZE result.

Other possibility is that it is slow due to I/O operations. Check this thread about UPDATE performance in Postgres.

Pavel Horal
  • 17,782
  • 3
  • 65
  • 89
  • 1
    Re: slow updates, this may also be useful: http://stackoverflow.com/questions/3361291/slow-simple-update-query-on-postgresql-database-with-3-million-rows#comment3495735_3361903 – Matthew Cornell Sep 09 '13 at 12:28