主页 / dba / 问题 / 93990
Accepted
pietrop
Asked: 2015-02-28 02:58:36 +0800 CST

A 列上的聚集索引是否与创建 A 排序的表相同？

  • 772

在阅读9.0 版的官方 PostgreSQL 文档时，我读到了一个有趣的escamotage，它比CLUSTER大表表现得更好：

CLUSTER 命令通过使用您指定的索引扫描原始表来重新排序。这在大型表上可能会很慢，因为行是按索引顺序从表中获取的，如果表是无序的，则条目位于随机页面上，因此每移动一行都会检索一个磁盘页面。（PostgreSQL 有一个缓存，但大表的大部分内容不适合缓存。）集群表的另一种方法是使用：

  CREATE TABLE newtable AS
    SELECT * FROM table ORDER BY columnlist;

它使用 PostgreSQL 排序代码来生成所需的顺序；这通常比无序数据的索引扫描快得多。然后删除旧表，使用 ALTER TABLE ... RENAME 将新表重命名为旧名称，并重新创建表的索引。这种方法的最大缺点是它不保留 OID、约束、外键关系、授予的权限和表的其他辅助属性——所有这些项目都必须手动重新创建。另一个缺点是这种方式需要一个与表本身大小相同的排序临时文件，因此峰值磁盘使用量大约是表大小的三倍而不是表大小的两倍。

问题是这个建议没有出现在 > 9.0 版本的官方文档中。

我的问题是这个 escamotage 是否仍然对 9.1、9.2、9.3 和 9.4 有效，因为我被困CLUSTER在两个大表上的操作（一个有 ~750M 行，另一个有 ~1650M 行）和平均磁盘写入/读取由于CLUSTER官方文档中解释的算法，速度为 3MB/s 。对于大表来说，这是一个缓慢的过程，所以我想避免它执行“在索引关联列上创建有序表”的技巧。这将节省我几天的数据库处理时间。

postgresql clustered-index

1 个回答

  1. Best Answer

    就像@dezso 评论的那样，在旧版本中创建一个新表并删除旧表曾经更快，但在 pg 9.1 中的新实现不再如此。

    最常见的问题CLUSTER是它需要在表上使用排他锁，这对于并发访问它并不顺利。

    这个问题的解决方案是pg_repack，它不会独占锁定表。

    通常，请确保您的服务器配置适合该任务。高设置（大量内存）对大桌子都有maintenance_work_mem帮助。标准设置对你来说太小了。请点击链接了解详情。CLUSTERCREATE INDEX

    您可以暂时将其设置为非常高的交易，SET LOCAL否则将其保留在合理的设置：

    BEGIN;
SET LOCAL maintenance_work_mem = ????MB; -- find the sweet spot
CLUSTER tbl;
COMMIT;

    如果可能，将其设置得足够高以适应 RAM 中的整个操作。

    更多的：

    • 4

相关问题