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.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/457582.html
標籤：其他