postgresql 为什么删除索引比创建索引花费的时间更长？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26346147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does dropping an index take longer than creating it?
提问by Martin Carney
In postgresql, I added an index to a large table, and it took about 1 second (which, frankly, surprised me).
在 postgresql 中，我为一个大表添加了一个索引，大约花了 1 秒（坦率地说，这让我感到惊讶）。
When I went to drop the index, I let it run for >200 seconds without it returning, and finally cancelled the drop operation.
当我去drop索引的时候，我让它运行了>200秒没有返回，最后取消了drop操作。
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 cservicelocationdivisionstatushas 6 columns and about 310k rows of data.
该表cservicelocationdivisionstatus有 6 列和大约 310k 行数据。
Why does removing an index take so much longer than creating it?
为什么删除索引比创建索引花费的时间长？
EDIT: This pageindicates 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?
编辑：此页面表明对于 mySql，具有多个索引的表将复制该表并重新插入所有行，而无需删除您要删除的索引。（更糟糕的是，对于 mySql，如果您在同一个表上删除多个索引，它将为您要删除的每个索引重新复制该表一次，而不是聪明地重新复制一次数据而没有您要删除的所有索引下降。）这样的事情发生在 postgres 上吗？
回答by khampson
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.
您提到的大小的表上的索引通常应该能够很快被删除（当然比 3+ 分钟更快）。在我看来，表/索引正在使用中，因此无法删除。
You can confirm that by querying the pg_stat_activitytable and looking for activity involving the table on which you created the index.
您可以通过查询pg_stat_activity表并查找涉及您在其上创建索引的表的活动来确认这一点。
