Re: Force specific index disuse

Steve Crawford wrote:
> 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?

Dropped relations are not deleted from disk until transaction commit, so
the original index is kept intact, and when ROLLBACK is executed only
catalog state changed by the DROP INDEX is "reverted," but it's an
instant operation.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services