I'm seeing occasional simple-looking updates take way longer than I think they should, and if my theory about it is
correct,it's not actually a problem. Consider this index, intended to provide extremely quick access to a small number
ofitems from a much larger table:
create index not_exported on exports(id) where exported_when is null
My guess is that if instead of a very small number of items, there are 1000s or 10s of 1000s of items, and a process is
updatingthem one at a time, then occasionally there will be an expensive update of that index that involves touching &
writinga lot of pages?
If that's what's happening, great. (The processing is normally triggered by notify, and happens much faster than the
rateat which these come in, so the number of items in that index should be 0 most of the time, occasionally 1 for a
second,and possibly but rarely 2 or 3 for a second. The current situation of lots of entries in it has to do with
1-timeprocessing of legacy data.)
If that can't be what's happening, then I would want to investigate further why an update of a smallish row with 3
smallindexes sometimes takes 600ms.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice