Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I had created an index wrongly and now im trying to drop that index. Since the datas are large for my table, dropping index is taking lot of time. Is there any other way to drop the index quickly?

Thanks, Karthika

share|improve this question

migrated from Sep 15 '13 at 2:33

This question came from our site for professional and enthusiast programmers.

If it's taking that long, it's probably being locked by some other transaction. Check pg_stat_activity – a_horse_with_no_name Sep 14 '13 at 6:09

You could try DROP INDEX [ CONCURRENTLY ] name


    Drop the index without locking out concurrent selects, inserts, updates, and deletes on the index's table. A normal DROP INDEX acquires exclusive lock on the table, blocking other accesses until the index drop can be completed. With this option, the command instead waits until conflicting transactions have completed.

  • Here's the documentation for postgres 9.2:

Note: This feature is not available before postgres 9.2.

share|improve this answer
DROP INDEX CONCURRENTLY idx_treenodes_mid_nid ; shows me syntax error – user2659199 Sep 14 '13 at 6:08
What is the syntax error? Which version of postgres are you using? – Mingyu Sep 14 '13 at 6:11
ERROR: syntax error at or near "CONCURRENTLY" LINE 1: DROP INDEX CONCURRENTLY idx_treenodes_mid_nid ..... Im using 9.1 version – user2659199 Sep 14 '13 at 6:21

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.