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 of items 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 updating them one at a time, then occasionally there will be an expensive update of that index that involves touching & writing a lot of pages? If that's what's happening, great. (The processing is normally triggered by notify, and happens much faster than the rate at 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-time processing 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 small indexes sometimes takes 600ms. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
