Why is postgresql update query so slow sometimes, even with index

Question

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)

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
– leonbloy, Commented May 25, 2010 at 1: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
– matija, Commented May 25, 2010 at 6: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
– araqnid, Commented May 25, 2010 at 12:46

Eric Petroelje · Accepted Answer · 2010-05-24 19:47:43Z

4

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.

answered May 24, 2010 at 19:47

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pavel Horal · Accepted Answer · 2011-07-27 12:51:10Z

3

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.

edited Jul 27, 2011 at 12:51

answered Jul 27, 2011 at 12:44

Pavel Horal

18.3k3 gold badges70 silver badges93 bronze badges

1 Comment

Matthew Cornell Over a year ago

Re: slow updates, this may also be useful: stackoverflow.com/questions/3361291/…

Collectives™ on Stack Overflow

Why is postgresql update query so slow sometimes, even with index

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related