Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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
1  
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 at 6:09

migrated from stackoverflow.com Sep 15 at 2:33

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

1 Answer

up vote 2 down vote

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.

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

Your Answer

 
discard

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