主页 / dba / 问题 / 280760
Accepted
Noxville
Asked: 2020-12-03 09:37:42 +0800 CST

Postgres REINDEX 时间估计

  • 772

我有一个尚未升级的旧数据库（postgres 10.15）。一个有问题的表上有几个大索引，其中一些已损坏并需要重新索引。由于它不在版本 12+ 上，因此我无法同时重新索引表（这意味着我需要非并发地执行此操作，这需要表写入锁） - 所以我想知道如何对如何进行一些粗略的计算重新索引需要很长时间，所以我可以计划一些维护。我的大部分研究都以“只使用 pg_stat_progress_create_index！”（这在 10 中不可用）而告终，或者人们只是说要同时使用。

该表约为 200GB，索引为 7 个索引，每个索引为 14GB（根据 pg_relation_size）。为此任务，我可以在数据库上获得约 900M/s 的恒定读取速率。是否有一个简单的指标可以用来确定需要读取多少数据才能完全重新索引？

postgresql index

2 个回答

  1. Best Answer

    您可以通过以下方式创建具有不同名称的新索引

    create index concurrently index_new on ...

    然后删除损坏的索引

    drop index concurrently index_old;

    然后您可以将新索引重命名为旧名称：

    alter index index_new rename to index_old;

    后者将需要锁，但在获得锁后的几毫秒的运行时间。因此，您不需要因写锁而停机。

    索引的定义可以从命令中获取pg_dump -s -t tablename --no-acl

    reindex concurrently这与引擎盖下的程序完全相同。但是reindex concurrently要便宜一些，因为索引重命名阶段不需要锁定。

    也广为人知的pg_repack具有使用选项重新索引表的功能--only-indexes。此选项同时实现为创建 + 删除索引。

    是否有一个简单的指标可以用来确定需要读取多少数据才能完全重新索引？

    好吧，任何索引创建concurrently都将顺序读取整个表（concurrently将读取表两次）。其他的取决于访问方法。Btree 将对所有活动元组进行排序。这是创建索引最耗时的部分，对于大型索引，工作将在临时文件中完成（记住增加maintenance_work_mem）。这部分还取决于数据类型和值。选择性较小的文本（例如某些status字段）的构建速度明显比整数序列慢。

    我没有办法估计，除了一个：测量一些数据样本上索引的创建时间：

    create table estimate_table as (
  select * from tablename 
  where created_at > '2020-01-01'
);
\dt+ estimate_table
\timing on
create index on estimate_table ...

    重新索引只是索引创建的一种特殊形式。嗯，还有一点很重要：在资源使用方面reindex table与几个没有区别。是通过调用表上的每个单独索引来实现的。因此，具有 5 个索引的表将被扫描 5 次。reindex indexreindex tablereindex_index

    • 5

  2. 唯一可靠的估计需要多长时间来自将物理备份恢复到相同的机器并在那里进行测试。

    有太多的因素会影响到这一点，否则无法得出一个好的估计。

    • 1

相关问题