Re: Force specific index disuse

Steve Crawford <scrawford(at)pinpointresearch(dot)com> writes:
> On 05/20/2014 10:44 AM, Alvaro Herrera wrote:
>> 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?

The index doesn't need to be rebuilt; the transaction need take only
as long as your EXPLAIN does.

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

The INSERT would insert a tuple lacking any entry in bothersome_idx,
but it doesn't matter since it'll get rolled back.

regards, tom lane