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?
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.
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
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