Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why does dropping an index take longer than creating it?

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?

like image 660
Martin Avatar asked Oct 13 '14 18:10

Martin


People also ask

How long does it take to drop an index SQL?

This database is in Simple recovery. Execute a drop index on a table with 11Mil rows takes over 1 hour. It could be that the TSQL to drop the index is being blocked by something else running against the table. Also check to see if you auto shrink set on on the database.

What happens when an index is dropped?

Dropping an index does not cause any other objects to be dropped but might cause some packages to be invalidated. A primary key or unique key index cannot be explicitly dropped.

Does dropping an index lock a table?

A normal DROP INDEX acquires an ACCESS 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.

How long does an index creation take?

If you are just adding the single index, it should take about 10 minutes. However, it will take 100 minutes or more if you don't have that index file in memory.


1 Answers

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.

like image 77
khampson Avatar answered Oct 25 '22 21:10

khampson