Re: Force specific index disuse

Steve Crawford 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?
>
> What would happen if you did:
> BEGIN;
> DROP INDEX bothersome_idx;
> INSERT INTO indexed_table...;
> ROLLBACK;

DROP INDEX would take a lock, the insert would happen without updating
bothersome_idx, then the rollback would revert indexed_table back to the way
it was before the DROP INDEX was issued - both data and active indexes.
Since the table contents didn't change there is no need to rebuild any
associated indexes.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Force-specific-index-disuse-tp5804564p5804591.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.