Re: Force specific index disuse

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?

What would happen if you did:
BEGIN;
DROP INDEX bothersome_idx;
INSERT INTO indexed_table...;
ROLLBACK;

Cheers,
Steve