performance - 在 Postgres 中创建索引的最有效方法

Question

在加载数据完成之后或之前创建索引效率更高，还是无所谓？

例如，假设我有 500 个文件要加载到 Postgres 8.4 数据库中。以下是我可以使用的两个索引创建场景:

1. 创建表时创建索引，然后将每个文件加载到表中；或
2. 在所有文件都加载到表中后创建索引。

表数据本身约为 45 GB。该索引约为 12 GB。我正在使用标准索引。它是这样创建的:

CREATE INDEX idx_name ON table_name (column_name);

我的数据加载使用 COPY FROM .

一旦加载了所有文件，表上将不会发生任何更新、删除或额外加载(这是一天的数据不会改变)。所以我想问一下哪种情况最有效？初步测试似乎表明加载所有文件然后创建索引(场景 2)速度更快，但我没有对这两种方法进行科学比较。

Answer 1

您的观察是正确的——先加载数据然后再创建索引的效率要高得多。这样做的原因是插入期间的索引更新很昂贵。如果在所有数据都存在之后创建索引，速度会快得多。

更进一步 - 如果您需要将大量数据导入现有索引表，通常更有效的做法是先删除现有索引，导入数据，然后重新创建索引。

导入后创建索引的一个缺点是必须锁定表，并且可能需要很长时间(相反情况下不会被锁定)。但是，在 PostgreSQL 8.2 及更高版本中，您可以使用 CREATE INDEX CONCURRENTLY ，它在索引期间不会锁定表(有一些注意事项)。