2.3 使用聚簇表改善速度

考虑用户想要读取一整个范围的数据的情况，这个范围可能是一定的时间范围、一些块、ID等。这种查询的运行时间会根据数据量以及数据在磁盘上的物理分布而变化。因此，即便用户运行返回同样数量行的查询，两个系统可能不会在相同的时间跨度内提供回答，因为物理磁盘布局可能会不同。

比如：

EXPLAIN (analyze true, buffers true, timing true)
SELECT
    *
FROM
    t_test
WHERE
    id < 10000;
    
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_id on t_test  (cost=0.43..309.16 rows=8956 width=9) (actual time=0.028..5.816 rows=9999 loops=1)
   Index Cond: (id < 10000)
   Buffers: shared hit=85
 Planning time: 0.181 ms
 Execution time: 7.433 ms
(5 rows)

数据是以一种有组织的并且顺序的方式被转载到表中。数据被一个ID接着一个ID添加，因此可以预期这些数据将以连续的顺序放在磁盘上。如果数据使用某个自增列被装载到一个空表中，情况就是如此。

这个例子中利用了EXPLAIN (analyze true, buffers true, timing true)。analyze除了展示查询计划之外，还会执行该查询并且展示执行时的情况。对比较规划器的估计值和实际值来说，EXPLAIN analyze非常好。要判断规划器是正确的还是差得很远，这是一种最好的方法。buffers true参数将告诉我们该查询会用到多少个块，这个例子中，用了85个块。shared hit表示来自于PostgreSQL的IO缓冲（共享缓存）的数据，PostgreSQL一共花了7ms来检索这些数据。

如果表中的数据有点随机会怎么样？先创建一个顺序随机的表并建立索引：

CREATE TABLE t_random AS
SELECT
    *
FROM
    t_test
ORDER BY
    random();


CREATE INDEX
    idx_random
ON
    t_random (id);

要正确工作，PostgreSQL会需要优化器统计信息。统计信息会告诉PostgreSQL有多少数据、值是怎样分布以及数据在磁盘是否相关。为了更快地得到统计信息，我们做一次VACUUM调用。

postgres=# VACUUM ANALYZE t_random;
VACUUM

现在运行同样的查询：

EXPLAIN (analyze true, buffers true, timing true)
SELECT
    *
FROM
    t_random
WHERE
    id < 10000;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_random  (cost=176.31..17225.32 rows=9275 width=9) (actual time=5.220..22.531 rows=9999 loops=1)
   Recheck Cond: (id < 10000)
   Heap Blocks: exact=8037
   Buffers: shared hit=8067
   ->  Bitmap Index Scan on idx_random  (cost=0.00..173.99 rows=9275 width=0) (actual time=2.930..2.930 rows=9999 loops=1)
         Index Cond: (id < 10000)
         Buffers: shared hit=30
 Planning time: 0.144 ms
 Execution time: 24.129 ms
(9 rows)

首先，数据块达到了8067块，而且运行时间涨到了24ms。唯一幸运的是，数据还是来自于内存而非磁盘，不然不得不访问磁盘8067次，因为磁盘等待势必会让该查询速度慢下来。

规划器是怎么知道数据被如何存储在磁盘上？pg_stats是一个包含了所有有关列内容统计信息的系统视图。该视图的内容由ANALYZE创建（通常会有一个autovacuum守护进程在后台自动地执行ANALYZE），它对性能至关重要：

postgres=# \h ANALYZE

Command:     ANALYZE
Description: collect statistics about a database
Syntax:
ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

SELECT
    tablename, 
    attname, 
    correlation 
FROM 
    pg_stats 
WHERE 
    tablename IN
        (
            't_test', 
            't_random'
        ) 
ORDER BY tablename, attname;

 tablename | attname | correlation
-----------+---------+-------------
 t_random  | id      |   0.0152077
 t_random  | name    |    0.511164
 t_test    | id      |           1
 t_test    | name    |           1
(4 rows)

如你所见，两个表都有两列（id和name）。对于t_test.id，关联度是1，这表示下一个值有些依赖于前一个值。对于t_test.name，有一些项含有hans，还有一些含有paul，所有相同的名称因此被存在一起。

在t_random中，一个负的相关度表示数据是混乱的。name列关联度大约是0.5，实际上，它表示在该表中相同的名字通常不是挨在一起，它还意味着以物理顺序读取该表时，读到的名字总是在切换。

聚簇表

在PostgreSQL中，使用CLUSTER命令可以以一种我们想要的顺序重写一个表，可以指定一个索引并且按该索引的顺序来存储数据：

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

使用CLUSTER命令前，要考虑到：

• CLUSTER命令在运行时将锁住表，不能插入或者修改数据。
• 数据只能按照一个索引进行组织，这意味着如果存在一个大部分时间都会被使用的搜索条件，CLUSTER才有意义。
• 实际上，一个聚簇表和一个非聚簇表之间的性能差异将取决于负载、接收到的数据量、缓存命中率等很多因素。

下面是使用CLUSTER命令的例子：

-- 显示前3行，可以看到id是乱序的
SELECT
    *,
    row_number() over() AS rownum    -- 显示行号
FROM
    t_random
LIMIT 3;

   id    | name | rownum
---------+------+--------
 3233227 | paul |      1
  139598 | hans |      2
 1138744 | hans |      3
(3 rows)


-- 聚簇
CLUSTER t_random USING idx_random;


-- 调用VACUUM
VACUUM ANALYZE t_random;


-- 可以看到关联度变成了1
SELECT
    tablename,
    attname,
    correlation
FROM
    pg_stats
WHERE
    tablename IN
        (
            't_test',
            't_random'
        )
ORDER BY tablename, attname;
 tablename | attname | correlation
-----------+---------+-------------
 t_random  | id      |           1
 t_random  | name    |           1
 t_test    | id      |           1
 t_test    | name    |           1
(4 rows)


-- 前3行是有序的
SELECT
    *,
    row_number() over() AS rownum
FROM
    t_random
ORDER BY rownum ASC
LIMIT 3;

 id | name | rownum
----+------+--------
  1 | hans |      1
  2 | hans |      2
  3 | hans |      3
(3 rows)

做两次插入后，新插入的数据仍是无序的，需要重新CLUSTER：

INSERT INTO t_random (
    id,
    name
)
VALUES 
    (
        4000002,
        'hans'
    );


INSERT INTO t_random (
    id,
    name
)
VALUES 
    (
        4000001,
        'paul'
    );


-- 最后3行是无序的
SELECT
    *, 
    row_number() over() AS rownum  
FROM  
    t_random 
ORDER BY rownum DESC 
LIMIT 3;

   id    | name | rownum
---------+------+---------
 4000001 | paul | 4000002
 4000002 | hans | 4000001
 4000000 | paul | 4000000
(3 rows)


-- 重新CLUSTER后，变为有序
CLUSTER t_random USING idx_random;


SELECT
    *, 
    row_number() over() AS rownum  
FROM  
    t_random 
ORDER BY rownum DESC 
LIMIT 3;

   id    | name | rownum
---------+------+---------
 4000002 | hans | 4000002
 4000001 | paul | 4000001
 4000000 | paul | 4000000
(3 rows)