PostgreSQL 10 - 使用clustered tables提升速度

clustered table是什么意思呢？加入你想读整片数据，比如有一定的时间范围、一些块或者ID等等。
这些查询依赖于数据的量和在磁盘上的范围。所以，假如你运行的查询返回相同数量的记录，如果磁盘布局不同，返回时间也就不同。
比如下面的例子

postgres=# 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..322.86 rows=9396 width=9) (actual time=0.020..2.694 rows=9999 loops=1)
   Index Cond: (id < 10000)
   Buffers: shared hit=85
 Planning Time: 0.137 ms
 Execution Time: 3.463 ms
(5 行记录)

你可能还记得，数据是组织的，按顺序加载。数据增加了ID列，你希望数据在磁盘上也按顺序保存。如果数据被保存到一张空表，使用自增长的列，上面的希望还是有可能实现的。
上面的例子，EXPLAIN (analyze true, buffers true, and timing true)，分析不仅显示计划，也包括实际的查询发生了什么。通过这样的语句，可以看出计划是否合理。参数buffers true会告诉我们，查询了多少数据块。本例中使用了85个块。shared hit的意思是数据来自PostgreSQL的I/O 缓存（shared buffers）。为了检索这些数据，PostgreSQL花费了3毫秒多。

如果表中的数据比较随机，会发生什么？
我们建一张新表，数据相同，但是顺序随机：

postgres=# CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
SELECT 4000000
Time: 7976.155 ms (00:07.976)

为了公平地比较，我们建相同的索引：

postgres=# CREATE INDEX idx_random ON t_random (id);
CREATE INDEX
Time: 4248.689 ms (00:04.249)

为了正常运行，PostgreSQL需要优化器统计。这些统计数据会告诉PostgreSQL，有多少数据，值是如何分布的，以及数据是磁盘相关的（correlated on disk）。为了加快速度，我调用一次VACUUM。

postgres=# VACUUM ANALYZE t_random;
VACUUM
时间：458.423 ms

好了，让我们执行相同的查询：

postgres=# EXPLAIN (analyze true, buffers true, timing true)
postgres-# SELECT * FROM t_random WHERE id < 10000;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t_random  (cost=211.02..18797.24 rows=11173 width=9) (actual time=4.342..34.544 rows=9999 loops=1)
   Recheck Cond: (id < 10000)
   Heap Blocks: exact=8061
   Buffers: shared hit=833 read=7258
   ->  Bitmap Index Scan on idx_random  (cost=0.00..208.23 rows=11173 width=0) (actual time=2.164..2.164 rows=9999 loops=1)
         Index Cond: (id < 10000)
         Buffers: shared hit=3 read=27
 Planning Time: 0.275 ms
 Execution Time: 35.198 ms
(9 行记录)

注意：一共读取了7258+833=8091个块，执行时间超过了35毫秒。
而且，计划也改变了。PostgreSQL使用位图扫描，而不是普通的索引扫描。这样做，减少了查询需要访问的块。
查询计划器如何知道数据在磁盘上怎么组织的呢？系统视图pg_stats包含了关于列的内容的所有的统计。

postgres=# SELECT tablename, attname, correlation FROM pg_stats
postgres-# WHERE tablename IN ('t_test', 't_random') ORDER BY 1, 2;
 tablename | attname | correlation 
-----------+---------+-------------
 t_random  | id      |  0.00601573
 t_random  | name    |    0.506519
 t_test    | id      |           1
 t_test    | name    |           1
(4 行记录)

你已经看到了，PostgreSQL关心每个列。该视图的内容，由ANALYZE生成。

postgres=# \h ANALYZE
命令：       ANALYZE
描述：       收集数据库的统计信息
语法：
ANALYZE [ ( 选项 [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]

选项可以是下列内容之一:

    VERBOSE

and table_and_columns is:

    表名 [ ( 列名称 [, ...] ) ]

一般来说，ANALYZE使用autovacuum daemon，在后台自动执行。
回到我们的查询。你已经看到了，每个表都有两列，id和name。对于t_test.id，correlation的值是1，这意味着在某种程度上来说，后一个值依赖前一个值（对于t_test.id，是简单上升的）。t_test.name也是这样，前面的都是hans，后面的都是paul，相同的name存储在一起。
而t_random，情况相当不同：负相关意味着数据像洗过的牌一样杂乱。name的correlation大约是0.5,从物理顺序上看，不同的数据一直在切换。因为我们查询的数据分布在很多块中，导致了性能很差。

Clustering tables

PostgreSQL有一个命令叫CLUSTER，它允许以期望的顺序重写表。比如可以让索引和数据的顺序相同：

postgres=# \h CLUSTER
命令：       CLUSTER
描述：       按照索引进行表的聚集
语法：
CLUSTER [VERBOSE] 表名 [ USING 索引名称 ]
CLUSTER [VERBOSE]

CLUSTER命令已经存在多年。在生产系统使用之前，要考虑以下几点：

• CLUSTER命令运行的时候会锁表。此时不能插入或者修改数据
• 数据只能以一个索引的顺序组织
• 记住，这个例子是最糟糕的情况。实际上，clustered的表和nonclustered的表的性能差别，取决于工作量、检索的数据量、缓存命中率等等
• 随着对表的操作，不会再维护表的clustered状态。随着时间的推移，Correlation一般会恶化

我们可以这样运行CLUSTER命令：

postgres=# CLUSTER t_random USING idx_random;
CLUSTER
Time: 13309.700 ms (00:13.310)

只使用索引扫描

下面两个例子，看上去差不多，性能差别却很大：

postgres=# EXPLAIN SELECT * FROM t_test WHERE id = 34234;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Index Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=9)
   Index Cond: (id = 34234)
(2 行记录)

postgres=# EXPLAIN SELECT id FROM t_test WHERE id = 34234;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Only Scan using idx_id on t_test  (cost=0.43..8.45 rows=1 width=4)
   Index Cond: (id = 34234)
(2 行记录)

你看到了，第一个的计划是index scan，第二个是index only scan。id列有索引，所以第二个查询不用访问表，而只访问了索引。所以性能更好。