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


2 Answers 2


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: http://www.postgresql.org/docs/9.2/static/sql-dropindex.html

Note: This feature is not available before postgres 9.2.

  • DROP INDEX CONCURRENTLY idx_treenodes_mid_nid ; shows me syntax error
    – user2659199
    Sep 14, 2013 at 6:08
  • 1
    What is the syntax error? Which version of postgres are you using?
    – Mingyu
    Sep 14, 2013 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, 2013 at 6:21

Somehow maintainance_work_mem is not considered as maintenance operations and it does not have any implication to speed up the drop index process.

Dropping the index concurrently seems to be the only option but I won't suggest to directly run it from Pgadmin or Psql terminal. Because if the session gets terminated or due to timeout, it may leave index inconsistent.

Hope this helps. Aj


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy