Re: Memory usage - indexes

Tobias,

First off, what version of PostgreSQL are you running? If you have 8.4, nightly vacuuming shouldn't be necessary with properly tuned autovacuum jobs.

The pertinent difference between pg_stat_user_indexes and pg_statio_user_indexes is the latter shows the number of blocks read from disk or found in the cache. You're correct, unused indexes will remain on disk, but indexes that don't completely fit into memory must be read from disk for each index scan, and that hurts performance. (In fact, it will suddenly drop like a rock. BTDT.) By making smaller equivalent indexes on partitioned data the indexes for individual partitions are more likely to stay in memory, which is particularly important when multiple passes are made over the index by a query.

You are correct on all the points you make concerning indexes, but point 4 is the one I'm referring to. You discovered this independently yourself, according to your anecdote about the overlapping indexes.

Bob Lunney

--- On Fri, 9/24/10, Tobias Brox <tobixen(at)gmail(dot)com> wrote:

> From: Tobias Brox <tobixen(at)gmail(dot)com>
> Subject: Re: [PERFORM] Memory usage - indexes
> To: "Bob Lunney" <bob_lunney(at)yahoo(dot)com>
> Cc: pgsql-performance(at)postgresql(dot)org
> Date: Friday, September 24, 2010, 12:46 PM
> On 24 September 2010 18:23, Bob
> Lunney <bob_lunney(at)yahoo(dot)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.
>