On 24 September 2010 18:23, Bob Lunney <bob_lunney@xxxxxxxxx> wrote: > Consult pg_statio_user_indexes to see which indexes have been used > and how much. What is the main differences between pg_statio_user_indexes and pg_stat_user_indexes? > Indexes with comparitively low usages rates aren't helping you much and are > candidates for elimination. No doubt about that - but the question was, would it really help us to drop those indexes? I think the valid reasons for dropping indexes would be: 1) To speed up inserts, updates and deletes 2) To spend less disk space 3) Eventually, speed up nightly vacuum (it wouldn't be an issue with autovacuum though) 4) To spend less memory resources? I'm not at all concerned about 1 and 2 above - we don't have any performance issues on the write part, and we have plenty of disk capacity. We are still doing the nightly vacuum thing, and it does hurt us a bit since it's dragging ever more out in time. Anyway, it's number four I'm wondering most about - is it anything to be concerned about or not for the least frequently used indexes? An index that aren't being used would just stay on disk anyway, right? And if there are limited memory resources, the indexes that are most frequently used would fill up the cache space anyway? That's my thoughts at least - are they way off? We did have similar experiences some years ago - everything was running very fine all until one day when some semi-complicated very-frequently-run selects started taking several seconds to run rather than tens of milliseconds. I found that we had two slightly overlapping indexes like this ... account_transaction(customer_id, trans_type) account_transaction(customer_id, trans_type, created) both of those indexes where heavily used. I simply dropped the first one, and the problems disappeared. I assume that both indexes up to some point fitted snuggly into memory, but one day they were competing for the limited memory space, dropping the redundant index solved the problem all until the next hardware upgrade. I would never have found those indexes searching for the least used indexes in the pg_stat(io)_user_indexes view. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
