专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

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/85630528https://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.

 

posted on   MSSQL123  阅读(643)  评论(0编辑  收藏  举报

点击右上角即可分享
微信分享提示