[FIXED] Why does dropping an index take longer than creating it?

Issue

In postgresql, I added an index to a large table, and it took about 1 second (which, frankly, surprised me).

When I went to drop the index, I let it run for >200 seconds without it returning, and finally cancelled the drop operation.

CREATE INDEX idx_cservicelocationdivisionstatus_inversed
ON cservicelocationdivisionstatus (cservicelocationdivisionid, startdate, enddate DESC);

Takes very little time, but

DROP INDEX idx_cservicelocationdivisionstatus_inversed;

Took so long that I gave up and cancelled.

The table cservicelocationdivisionstatus has 6 columns and about 310k rows of data.

Why does removing an index take so much longer than creating it?

EDIT: This page indicates that for mySql, a table with multiple indexes will copy the table and re-insert all the rows without the index you're dropping. (Worse, with mySql, if you drop multiple indexes on the same table, it will re-copy the table once for each index you're removing, instead of being smart and re-copying the data once without all the indexes you're dropping.) Is something like this happening with postgres?

Solution

An index on a table of the size you mentioned should generally be able to be dropped pretty quickly (and certainly more quickly than 3+ minutes). It sounds to me like the table/index was in use, and therefore could not be dropped.

You can confirm that by querying the pg_stat_activity table and looking for activity involving the table on which you created the index.

Answered By - khampson

Answer Checked By - Mary Flores (PHPFixing Volunteer)