为什么删除索引比创建索引要花费更长的时间？ [英] Why does dropping an index take longer than creating it?

问题描述

在postgresql中，我向一个大表中添加了一个索引，大约花了1秒的时间（坦率地说，这让我感到惊讶）。

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

索引，我让它运行了200秒以上而没有返回，最后取消了放置操作。

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);

花很少的时间，但是

DROP INDEX idx_cservicelocationdivisionstatus_inversed;

花了这么长时间，我放弃并取消了。

Took so long that I gave up and cancelled.

表 cservicelocationdivisionstatus 具有6列和约31万行数据。

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

为什么要删除索引比创建它需要更长的时间？

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

编辑：此页面指示，对于mySql，具有多个索引的表将复制该表并重新插入所有行，而无需删除要删除的索引。 （更糟糕的是，如果使用mySql，如果您在同一张表上删除多个索引，它将为要删除的每个索引重新复制一次表，而不是很聪明，并且在没有所有索引的情况下重新复制一次数据

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?

推荐答案

您提到的大小的表上的索引通常应该能够被丢弃的速度非常快（而且肯定比3分钟多快）。在我看来，表/索引正在使用中，因此无法删除。

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.

您可以通过查询 pg_stat_activity 表并查找涉及创建索引的表的活动来确认这一点

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

这篇关于为什么删除索引比创建索引要花费更长的时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！