☰

表的维护：截断并重新填充后，是否需要重新索引表？

tag-icon • tag-icon database postgresql database-performance
表的维护：截断并重新填充后，是否需要重新索引表？

我有一张包含约 200 万行交易数据的表格，我们用它来进行分析。每周我们都会用新数据重新加载该表格，因此我们一直使用 TRUNCATE 来清除数据，然后插入新行。

表上有几个索引。如果我不删除并重新创建索引，每次截断和重新填充后是否都需要重新索引，或者这是否没有必要？我应该在 TRUNCATE 之后运行 VACUUM 吗，或者这是否也没有必要？

答案1

不，您通常不需要在之后重新建立索引TRUNCATE- 如果需要，最好删除索引，加载数据，然后在最后重新创建索引。

这有点类似于关于集群的这个答案- Pg 会在您插入数据时自动删除索引TRUNCATE，然后逐步重建它，因此不会出现之前遗留的索引膨胀TRUNCATE。

如果删除索引、截断、插入数据并重新创建索引，您可能会获得更紧凑、更高效的索引。它们肯定会更快地建立。对于大多数仅使用 b 树索引的应用程序来说，一旦建立索引，索引性能的差异不太可能足以证明额外的努力是值得的，但填充表所需时间的差异是值得的。如果您使用的是 GiST 或（尤其是）GIN，最好删除索引并在最后重新创建。

如果方便的话，请删除索引并在最后将其添加回来，如果这对您来说不切实际，请不要太担心。

对于我测试中的常规 B 树，增量创建的复合索引为 3720kb，而一次性创建的索引为 2208kb。构建时间为 164ms（插入）+ 347ms（索引）vs 742ms（插入+索引）。这个差异很大，但除非你正在执行大规模 DW，否则不足以引起很大的关注。REINDEX在插入+索引运行后，A 又花了 342ms。参见

因此，@TomTom 是正确的（这并不奇怪），因为它是值得的删除并重新创建如果方便的话，可以建立索引，例如，如果您要为 OLAP 工作批量填充表。

然而，重新索引可能是错误的答案因为这意味着您需要做大量昂贵的工作来创建索引，然后将其丢弃。删除索引并重新创建它，而不是重新索引。

演示环节：

regress=# -- Create, populate, then create indexes:
regress=# CREATE TABLE demo (someint integer, sometext text);
CREATE TABLE
regress=# \timing on
regress=# INSERT INTO demo (someint, sometext)
SELECT x, (x%100)::text
FROM generate_series(1,100000) x;
INSERT 0 100000
Time: 164.678 ms
regress=# CREATE INDEX composite_idx ON demo(sometext, someint);
CREATE INDEX
Time: 347.958 ms
regress=# SELECT pg_size_pretty(pg_indexes_size('demo'::regclass));
 pg_size_pretty 
----------------
 2208 kB
(1 row)
regress=# -- Total time: 347.958+164.678=512.636ms, index size 2208kB

regress=# -- Now, with truncate and insert:
regress=# TRUNCATE TABLE demo;
TRUNCATE TABLE
regress=# INSERT INTO demo (someint, sometext)
SELECT x, (x%100)::text
FROM generate_series(1,100000) x;
INSERT 0 100000
Time: 742.813 ms
regress=# SELECT pg_size_pretty(pg_indexes_size('demo'::regclass));
 pg_size_pretty 
----------------
 3720 kB
(1 row)
regress=# -- Total time 742ms, index size 3720kB
regress=# -- Difference: about 44% time increase, about 68% index size increase.
regress=# -- Big-ish, but whether you care depends on your application. Now:

regress=# REINDEX INDEX composite_idx ;
REINDEX
Time: 342.283 ms
regress=# SELECT pg_size_pretty(pg_indexes_size('demo'::regclass));
 pg_size_pretty 
----------------
 2208 kB
(1 row)

regress=# -- Index is back to same size, but total time for insert with progressive
regress=# -- index build plus reindex at the end us up to 1084.283, twice as long as
regress=# -- dropping the indexes, inserting the data, and re-creating the indexes took.

所以：

  • 对于 OLAP，删除索引，插入，重新创建索引。

  • 对于 OLTP，您可能只想坚持使用渐进式索引构建。考虑在索引上使用非 100% 填充因子来降低插入成本。

  • 避免使用渐进式索引构建进行插入然后重新索引，这是两全其美的结果。

当然，本次测试使用的尺寸是玩具桌尺寸，因此你应该在真实世界数据和索引样本上重复此测试清楚地了解它会产生多大的影响为你。我用比上述大 100 的比例因子重复了这些测试，并始终发现索引的大小几乎是增量构建的两倍，尽管相对构建时间差异实际上在这个特定测试中有所下降。

所以：用您的数据和模式进行测试。

相关内容