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列有索引,所以第二个查询不用访问表,而只访问了索引。所以性能更好。