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