Clustering a Table - Bruce Momjian（译）-技术圈

写了600 多篇博客文章后，我以为我已经掌握了cluster命令的复杂性，但似乎我还没有，所以现在让我们开始吧。

Cluster是一个不寻常的sql命令，它只会影响性能。实际上， cluster需要索引的存在。那么，CLUSTER做了什么呢？，创建索引又做了什么呢？让我们看看存储在 Postgres 中是如何工作的。

用户数据行存储在文件系统的堆文件中，这些行以不确定的顺序存储。如果表最初是按insert/copy顺序加载的，以后的插入、更新和删除将导致在堆文件中以不可预测的顺序添加行。Create index创建一个二级文件，其中的条目指向堆行，索引条目被排序以匹配create index命令中指定的列中的值。通过在索引中快速查找所需值，可以跟踪索引指针以快速查找匹配的堆行。

在大多数情况下，创建索引足以产生可接受的性能。但是，在少数情况下，索引已排序，但堆无序，会导致严重的性能问题。这就是CLUSTER变得有用的地方——它对堆进行排序以匹配其中一个索引的排序。（一些非 btree 索引不能聚集，因为它们缺乏线性排序。）

这种堆排序如何提高性能？当然，如果你只查找一行，那么它在堆文件中的位置并不重要——它只需要一个堆访问来检索它。但是，假设您要检索与索引列匹配的一百行？好吧，我们也可以快速找到一百个匹配的索引条目，但是一百个堆行呢？如果它们离散的分布在在一百个 8kB 堆页面上，则需要多次I/O访问。但是，如果匹配的堆行位于相邻的堆页面上，那会减少所需的堆页面数量。如果这些堆页面都在内存中，则可能无关紧要，但如果有些在存储中，则减少堆访问次数可以产生显着的性能优势。

堆排序何时有助于提高性能？我想到三种情况：

访问具有许多重复项的单个索引值，例如col = 5，其中有许多匹配值
访问一个范围，例如col>=10 AND col<20
访问其他会话经常访问的值，例如未付发票行

对于这些工作负载，对堆进行排序可以大大减少堆访问的次数。

但是，使用CLUSTER有两个很大的缺点。首先，当 cluster 命令创建一个新的堆文件以匹配索引时，没有其他会话可以读取或写入该表。其次，与索引组织表不同（Postgres 不支持，因为它们有严重的缺点），堆不会保持聚簇的状态——稍后的插入和更新操作会将行以不确定的顺序放置在堆中，导致随着时间推移堆变得不那么有序——需要在以后继续执行cluster操作来恢复理想的排序。（非默认堆填充因子可以提高更新局部性。）幸运的是，当前的clster操作能够记得以前的cluster操作，并且可以自动地恢复到之前cluster操作的理想排序状态。

让我们看看explain如何利用被排序的堆行。实际上，这与cluster命令无关——Postgres 会根据每一列以及潜在的表达式索引维护堆如何排序，而不仅仅是之前cluster操作中涉及的列。cluster实际上只是强制堆排序的一种方式，但是堆排序可能会自然发生在一些平时的排序操作中，Postgres 可以利用这一点。

在下面的示例中，行由于它们的插入顺序而自动排序，并且对pg_stats和pg_statistic 的查询验证相关性为1：

-- 使用二列，因此不会使用仅索引扫描，因此该行具有典型长度

CREATE TABLE public.cluster_test (x INTEGER, y TEXT);
CREATE INDEX i_cluster_test ON cluster_test (x);
insert into public.cluster_test        SELECT *, repeat('x', 250) FROM generate_series(1, 100000);

-- 计算相关性统计信息

ANALYZE cluster_test;

-- 使用视图 pg_stats

SELECT correlation    FROM pg_stats    WHERE schemaname = 'public' AND tablename = 'cluster_test' AND attname = 'x'; correlation-------------1

-- 使用表 pg_statistic，SQL 来自 psql 的 '\d+ pg_stats'

SELECT CASE        WHEN stakind1 = 3 THEN stanumbers1[1]        WHEN stakind2 = 3 THEN stanumbers2[1]        WHEN stakind3 = 3 THEN stanumbers3[1]        WHEN stakind4 = 3 THEN stanumbers4[1]        WHEN stakind5 = 3 THEN stanumbers5[1]        ELSE NULL::real        END AS correlationFROM pg_namespace JOIN pg_class ON (pg_namespace.oid = relnamespace)        JOIN pg_attribute ON (pg_class.oid = pg_attribute.attrelid)        JOIN pg_statistic ON (pg_class.oid = starelid AND pg_attribute.attnum = WHEREpublic'AND'AND' relname = 'cluster_test' AND attname = 'x'; correlation-------------1

EXPLAIN SELECT * FROM cluster_test WHERE x < 74000；                             QUERY PLAN--------------------------------------------------------------------- Index Scan using i_cluster_test on cluster_test  (cost=0.29..4841.80 rows=73743 width=258)   Index Cond: (x < 74000)

EXPLAIN SELECT * FROM cluster_test WHERE x < 75000;                             QUERY PLAN--------------------------------------------------------------------- Seq Scan on cluster_test  (cost=0.00..4954.00 rows=74752 width=258)   Filter: (x < 75000)

您可以看到 Postgres 优化器在 74k 和 75k 行访问之间从索引扫描切换到顺序扫描。下面这个示例以随机顺序插入行，这会产生接近于零的相关性，同时以及会以一个更小的值开始停止使用索引，即 28k vs 75k：

-- 使用两二列，以便不使用仅索引扫描

DELETE FROM public.cluster_test;
CREATE INDEX i_cluster_test ON cluster_test (x);
INSERT INTO public.cluster_test        SELECT *, repeat('x', 250) FROM generate_series(1, 100000) ORDER BY random();

-- 计算相关统计

ANALYZE cluster_test;

-- 使用视图 pg_stats

SELECT correlationFROM pg_statsWHERE schemaname = 'public' AND tablename = 'cluster_test' AND attname = 'x';  correlation--------------- - 0.0048559047

EXPLAIN SELECT * FROM cluster_test WHERE x < 3;                                      QUERY PLAN-------------------------------------------------------------------------------- Index Scan using i_cluster_test on cluster_test  (cost=0.42..12.45 rows=2 width=258)   Index Cond: (x < 3)(2 rows)

EXPLAIN SELECT * FROM cluster_test WHERE x < 4;                                 QUERY PLAN----------------------------------------------------------------------------- Bitmap Heap Scan on cluster_test  (cost=4.44..16.30 rows=3 width=258)   Recheck Cond: (x < 4)   ->  Bitmap Index Scan on i_cluster_test  (cost=0.00..4.44 rows=3 width=0)         Index Cond: (x < 4)

EXPLAIN SELECT * FROM cluster_test WHERE x < 27000;                                    QUERY PLAN----------------------------------------------------------------------------------- Bitmap Heap Scan on cluster_test  (cost=809.99..8556.02 rows=27042 width=258)   Recheck Cond: (x < 27000)   ->  Bitmap Index Scan on i_cluster_test  (cost=0.00..803.23 rows=27042 width=0)         Index Cond: (x < 27000)

EXPLAIN SELECT * FROM cluster_test WHERE x < 28000;                             QUERY PLAN--------------------------------------------------------------------- Seq Scan on cluster_test  (cost=0.00..8658.00 rows=28058 width=258)   Filter: (x < 28000)

请注意，它在第三行之后就从索引扫描切换到位图堆扫描，因为统计信息指明了匹配的行随机存储在堆中。当使用一个索引排序与堆排序很接近的索引时，使用位图堆扫描相比索引扫描没有任何价值。

使用cluster，我们可以强制堆匹配索引排序，并再次导致索引用于更多行：

CLUSTER cluster_test USING i_cluster_test;
ANALYZE cluster_test;
EXPLAIN SELECT * FROM cluster_test WHERE x < 74000;                                         QUERY PLAN-------------------------------------------------------------------------------------- Index Scan using i_cluster_test on cluster_test  (cost=0.29..4836.03 rows=73642 width=258)   Index Cond: (x < 74000)

EXPLAIN SELECT * FROM cluster_test WHERE x < 75000;                             QUERY PLAN--------------------------------------------------------------------- Seq Scan on cluster_test  (cost=0.00..4954.00 rows=74696 width=258)   Filter: (x < 75000)

当然，explain只是评估检索数据的最有效方式——需要适当的性能测试来确定何时使用cluster命令。

具有时间序列的数据常常很难与cluster一起使用。最近的数据通常是最常访问的。如果表几乎没有更新和删除，新行通常会附加到文件的末尾，提供良好的相关性排序，可以被 Postgres 检测和利用。但是，如果有很多更新/删除，插入和更新的行会被放置在表中任何未使用的空间中，因此相关性会很低。事实上，如果您之前对表进行了cluster，并且您只访问最近的数据，您可能会得到一个不具代表性的高相关值和低效的计划，因为虽然大多数表行都被集群了，但是曾经最常访问的新行，并未基于索引排序。表分区可以被认为是一种粗略的cluster，它可以通过使用基于时间的分区来改善数据局部性来帮助减轻负载。无论如何，希望这篇博文已经为您提供了一些有关cluster何时有用的提示。