On Tue, May 20, 2014 at 11:48 AM, Steve Crawford <[hidden email]> wrote:
On 05/20/2014 10:44 AM, Alvaro Herrera wrote:
Steve Crawford wrote:
Is there a way to force a specific index to be removed from
consideration in planning a single query?
Specifically, on a 60-million-row table I have an index that is a
candidate for removal. I have identified the sets of nightly queries
that use the index but before dropping it I would like to run
EXPLAIN and do timing tests on the queries to see the impact of not
having that index available and rewrite the query to efficiently use
other indexes if necessary.
If you can afford to lock the table for a while, the easiest is
BEGIN;
DROP INDEX bothersome_idx;
EXPLAIN your_query;
ROLLBACK;
Interesting. But what do you mean by "a while?" Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback?Best case, 'A while' means however long it takes the explain (possibly analyze) to run, and for you to then type 'rollback;'worse case, someone else is already holding an incompatible lock (i.e. any lock) on the table, and is going to hang on to it for a long while, so your drop index hangs forever waiting to acquire the lock and in the process brings all other desired activity (except the one already holding the lock) to a screeching halt because they are not allowed to jump the lock queue.worser case, you forget to enter 'rollback' at all and accidentally commit the index drop.I use the begin...drop...rollback on test servers a lot, but rarely on productions servers. If I don't want to lock for that long, you can often alter the query to make the index useless, for example:explain select * from pgbench_accounts where aid=87;Becomes:explain select * from pgbench_accounts where aid+0=87;Although for complex queries it can be hard to find the antimagic bullet.Cheers,Jeff
This whole line of thought is a use-case for Jaime Casanova's recent proposal:
Note that disabling the index as shown, via clobbering a WHERE clause, doesn't help when the concern is how much time is being spent updating indexes during INSERT/UPDATE.
The risk of forgetting to issue the "BEGIN;" (not so much ROLLBACK given typical default behavior if BEGIN is indeed issued) is probably the one that would concern me the most if working on a production server.
David J.
View this message in context: Re: Force specific index disuse
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
