cluster：提升postgresql性能（译）

 cluster概念澄清

postgresql中没有聚集索引的概念，表都是以堆（heap）的方式存在，可以认为数据在物理存储上是无序的。
cluster：这里的cluster不是指多个机器的组成的集群，而是指表中数据行按照某种方式物理排序存储。是一种改变postgresql表的物理存储的一种方案。
适应场景：需要按照某种方式（比如时间）批量查询数据，但是数据基于这种方式（比如时间）离散度很高，就可以考虑是否需要按照相关字段的索引来cluster表，以达到提升查询效率的目的
cluster一张表的语法为 CLUSTER table_name USING index_name; 对某个表按照某个索引进行cluster之后，理论上说其离散度（select correlation from pg_stats）会接近于1。
cluster过程会额外占用相当于自身空间大小的空间。优化器记录着有关表的排序的统计，所以建议在新cluster的表上运行 ANALYZE 。否则，规划器可能会选择很差劲的查询规划

参考：https://blog.csdn.net/chuckchen1222/article/details/85630528，https://www.postgresql.org/docs/current/sql-cluster.html

原文地址：https://www.cybertec-postgresql.com/en/cluster-improving-postgresql-performance/
PostgreSQL:排序或不排序

为了演示磁盘布局的重要性，我创建了一个简单的测试集:  

test=# CREATE TABLE t_test AS SELECT *
        FROM generate_series(1, 10000000);
SELECT 10000000
test=# CREATE TABLE t_random AS SELECT *
        FROM t_test
        ORDER BY random();
SELECT 10000000

注意，这两个数据集是完全相同的。 我已经将1000万行加载到一个简单的表中。 然而，在第一种情况下，数据已经排序，然后插入。 Generate_series按升序返回数据，因为表是新数据，所以数据将按升序写入磁盘。  

在第二种情况下，我决定在插入之前打乱数据。 我们还是在讨论相同的数据集。 然而，顺序并不相同: 

test=# \d+
                    List of relations
 Schema |   Name   | Type  | Owner |  Size  | Description
--------+----------+-------+-------+--------+-------------
 public | t_random | table | hs    | 346 MB |
 public | t_test   | table | hs    | 346 MB |
(2 rows)

在这两种情况下，磁盘的大小是相同的。 在空间消耗方面没有变化，这也是一个重要的因素。  

在PostgreSQL中创建索引

在两张表上创建同样的索引

test=# \timing
Timing is on.
test=# CREATE INDEX idx_test ON t_test (generate_series);
CREATE INDEX
Time: 3699.416 ms (00:03.699)
test=# CREATE INDEX idx_random ON t_random (generate_series);
CREATE INDEX
Time: 5084.823 ms (00:05.085)

由于各种原因，在排序数据上创建索引甚至已经更快了。 但是，创建初始索引并不经常发生，因此您不应该过于担心。  

在下一步中，我们已经可以创建优化器统计数据，并确保所有提示位都被设置，以确保公平的性能比较:  

test=# VACUUM ANALYZE;
VACUUM

读取数据库块

现在，所有的测试数据集都已经就绪，我们可以运行一个简单的测试:  

test=# explain (analyze, buffers) SELECT *
    FROM    t_test
    WHERE   generate_series BETWEEN 1000 AND 50000;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_test on t_test  (cost=0.43..1362.62 rows=43909 width=4) (actual time=0.017..7.810 rows=49001 loops=1)
   Index Cond: ((generate_series >= 1000) AND (generate_series <= 50000))
   Heap Fetches: 0
   Buffers: shared hit=138
 Planning Time: 0.149 ms
 Execution Time: 11.785 ms
(6 rows)

还可以， 我们需要11.785毫秒来读取数据。 这里需要考虑的最重要的是，8k块的数量是138，这并不多。 “共享命中”意味着所有的数据都来自内存。  

在另外一张表上执行相同的测试

test=# explain (analyze, buffers) SELECT *
    FROM    t_random
    WHERE   generate_series BETWEEN 1000 AND 50000;
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_random on t_random  (cost=0.43..1665.17 rows=53637 width=4) (actual time=0.013..9.892 rows=49001 loops=1)
   Index Cond: ((generate_series >= 1000) AND (generate_series <= 50000))
   Heap Fetches: 0
   Buffers: shared hit=18799
 Planning Time: 0.102 ms
 Execution Time: 13.386 ms
(6 rows)

在本例中，查询花费的时间要长一些:13.4 ms。 然而，让我们在这里讨论最重要的数字:返回该结果所需的块数量。 18799块， 喔， 这大约是150多倍。  

有人可能会说，查询实际上并没有那么慢。 这是正确的。 然而，在我的例子中，所有的数据都来自内存。 让我们暂时假设数据必须从磁盘读取，因为出于某种原因，我们没有获得缓存命中。 情况将发生戏剧性的变化。 假设从磁盘读取一个块需要0.1 ms:  138 * 0.1 + 11.7 = 25.5 毫秒   vs 18799 * 0.1 + 13.4 = 1893.3毫秒  。

这是一个主要的区别。 这就是为什么块的数量确实会有影响——即使乍一看似乎并非如此。 缓存命中率越低，问题就越严重。  

在这个例子中还有一个方面需要考虑:请注意，如果您只想读取少量的行，那么磁盘布局不会有太大的区别。 但是，如果数据的子集包含数千行，那么在磁盘上排序的方式确实会对性能产生影响。 

 

Cluster：PostgreSQL的到来拯救了我们

CLUSTER命令在许多年前就被引入了，目的正是为了解决我刚才概述的问题。 它允许您根据索引组织数据。 语法如下:  

test=# \h CLUSTER
Command:     CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER [VERBOSE] table_name [ USING index_name ]
CLUSTER [VERBOSE]

URL: https://www.postgresql.org/docs/12/sql-cluster.html

使用CLUSTER命令很容易。 下面的代码片段将展示如何做到这一点:  

test=# CLUSTER t_random USING idx_random;
CLUSTER

为了查看发生了什么，我再次执行了相同的查询。 然而，有一些重要的事情需要注意:  

test=# explain (analyze, buffers)
    SELECT *    FROM t_random
    WHERE   generate_series BETWEEN 1000 AND 50000;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_random  (cost=1142.21..48491.32 rows=53637 width=4) (actual time=3.328..9.564 rows=49001 loops=1)
   Recheck Cond: ((generate_series >= 1000) AND (generate_series <= 50000)) Heap Blocks: exact=218 Buffers: shared hit=2 read=353 ->  Bitmap Index Scan on idx_random  (cost=0.00..1128.80 rows=53637 width=0) (actual time=3.284..3.284 rows=49001 loops=1)
         Index Cond: ((generate_series >= 1000) AND (generate_series <= 50000))
         Buffers: shared hit=2 read=135
 Planning Time: 1.024 ms
 Execution Time: 13.077 ms
(9 rows)

PostgreSQL修改了执行计划。 这是因为错误的统计数据。 因此，重要的是运行ANALYZE，以确保优化器有最新的信息:  

test=# ANALYZE;
ANALYZE

一旦新的优化器统计数据到位，执行计划将再次如预期那样:  

test=# explain (analyze, buffers) SELECT *
    FROM    t_random
    WHERE   generate_series BETWEEN 1000 AND 50000;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_random on t_random  (cost=0.43..1807.12 rows=50884 width=4) (actual time=0.012..11.737 rows=49001 loops=1)
   Index Cond: ((generate_series >= 1000) AND (generate_series <= 50000))
   Heap Fetches: 49001
   Buffers: shared hit=355
 Planning Time: 0.220 ms
 Execution Time: 15.267 ms
(6 rows)

 

维持（数据行的顺序）Order

如果您决定对一个表进行集群，这并不意味着永远保持磁盘上的顺序。 如果频繁地运行UPDATES等，表可能会再次逐渐松散。 因此，如果您的数据相当静态，那么CLUSTER尤其有用。 在导入数据以确保物理顺序时，还可以对数据进行排序。  

Finally …

If you want to learn more about database performance and storage consider checking out my post about shrinking the storage footprint of PostgreSQL.