7

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?

7

You could try DROP INDEX [ CONCURRENTLY ] name

  • CONCURRENTLY

    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 '13 at 6:08
  • 1
    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
0

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

http://www.postgresql-blog.com/

Your Answer

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