Re: update performance of degenerate index

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_ribe(at)elevated-dev(dot)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_ribe(at)elevated-dev(dot)com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>