From: Greg Smith <greg@2ndquadrant.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: since when has pg_stat_user_indexes.idx_scan been counting?
Date: Thu, 12 May 2011 16:06:49 -0400
Message-ID: <4DCC3DD9.4000402@2ndquadrant.com> (raw)
In-Reply-To: <4DCC24BD.9040900@fuzzy.cz>
References: <BANLkTikhNLPhCjE819=RFG-+CB6UV4GbUA@mail.gmail.com>
<4DCC24BD.9040900@fuzzy.cz>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, tv@fuzzy.cz
Subject: Re: since when has pg_stat_user_indexes.idx_scan been counting?
In-Reply-To: <4DCC3DD9.4000402@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox