在设计数据库应用程序时,通常忽略磁盘中数据的布局。然而,PostgreSQL存储数据的方式会对性能产生重大影响。因此,有必要看看可以采取哪些措施来提高速度和吞吐量。在这篇文章中,您将学习最重要的技巧之一。
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
请注意,两个数据集绝对相同。我已经将一千万行加载到一个简单的表中。但是,在第一种情况下,数据已排序,然后插入。generate_series以升序返回数据,并且由于表是新数据,因此将按该顺序将数据写入磁盘。在第二种情况下,我决定在插入之前先对数据洗混。它们是相同的数据集。但是,它们的顺序不同:
在这两种情况下,磁盘上的大小都是相同的。空间消耗方面没有变化,这也是一个重要的因素。
在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
读取数据库块
现在所有测试数据集都已经到位,我们可以运行一个简单的测试:让我们首先从排序后的数据集中获取49000行:
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,这并不多。“shared hit”表示所有数据都来自内存。
让我在另一个表运行相同的测试:
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毫秒。但是,让我们在这里讨论最重要的数字:返回此结果所需的块数。18799块。哇。大约是原来的150倍。
有人可能会说,查询的速度实际上并没有那么慢。这是真的。但是,在我的示例中,所有数据都来自内存。让我们暂时假设必须从磁盘读取数据,因为某种原因,我们没有缓存命中。情况将发生巨大变化。让我们假设从磁盘读取一个块需要0.1毫秒:
138 * 0.1 + 11.7 = 25.5 ms
vs.
18799 * 0.1 + 13.4 = 1893.3 ms
那是一个主要的区别。这就是为什么块的数量确实有所不同的原因-即使乍一看似乎并非如此。高速缓存命中率越低,问题将变得越大。
在此示例中,还需要考虑其他方面:请注意,如果要读取几行,则仅磁盘上的布局不会有太大的不同。但是,如果数据子集包含数千行,则在磁盘上排序的方式确实会影响性能。
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)
维持排序
如果您决定对一个表进行cluster,那并不意味着磁盘上的顺序将永远保持下去。如果您经常运行UPDATES等,则该表可能会逐渐失去顺序。因此,如果您的数据比较静态,则CLUSTER特别有用。在导入数据时对数据进行排序以确保物理顺序是有意义的。
最后……
如果您想了解有关数据库性能和存储的更多信息,请查看我有关减少PostgreSQL存储空间的文章。
本文翻译自:https://www.cybertec-postgresql.com/en/cluster-improving-postgresql-performance/