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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
