On 25 September 2010 00:00, Greg Smith <greg@xxxxxxxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
