Re: since when has pg_stat_user_indexes.idx_scan been counting?

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books