Message-ID: <4DCC3DD9.4000402@2ndquadrant.com>
Date: Thu, 12 May 2011 16:06:49 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Tomas Vondra <tv@fuzzy.cz>
CC: pgsql-performance@postgresql.org
Subject: Re: since when has pg_stat_user_indexes.idx_scan been counting?
References: <BANLkTikhNLPhCjE819=RFG-+CB6UV4GbUA@mail.gmail.com>
	<4DCC24BD.9040900@fuzzy.cz>
In-Reply-To: <4DCC24BD.9040900@fuzzy.cz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tomas Vondra wrote:
> BTW it's really really tricky to remove indexes once they're created.
> What if the index is created for a single batch process that runs once a
> year to close the fiscal year etc?
>   

True in theory.  Reports that are executing something big at the end of 
the year fall into three categories:

1) They touch a whole lot of the data for the year first.  In this case, 
sequential scan is likely regardless.

2) They access data similarly to regular queries, using the same indexes.

3) They have some very specific data only they touch that is retrieved 
with an index.

You're saying to watch out for (3); I think that's not usually the case, 
but that's a fair thing to warn about.  Even in that case, though, it 
may still be worth dropping the index.  Year-end processes are not 
usually very sensitive to whether they take a little or a long time to 
execute.  But you will be paying to maintain the index every day while 
it is there.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books