Just some questions, which might be helpful. What size is this index? What is underlying table size? Is ANALYZE running regularly (autovacuum or manual)? What are stats for exported_when column (pg_stats)? Did you look at pg_locks during this lengthy update? Do you have many concurrent statements which involve on this table? Did you cross out CPU and I/O contention?
On Mon, Jan 28, 2013 at 2:15 PM, Scott Ribe <scott_r...@elevated-dev.com>wrote: > 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 >
