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
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.
Here's the documentation for postgres 9.1: http://www.postgresql.org/docs/9.1/static/sql-dropindex.html
This is similar to
ONLINE = ONoption in SQL Server: http://technet.microsoft.com/en-us/library/ms176118.aspx
-
DROP INDEX CONCURRENTLY idx_treenodes_mid_nid ; shows me syntax error– user2659199Commented Sep 14, 2013 at 6:08
-
1What is the syntax error? Which version of postgres are you using?– MingyuCommented 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– user2659199Commented 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
pg_stat_activity