利用Postgresql怎么對排序的性能進行優化

利用Postgresql怎么對排序的性能進行優化？針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

1 構造測試數據

create table tbl(id int, num int, arr int[]); 
create index idx_tbl_arr on tbl using gin (arr); 
create or replace function gen_rand_arr() returns int[] as $$ 
 select array(select (1000*random())::int from generate_series(1,64)); 
$$ language sql strict;

insert into tbl select generate_series(1,3000000),(10000*random())::int, gen_rand_arr();

insert into tbl select generate_series(1,500), (10000*random())::int, array[350,514,213,219,528,753,270,321,413,424,524,435,546,765,234,345,131,345,351];

2 查詢走GIN索引

測試場景的限制GIN索引查詢速度是很快的， 在實際生產中，可能出現使用gin索引后，查詢速度依然很高的情況，特點就是執行計劃中Bitmap Heap Scan占用了大量時間，Bitmap Index Scan大部分標記的塊都被過濾掉了。

這種情況是很常見的，一般的btree索引可以cluster來重組數據，但是gin索引是不支持cluster的，一般的gin索引列都是數組類型。所以當出現數據非常分散的情況時，bitmap index scan會標記大量的塊，后面recheck的成本非常高，導致gin索引查詢慢。

我們接著來看這個例子

explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 20;
                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=2152.02..2152.03 rows=1 width=40) (actual time=57.665..57.668 rows=20 loops=1)
 -> Sort (cost=2152.02..2152.03 rows=1 width=40) (actual time=57.664..57.665 rows=20 loops=1)
   Sort Key: num
   Sort Method: top-N heapsort Memory: 27kB
   -> Bitmap Heap Scan on tbl (cost=2148.00..2152.01 rows=1 width=40) (actual time=57.308..57.581 rows=505 loops=1)
    Recheck Cond: (arr @> '{350,514,213,219,528,753,270}'::integer[])
    Heap Blocks: exact=493
    -> Bitmap Index Scan on idx_tbl_arr (cost=0.00..2148.00 rows=1 width=0) (actual time=57.248..57.248 rows=505 loops=1)
      Index Cond: (arr @> '{350,514,213,219,528,753,270}'::integer[])
 Planning time: 0.050 ms
 Execution time: 57.710 ms

可以看到當前執行計劃是依賴gin索引掃描的，但gin索引出現性能問題時我們如何來優化呢？

3 排序limit組合場景優化

SQL中的排序與limit組合是一個很典型的索引優化創景。我們知道btree索引在內存中是有序的，通過遍歷btree索引可以直接拿到sort后的結果，這里組合使用limit后，只需要遍歷btree的一部分節點然后按照其他條件recheck就ok了。

我們來看一下優化方法：

create index idx_tbl_num on tbl(num);
analyze tbl;
set enable_seqscan = off;
set enable_bitmapscan = off;
postgres=# explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 10;
                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.43..571469.93 rows=1 width=287) (actual time=6.300..173.949 rows=10 loops=1)
 -> Index Scan Backward using idx_tbl_num on tbl (cost=0.43..571469.93 rows=1 width=287) (actual time=6.299..173.943 rows=10 loops=1)
   Filter: (arr @> '{350,514,213,219,528,753,270}'::integer[])
   Rows Removed by Filter: 38399
 Planning time: 0.125 ms
 Execution time: 173.972 ms
(6 rows)

Time: 174.615 ms
postgres=# cluster tbl using idx_tbl_num;
CLUSTER
Time: 124340.276 ms
postgres=# explain analyze select * from tbl where arr @> array[350,514,213,219,528,753,270] order by num desc limit 10;
                QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.43..563539.77 rows=1 width=287) (actual time=1.145..34.602 rows=10 loops=1)
 -> Index Scan Backward using idx_tbl_num on tbl (cost=0.43..563539.77 rows=1 width=287) (actual time=1.144..34.601 rows=10 loops=1)
   Filter: (arr @> '{350,514,213,219,528,753,270}'::integer[])
   Rows Removed by Filter: 38399
 Planning time: 0.206 ms
 Execution time: 34.627 ms
(6 rows)

本例的測試場景構造可能沒有最大程度的體現問題，不過可以看出cluster后走btree索引可以很穩定的達到34ms左右。

在gin性能存在問題的時候，這類limit + order by的SQL語句不妨常識強制（pg_hint_plan）走一下btree索引，可能有意想不到的效果。

4 高并發場景下的gin索引查詢性能下降

GIN索引為PostgreSQL數據庫多值類型的倒排索引，一條記錄可能涉及到多個GIN索引中的KEY，所以如果寫入時實時合并索引，會導致IO急劇增加，寫入RT必然增加。為了提高寫入吞吐，PG允許用戶開啟GIN索引的延遲合并技術，開啟后，數據會先寫入pending list，并不是直接寫入索引頁，當pending list達到一定大小，或者autovacuum 對應表時，會觸發pending list合并到索引的動作。

查詢時，如果有未合并到索引中的PENDING LIST，那么會查詢pending list，同時查詢索引也的信息。

如果寫入量很多，pending list非常巨大，合并(autovacuum worker做的)速度跟不上時，會導致通過GIN索引查詢時查詢性能下降。

create extension pageinspect ; 
SELECT * FROM gin_metapage_info(get_raw_page('idx_tbl_arr', 0)); 

-- 如果很多條記錄在pending list中，查詢性能會下降明顯。
-- vacuum table，強制合并pending list
vacuum tbl;

第4部分引用https://github.com/digoal/blog/blob/master/201809/20180919_02.md

補充：PostgreSQL -- 性能優化的小方法

一、回收磁盤空間

在PostgreSQL中，使用delete和update語句刪除或更新的數據行并沒有被實際刪除，而只是在舊版本數據行的物理地址上將該行的狀態置為已刪除或已過期。因此當數據表中的數據變化極為頻繁時，那么在一段時間之后該表所占用的空間將會變得很大，然而數據量卻可能變化不大。要解決該問題，需要定期對數據變化頻繁的數據表執行VACUUM操作。現在新版PostgreSQL是自動執行VACUUM的

使用VACUUM和VACUUM FULL命令回收磁盤空間

postgres=# vacuum arr_test;

postgres=# vacuum full arr_test;

創建測試數據：

postgres=# create table arr (id serial, value int, age int) #創建測試表
postgres=# insert into arr (value, age) select generate_series(1, 1000000) as value, (random()*(10^2))::integer; #插入100W測試數據
postgres=# select pg_relation_size('arr'); #查看表大小
 pg_relation_size 
------------------
   44285952
(1 row)
postgres=# delete from arr where id<300000; #刪除299999條數據
DELETE 299999
postgres=# select pg_relation_size('arr'); #再次查看表大小，沒有變化
pg_relation_size 
------------------
   44285952
(1 row)
postgres=# vacuum full arr; #vacuum表，再次查看表大小，明顯變小了
VACUUM
postgres=# select pg_relation_size('arr');
 pg_relation_size 
------------------
   30998528
(1 row)
postgres=# update arr set age=10000 where id>=300000 and id<600000; #更新30W條數據
UPDATE 300000
postgres=# select pg_relation_size('arr'); #查看表大小，明顯再次增大
 pg_relation_size 
------------------
   44285952
(1 row)

二、重建索引

在PostgreSQL中，為數據更新頻繁的數據表定期重建索引(REINDEX INDEX)是非常有必要的。

對于B-Tree索引，只有那些已經完全清空的索引頁才會得到重復使用，對于那些僅部分空間可用的索引頁將不會得到重用，如果一個頁面中大多數索引鍵值都被刪除，只留下很少的一部分，那么該頁將不會被釋放并重用。

在這種極端的情況下，由于每個索引頁面的利用率極低，一旦數據量顯著增加，將會導致索引文件變得極為龐大，不僅降低了查詢效率，而且還存在整個磁盤空間被完全填滿的危險。

對于重建后的索引還存在另外一個性能上的優勢，因為在新建立的索引上，邏輯上相互連接的頁面在物理上往往也是連在一起的，這樣可以提高磁盤頁面被連續讀取的幾率，從而提高整個操作的IO效率

postgres=# REINDEX INDEX testtable_idx;

三、重新收集統計信息

PostgreSQL查詢規劃器在選擇最優路徑時，需要參照相關數據表的統計信息用以為查詢生成最合理的規劃。這些統計是通過ANALYZE命令獲得的，你可以直接調用該命令，或者把它當做VACUUM命令里的一個可選步驟來調用，如VACUUM ANAYLYZE table_name，該命令將會先執行VACUUM再執行ANALYZE。與回收空間(VACUUM)一樣，對數據更新頻繁的表保持一定頻度的ANALYZE，從而使該表的統計信息始終處于相對較新的狀態，這樣對于基于該表的查詢優化將是極為有利的。然而對于更新并不頻繁的數據表，則不需要執行該操作。

我們可以為特定的表，甚至是表中特定的字段運行ANALYZE命令，這樣我們就可以根據實際情況，只對更新比較頻繁的部分信息執行ANALYZE操作，這樣不僅可以節省統計信息所占用的空間，也可以提高本次ANALYZE操作的執行效率。

這里需要額外說明的是，ANALYZE是一項相當快的操作，即使是在數據量較大的表上也是如此，因為它使用了統計學上的隨機采樣的方法進行行采樣，而不是把每一行數據都讀取進來并進行分析。因此，可以考慮定期對整個數據庫執行該命令。

事實上，我們甚至可以通過下面的命令來調整指定字段的抽樣率

如：

ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200

注意：該值的取值范圍是0--1000，其中值越低采樣比例就越低，分析結果的準確性也就越低，但是ANALYZE命令執行的速度卻更快。如果將該值設置為-1，那么該字段的采樣比率將恢復到系統當前默認的采樣值，我們可以通過下面的命令獲取當前系統的缺省采樣值。

postgres=# show default_statistics_target;
  default_statistics_target
 ---------------------------
  100
 (1 row)

從上面的結果可以看出，該數據庫的缺省采樣值為100(10%)。

postgresql 性能優化

一、排序:

1. 盡量避免

2. 排序的數據量盡量少，并保證在內存里完成排序。

（至于具體什么數據量能在內存中完成排序，不同數據庫有不同的配置：

oracle是sort_area_size；

postgresql是work_mem (integer)，單位是KB，默認值是4MB。

mysql是sort_buffer_size 注意：該參數對應的分配內存是每連接獨占！

）

二、索引：

1. 過濾的數據量比較少，一般來說<20%,應該走索引。20%-40% 可能走索引也可能不走索引。> 40% ，基本不走索引(會全表掃描)

2. 保證值的數據類型和字段數據類型要一致。

3. 對索引的字段進行計算時，必須在運算符右側進行計算。也就是 to_char(oc.create_date, ‘yyyyMMdd')是沒用的

4. 表字段之間關聯，盡量給相關字段上添加索引。

5. 復合索引，遵從最左前綴的原則,即最左優先。（單獨右側字段查詢沒有索引的）

三、連接查詢方式：

1、hash join

放內存里進行關聯。

適用于結果集比較大的情況。

比如都是200000數據

2、nest loop

從結果1 逐行取出，然后與結果集2進行匹配。

適用于兩個結果集，其中一個數據量遠大于另外一個時。

結果集一：1000

結果集二：1000000

四、多表聯查時：

在多表聯查時，需要考慮連接順序問題。

1、當postgresql中進行查詢時，如果多表是通過逗號，而不是join連接，那么連接順序是多表的笛卡爾積中取最優的。如果有太多輸入的表， PostgreSQL規劃器將從窮舉搜索切換為基因概率搜索，以減少可能性數目(樣本空間)。基因搜索花的時間少， 但是并不一定能找到最好的規劃。

2、對于JOIN，LEFT JOIN / RIGHT JOIN 會一定程度上指定連接順序，但是還是會在某種程度上重新排列：FULL JOIN 完全強制連接順序。如果要強制規劃器遵循準確的JOIN連接順序，我們可以把運行時參數join_collapse_limit設置為 1

五、PostgreSQL提供了一些性能調優的功能：

優化思路：

0、為每個表執行 ANALYZE

。然后分析 EXPLAIN (ANALYZE，BUFFERS) sql。

1、對于多表查詢，查看每張表數據，然后改進連接順序。

2、先查找那部分是重點語句，比如上面SQL，外面的嵌套層對于優化來說沒有意義，可以去掉。

3、查看語句中，where等條件子句，每個字段能過濾的效率。找出可優化處。

比如oc.order_id = oo.order_id是關聯條件，需要加索引

oc.op_type = 3 能過濾出1/20的數據，

oo.event_type IN (…) 能過濾出1/10的數據，

這兩個是優化的重點，也就是實現確保op_type與event_type已經加了索引，其次確保索引用到了。

優化方案：

a) 整體優化：

1、使用EXPLAIN

EXPLAIN命令可以查看執行計劃，這個方法是我們最主要的調試工具。

2、及時更新執行計劃中使用的統計信息

由于統計信息不是每次操作數據庫都進行更新的，一般是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL執行的時候會更新統計信息，

因此執行計劃所用的統計信息很有可能比較舊。 這樣執行計劃的分析結果可能誤差會變大。

以下是表tenk1的相關的一部分統計信息。

SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';
relname | relkind | reltuples | relpages
----------------------+---------+-----------+----------
tenk1 | r | 10000 | 358
tenk1_hundred | i | 10000 | 30
tenk1_thous_tenthous | i | 10000 | 30
tenk1_unique1 | i | 10000 | 30
tenk1_unique2 | i | 10000 | 30
(5 rows)

其中 relkind是類型，r是自身表，i是索引index；reltuples是項目數；relpages是所占硬盤的塊數。

估計成本通過 （磁盤頁面讀取【relpages】*seq_page_cost）+（行掃描【reltuples】*cpu_tuple_cost）計算。

默認情況下， seq_page_cost是1.0，cpu_tuple_cost是0.01。

3、使用臨時表（with）

對于數據量大，且無法有效優化時，可以使用臨時表來過濾數據，降低數據數量級。

4、對于會影響結果的分析，可以使用 begin;…rollback;來回滾。

b) 查詢優化：

1、明確用join來關聯表，確保連接順序

一般寫法：SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

如果明確用join的話，執行時候執行計劃相對容易控制一些。

例子：

SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;

SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

c) 插入更新優化

1、關閉自動提交（autocommit=false）

如果有多條數據庫插入或更新等，最好關閉自動提交，這樣能提高效率

2、多次插入數據用copy命令更高效

我們有的處理中要對同一張表執行很多次insert操作。這個時候我們用copy命令更有效率。因為insert一次，其相關的index都要做一次，比較花費時間。

3、臨時刪除index【具體可以查看Navicat表數據生成sql的語句，就是先刪再建的】

有時候我們在備份和重新導入數據的時候，如果數據量很大的話，要好幾個小時才能完成。這個時候可以先把index刪除掉。導入后再建index。

4、外鍵關聯的刪除

如果表的有外鍵的話，每次操作都沒去check外鍵整合性。因此比較慢。數據導入后再建立外鍵也是一種選擇。

d) 修改參數：

介紹幾個重要的

1、增加maintenance_work_mem參數大小

增加這個參數可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的執行效率。

2、增加checkpoint_segments參數的大小

增加這個參數可以提升大量數據導入時候的速度。

3、設置archive_mode無效

這個參數設置為無效的時候，能夠提升以下的操作的速度

?CREATE TABLE AS SELECT

?CREATE INDEX

?ALTER TABLE SET TABLESPACE

?CLUSTER等。

4、autovacuum相關參數

autovacuum：默認為on，表示是否開起autovacuum。默認開起。特別的，當需要凍結xid時，盡管此值為off，PG也會進行vacuum。

autovacuum_naptime：下一次vacuum的時間，默認1min。 這個naptime會被vacuum launcher分配到每個DB上。autovacuum_naptime/num of db。

log_autovacuum_min_duration：記錄autovacuum動作到日志文件，當vacuum動作超過此值時。 “-1”表示不記錄。“0”表示每次都記錄。

autovacuum_max_workers：最大同時運行的worker數量，不包含launcher本身。

autovacuum_work_mem ：每個worker可使用的最大內存數。

autovacuum_vacuum_threshold ：默認50。與autovacuum_vacuum_scale_factor配合使用， autovacuum_vacuum_scale_factor默認值為20%。當update,delete的tuples數量超過autovacuum_vacuum_scale_factor *table_size+autovacuum_vacuum_threshold時，進行vacuum。如果要使vacuum工作勤奮點，則將此值改小。

autovacuum_analyze_threshold ：默認50。與autovacuum_analyze_scale_factor配合使用。

autovacuum_analyze_scale_factor ：默認10%。當update,insert,delete的tuples數量超過autovacuum_analyze_scale_factor *table_size+autovacuum_analyze_threshold時，進行analyze。

autovacuum_freeze_max_age：200 million。離下一次進行xid凍結的最大事務數。

autovacuum_multixact_freeze_max_age：400 million。離下一次進行xid凍結的最大事務數。

autovacuum_vacuum_cost_delay ：如果為-1，取vacuum_cost_delay值。

autovacuum_vacuum_cost_limit ：如果為-1，到vacuum_cost_limit的值，這個值是所有worker的累加值。

關于利用Postgresql怎么對排序的性能進行優化問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。