Postgres 多列索引需要很长时间才能完成

Question

我有一个大约 270,000,000 行的表，这就是我创建它的方式。

CREATE TABLE init_package_details AS
SELECT pcont.package_content_id as package_content_id,
pcont.activity_id as activity_id,
pc.org_id as org_id,
pc.bed_type as bed_type,
pc.is_override as is_override,
pmmap.package_id as package_id,
pcont.activity_qty as activity_qty,
pcont.charge_head as  charge_head,
pcont.activity_charge as charge,
COALESCE(pc.charge,0) - COALESCE(pc.discount,0) as package_charge 
FROM a pc
JOIN b od ON
(od.org_id = pc.org_id AND od.status='A')
JOIN c pm ON 
(pc.package_id=pm.package_id)
JOIN d pmmap ON
(pmmap.pack_master_id=pm.package_id)
JOIN e pcont ON 
(pcont.package_id=pmmap.package_id);

我需要在init_package_details表上构建索引。

此表将在大约 5-6 分钟后创建。

我创建了像这样的树索引，

CREATE INDEX init_package_details_package_content_id_idx 
ON init_package_details(package_content_id);`

这需要 10 分钟(超过创建和填充表本身的时间)

而且，当我创建另一个索引时，

CREATE INDEX init_package_details_package_act_org_bt_id_idx 
ON init_package_details(activity_id,org_id,bed_type);

它只是冻结并需要永远才能完成。我等了大约 30 分钟才手动取消它。

以下是iotop -o的统计数据，如果有帮助，

• 当我创建表时，平均速度约为 110-120 MB/s(这就是在 5-6 分钟内插入 2.7 亿行的方式)
• 当我创建第一个索引时，它的平均速度约为 70 MB/秒
• 在第二个索引上，它以 5-7 MB/s 的速度蜗牛

有人可以解释为什么会这样吗？无论如何，我可以在这里加快索引创建速度吗？

编辑 1：没有其他连接访问该表。而且，pg_stat_activity在整个运行时间内active显示为状态。这发生在事务内部(这发生在BEGIN和COMMIT之间，它在同一.sql文件中包含许多其他脚本)。

编辑2：

postgres=# show work_mem ;
work_mem
----------
5MB
(1 row)
postgres=# show maintenance_work_mem;
maintenance_work_mem
----------------------
16MB

Answer 1

构建索引需要很长时间，这很正常。

如果您没有 I/O 瓶颈，则可能是 CPU 瓶颈。

有几点可以提高性能：

• 设置maintenance_work_mem非常高。

• 使用 PostgreSQL v11 或更高版本，其中可以使用多个并行工作线程。