Re: Memory usage - indexes

On 25 September 2010 00:00, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Overindexed tables containing more columns than are actually selective is a
> very popular source of PostgreSQL slowdowns.  It's easy to say "oh, I look
> this data up using columns a,b,c, so lets put an index on a,b,c".  But if an
> index on a alone is 1% selective, that's probably wrong;  just index it
> instead, so that you have one lean, easy to maintain index there that's more
> likely to be in RAM at all times.  Let the CPU chew on filtering out which
> of those 1% matches also match the (b,c) criteria instead.

Hm ... yes, we have quite many of those indexes. Some of them we
can't live without. Digging out 1% out of a fat 100M table (1M rows)
when one really just needs 20 rows is just too costly. Well, I guess
we should try to have a serious walk-through to see what indexes
really are needed. After all, that really seems to be our main
problem nowadays - some frequently used indexes doesn't fit very
snuggly into memory.

> Every drop an index in a transaction block just to see how a query plan
> changes if it's not there anymore, then rollback so it never really went away?
> Great fun for this sort of experiment, try it sometime.

Yes, I was playing a bit with it long time ago ... but it seems a bit
risky to do this in the production environment ... wouldn't want
inserts to get stuck due to locks. There is also the problem that we
don't really have an overview of which queries would be affected if
dropping an index. Best thing we can do is to drop an index and
monitor the stats on seq scans, new slow queries popping up, etc.