Re: Memory usage - indexes

On 24 September 2010 18:23, Bob Lunney <bob_lunney@yahoo.com> 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.