PostgreSQL任意列組合條件行數估算實踐-取樣估算

標籤

PostgreSQL , 優化器 , 取樣 , 多列統計資訊 , 行評估 , 大表任意維度過濾行數評估

---

背景

在任意維度查詢（ADHoc）的場景中，有諸多技術手段來保證查詢的效能，比如rum索引，GIN倒排索引，BLOOM FILTER索引。又比如多個索引的bitmap 合併scan。

例子：

《PostgreSQL ADHoc(任意欄位組合)查詢 與 字典化 (rum索引加速) – 實踐與方案1》

《用PostgreSQL 做實時高效 搜尋引擎 – 全文檢索、模糊查詢、正則查詢、相似查詢、ADHOC查詢》

《HTAP資料庫 PostgreSQL 場景與效能測試之 20 – (OLAP) 使用者畫像圈人場景 – 多個欄位任意組合條件篩選與透視》

《PostgreSQL 多欄位任意組合搜尋的效能》

《時間、空間、物件多維屬性 海量資料任意多維 高效檢索 – 阿里雲RDS PostgreSQL最佳實踐》

《列存優化(shard,大小塊,歸整,塊級索引,bitmap scan) – (大量資料實時讀寫)任意列搜尋》

《多欄位，任意組合條件查詢(無需建模) – 毫秒級實時圈人 最佳實踐》

《寶劍贈英雄 – 任意組合欄位等效查詢, 探探PostgreSQL多列展開式B樹 (GIN)》

《PostgreSQL 如何高效解決 按任意欄位分詞檢索的問題 – case 1》

《PostgreSQL 9.6 黑科技 bloom 演算法索引，一個索引支撐任意列組合查詢》

但是在有些時候，我們可能想先知道條件輸入後，大概有多少複合條件的行（也就是行數評估），PostgreSQL的優化器提供了行評估的功能：

例如我們可以explain的結果來實現高效率的分頁估算。

《PostgreSQL pg_stats used to estimate top N freps values and explain rows》

《PostgreSQL 優化器行評估演算法》

《妙用explain Plan Rows快速估算行 – 分頁數估算》

那麼在任意欄位條件搜尋是否也可以用同樣的方法呢？多列多條件的評估實際上比單列評估的精度會有所下降，可能導致精度不準確。因此PostgreSQL 10又提供了一種黑科技，允許你對多列構建多列的統計資訊。從而提升多欄位過濾條件的行評估精準度（會引入一定的統計資訊的開銷和儲存擴充套件）。

《PostgreSQL 10 黑科技 – 自定義統計資訊》

那麼在PostgreSQL 10以前，或者是我們不想使用多列統計資訊，還有什麼好方法來提高多列條件的行評估精準度呢？答案是有的，PG提供了取樣的SQL介面，我們可以通過取樣資料的計算來評估。

《PostgreSQL Oracle 相容性 之 – 資料取樣與脫敏》

例如在以下文件中，就是使用取樣資料來估算TOP N的。

《秒級任意維度分析1TB級大表 – 通過取樣估值滿足高效TOP N等統計分析需求》

當然了，PG還提供了一些估算外掛，但是僅可用於建模好的資料環境，有興趣可以閱讀如下：

《Greenplum 最佳實踐 – 估值外掛hll的使用(以及hll分式聚合函式優化)》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 3》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 2》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 1》

《PostgreSQL count-min sketch top-n 概率計算外掛 cms_topn (結合視窗實現同比、環比、滑窗分析等) – 流計算核心功能之一》

例子

1、建表，6個欄位，寫入5000萬隨機測試資料。

postgres=# create table tbl1(c1 int, c2 int, c3 int, c4 int, c5 int, c6 timestamp);  
CREATE TABLE  
postgres=# insert into tbl1 select random()*100, random()*10, random()*50, random()*20, random()*5000, clock_timestamp()  from generate_series(1,50000000);  
INSERT 0 50000000  
postgres=# analyze tbl1;  
ANALYZE  

2、觀察多列、單列條件的行評估是否準確

兩列AND條件，非常準確（ 評估53562，實際50354 ）

postgres=# select count(*) from tbl1 where c1=1 and c2=1;  
 count   
-------  
 50354  
(1 row)  
  
postgres=# explain select * from tbl1 where c1=1 and c2=1;  
                                 QUERY PLAN                                    
-----------------------------------------------------------------------------  
 Gather  (cost=1000.00..686505.00 rows=53562 width=28)  
   Workers Planned: 2  
   ->  Parallel Seq Scan on tbl1  (cost=0.00..680148.80 rows=22318 width=28)  
         Filter: ((c1 = 1) AND (c2 = 1))  
(4 rows)  

兩列OR條件，非常準確（ 評估5558119，實際5451751 ）

postgres=# explain select * from tbl1 where c1=1 or c2=1;  
                           QUERY PLAN                              
-----------------------------------------------------------------  
 Seq Scan on tbl1  (cost=0.00..1117649.92 rows=5558119 width=28)  
   Filter: ((c1 = 1) OR (c2 = 1))  
(2 rows)  
  
postgres=# select count(*) from tbl1 where c1=1 or c2=1;  
  count    
---------  
 5451751  
(1 row)  

三列AND條件，非常準確（ 評估10，實際12 ）

postgres=# explain analyze select * from tbl1 where c1=1 and c2=1 and c5=99;  
                                                       QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=1000.00..733233.27 rows=10 width=28) (actual time=41.413..1422.724 rows=12 loops=1)  
   Workers Planned: 2  
   Workers Launched: 2  
   ->  Parallel Seq Scan on tbl1  (cost=0.00..732232.27 rows=4 width=28) (actual time=273.134..1420.060 rows=4 loops=3)  
         Filter: ((c1 = 1) AND (c2 = 1) AND (c5 = 99))  
         Rows Removed by Filter: 16666663  
 Planning time: 0.126 ms  
 Execution time: 1424.440 ms  
(8 rows)  

但是，所有情況都能準確嗎？

當資料出現傾斜時，可能就沒那麼準確了。

3、再次寫入5000萬資料，此次c2,c3,c4,c5使用固定值，使其傾斜。

postgres=# insert into tbl1 select random()*100, 10, 50, 20, 5000, clock_timestamp()  from generate_series(1,50000000);  
INSERT 0 50000000  
  
postgres=# analyze tbl1;  
ANALYZE  

4、非傾斜值的評估，依舊準確

postgres=# explain analyze select * from tbl1 where c1=1 and c2=1 and c5=99;  
                                                       QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=1000.00..1465464.64 rows=21 width=28) (actual time=362.359..3084.858 rows=12 loops=1)  
   Workers Planned: 2  
   Workers Launched: 2  
   ->  Parallel Seq Scan on tbl1  (cost=0.00..1464462.54 rows=9 width=28) (actual time=154.661..3082.340 rows=4 loops=3)  
         Filter: ((c1 = 1) AND (c2 = 1) AND (c5 = 99))  
         Rows Removed by Filter: 33333329  
 Planning time: 0.099 ms  
 Execution time: 3086.687 ms  
(8 rows)  

5、對於傾斜值，評估偏差很大（ 評估12702，實際0 ）

postgres=# explain analyze select * from tbl1 where c1=1 and c2=1 and c5=5000 and c4=20;  
                                                         QUERY PLAN                                                            
-----------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=1000.00..1570899.53 rows=12702 width=28) (actual time=2834.241..2834.241 rows=0 loops=1)  
   Workers Planned: 2  
   Workers Launched: 2  
   ->  Parallel Seq Scan on tbl1  (cost=0.00..1568629.33 rows=5292 width=28) (actual time=2831.777..2831.777 rows=0 loops=3)  
         Filter: ((c1 = 1) AND (c2 = 1) AND (c5 = 5000) AND (c4 = 20))  
         Rows Removed by Filter: 33333333  
 Planning time: 0.144 ms  
 Execution time: 2836.049 ms  
(8 rows)  

6、調整目標列的統計資訊柱狀圖，擴大到1000。

postgres=# alter table tbl1 alter COLUMN c5 set statistics 1000;  
ALTER TABLE  
postgres=# alter table tbl1 alter COLUMN c4 set statistics 1000;  
ALTER TABLE  
postgres=# alter table tbl1 alter COLUMN c1 set statistics 1000;  
ALTER TABLE  
postgres=# alter table tbl1 alter COLUMN c2 set statistics 1000;  
ALTER TABLE  
postgres=# analyze tbl1;  
ANALYZE  

7、行評估依舊不準確（ 評估13034，實際0 ）

postgres=# explain analyze select * from tbl1 where c1=1 and c2=1 and c5=5000 and c4=20;  
                                                         QUERY PLAN                                                            
-----------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=1000.00..1570932.73 rows=13034 width=28) (actual time=2852.150..2852.150 rows=0 loops=1)  
   Workers Planned: 2  
   Workers Launched: 2  
   ->  Parallel Seq Scan on tbl1  (cost=0.00..1568629.33 rows=5431 width=28) (actual time=2849.510..2849.510 rows=0 loops=3)  
         Filter: ((c1 = 1) AND (c2 = 1) AND (c5 = 5000) AND (c4 = 20))  
         Rows Removed by Filter: 33333333  
 Planning time: 0.181 ms  
 Execution time: 2853.844 ms  
(8 rows)  

那麼這個問題無解了嗎？實際上PG提供了取樣查詢的介面，我們通過取樣查詢能夠實現高效、準確的評估。

《秒級任意維度分析1TB級大表 – 通過取樣估值滿足高效TOP N等統計分析需求》

內建了4種取樣介面，我們這裡用到的是基於BLOCK級別的百分比取樣。

取樣百分之一，COUNT值乘以100就是評估行數。如果取樣百分之5的話，COUNT值乘以20就是評估行數。

實際上取樣百分之一，評估已經非常準確了。

postgres=# select count(*) from ( SELECT * FROM tbl1 TABLESAMPLE SYSTEM(1)) t where c1=1 and c2=1 and c5=5000 and c4=20;  
 count   
-------  
     0  
(1 row)  
Time: 104.212 ms  
  
postgres=# select count(*) from ( SELECT * FROM tbl1 TABLESAMPLE SYSTEM(1)) t where c1=1 and c2=1 and c5=5000 ;  
 count   
-------  
     0  
(1 row)  
Time: 107.088 ms  
  
postgres=# select count(*) from tbl1 where c1=1 and c2=1 and c5=5000;  
 count   
-------  
     2  
(1 row)  
Time: 2838.372 ms (00:02.838)  

postgres=# select count(*) from ( SELECT * FROM tbl1 TABLESAMPLE SYSTEM(1)) t where c1=1 and c2=1 ;  
 count   
-------  
   552  
(1 row)  
  
Time: 100.066 ms  
  
postgres=# select count(*) from tbl1 where c1=1 and c2=1 ;  
 count   
-------  
 50354  
(1 row)  
Time: 2546.602 ms (00:02.547)  

1億條記錄，取樣百分之一，大概耗時100毫秒。相比直接全表掃描還是節約了不少成本的。

小結

通過取樣查詢，我們實現了任意維度條件過濾的行評估，相比傳統的行評估演算法，精確度提高了不少，在1億資料量的情況下，取樣百分之一，耗時約100毫秒。

參考

《PostgreSQL Oracle 相容性 之 – 資料取樣與脫敏》

《PostgreSQL pg_stats used to estimate top N freps values and explain rows》

《PostgreSQL 優化器行評估演算法》

《妙用explain Plan Rows快速估算行 – 分頁數估算》

《秒級任意維度分析1TB級大表 – 通過取樣估值滿足高效TOP N等統計分析需求》

《PostgreSQL 10 黑科技 – 自定義統計資訊》

修改單列統計資訊柱狀圖精度，預設是100全域性default_statistics_target引數控制。

postgres=# alter table tbl1 alter COLUMN c5 set statistics 1000;  
ALTER TABLE  
Time: 0.786 ms  
  
postgres=# analyze tbl1;  
ANALYZE  
Time: 2157.194 ms (00:02.157)  

《Greenplum 最佳實踐 – 估值外掛hll的使用(以及hll分式聚合函式優化)》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 3》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 2》

《PostgreSQL hll (HyperLogLog) extension for “State of The Art Cardinality Estimation Algorithm” – 1》

《PostgreSQL count-min sketch top-n 概率計算外掛 cms_topn (結合視窗實現同比、環比、滑窗分析等) – 流計算核心功能之一》