PostgreSQL 優化器知識之 - stable 函數調用次數

标簽

PostgreSQL , 三态 , stable , 調用次數

https://github.com/digoal/blog/blob/master/201806/20180629_02.md#%E8%83%8C%E6%99%AF 背景

immutable 函數，輸入參數靜态時，傳回結果不變，是以它在plan前先計算，得到一個常量後，放到QUERY裡面再生成執行計劃。

stable 函數，輸入參數靜态時，如果在一個事務中多次調用它，傳回結果不變。

顯然一個immutable 函數在同一個SQL中，永遠隻調用一次，（在bind , execute的模式下則隻有prepare的時候被調用一次）

但是一個stable函數，雖然輸入參數靜态時，如果在一個事務中多次調用它，傳回結果不變。但是它可能被多次調用。

例如在同一個QUERY中，如果要掃描多條記錄時，stable函數可能被多次調用。

1、全表掃描，掃描多少條記錄，就需要調用多少次。（同時對于有靜态參數輸入或無參數的stable函數，需要再加一次執行計劃時的評估）

2、索引掃描：

需要區分STABLE函數輸入的是靜态還是動态參數(或volatile函數的傳回值作為參數)。

靜态參數，索引掃描時隻需要調用兩次（不管掃描多少索引條目），一次是執行計劃，一次是索引掃描前計算stable函數的傳回值，然後就是索引掃了。

如果stable函數輸入的是動态參數，不支援索引。是以隻可能走全表。

https://github.com/digoal/blog/blob/master/201806/20180629_02.md#%E4%BE%8B%E5%AD%90 例子

1、建立一個stable函數，裡面通過raise來輸出資訊，判斷這個函數被調用了多少次。

無輸入參數

postgres=# create or replace function ff() returns int as $$  
declare  
begin  
  raise notice 'a';  
  return 1;  
end;  
$$ language plpgsql strict stable;  
CREATE FUNCTION  
           

2、建立測試表

postgres=# create table test123(id int);  
CREATE TABLE  
postgres=# insert into test123 values (1),(2),(3);  
INSERT 0 3  
           

3、生成執行計劃時，stable函數被執行一次（指無動态參數的stable函數，有動态參數的，不執行）

postgres=# explain select * from test123 where id=ff();  
NOTICE:  a  
                        QUERY PLAN                          
----------------------------------------------------------  
 Seq Scan on test123  (cost=0.00..679.38 rows=13 width=4)  
   Filter: (id = ff())  
(2 rows)  
           

4、使用全表掃描，有多少記錄就會被執行多少次，同時加上一次執行計劃的評估，一共調用了4次

postgres=#  select * from test123 where id=ff();  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
 id   
----  
  1  
(1 row)  
           

5、建立索引

postgres=# create index idx_test123 on test123(id);  
CREATE INDEX  
           

6、強制使用索引掃描

postgres=# set enable_seqscan=off;  
SET  
           

7、生成執行計劃，調用一次

postgres=# explain select * from test123 where id=ff();  
NOTICE:  a  
                                   QUERY PLAN                                     
--------------------------------------------------------------------------------  
 Index Only Scan using idx_test123 on test123  (cost=0.38..2.60 rows=1 width=4)  
   Index Cond: (id = ff())  
(2 rows)  
           

8、執行+執行計劃，分别調用一次

postgres=#  select * from test123 where id=ff();  
NOTICE:  a  第一次生成執行計劃  
NOTICE:  a  第二次是索引掃描前的函數值計算  
 id   
----  
  1  
(1 row)  
           

9、新增一條資料

postgres=# insert into test123 values (1);  
INSERT 0 1  
           

10、符合條件的資料有2條，不影響索引掃描時，無參數或無動态參數的stable函數的調用次數

postgres=# select * from test123 where id=ff();  
NOTICE:  a  
NOTICE:  a  
 id   
----  
  1  
  1  
(2 rows)  
           

postgres=#  select * from test123 where id>ff();  
NOTICE:  a  
NOTICE:  a  
 id   
----  
  2  
  3  
(2 rows)  
  
postgres=#  select * from test123 where id>=ff();  
NOTICE:  a  
NOTICE:  a  
 id   
----  
  1  
  1  
  2  
  3  
(4 rows)  
  
postgres=# explain analyze select * from test123 where id>ff();  
NOTICE:  a  
NOTICE:  a  
                                                     QUERY PLAN                                                        
---------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_test123 on test123  (cost=0.38..2.60 rows=1 width=4) (actual time=0.067..0.070 rows=2 loops=1)  
   Index Cond: (id > ff())  
 Planning time: 0.185 ms  
 Execution time: 0.127 ms  
(4 rows)  
           

11、建立一個帶參數的stable函數

create or replace function ff(int) returns int as $$  
declare  
begin  
  raise notice 'a';  
  return 1;  
end;  
$$ language plpgsql strict stable;  
           

12、插入10條重複值

postgres=# insert into test123 select 1 from generate_series(1,10);  
INSERT 0 10  
           

13、使用靜态參數輸入時，可以走索引，是以依舊隻調用2次。

postgres=# select * from test123 where id>ff(1);  
NOTICE:  a  執行計劃  
NOTICE:  a  索引掃描前固定stable函數值  
 id   
----  
  2  
  3  
(2 rows)  
           

postgres=# explain select * from test123 where id>ff(1);  
NOTICE:  a  
                                QUERY PLAN                                   
---------------------------------------------------------------------------  
 Index Scan using idx_test123 on test123  (cost=0.38..2.60 rows=1 width=4)  
   Index Cond: (id > ff(1))  
(2 rows)  
           

14、改成動态參數，那麼就不能走索引掃描，調用次數等于記錄數，因為explain時也不會被調用

調用了14次。

postgres=# explain analyze select * from test123 where id>ff(id);  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
                                                     QUERY PLAN                                                        
---------------------------------------------------------------------------------------------------------------------  
 Seq Scan on test123  (cost=10000000000.00..10000000001.79 rows=1 width=4) (actual time=0.085..0.159 rows=2 loops=1)  
   Filter: (id > ff(id))  
   Rows Removed by Filter: 12  
 Planning time: 0.085 ms  
 Execution time: 0.197 ms  
(5 rows)  
  
postgres=# select * from test123 where id>ff(id);  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
NOTICE:  a  
 id   
----  
  2  
  3  
(2 rows)  
  
postgres=# select count(*) from test123;  
 count   
-------  
    14  
(1 row)  
           

https://github.com/digoal/blog/blob/master/201806/20180629_02.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL Oracle 相容性之 - PL/SQL DETERMINISTIC 與PG函數穩定性(immutable, stable, volatile)》 《函數穩定性講解 - retalk PostgreSQL function's [ volatile|stable|immutable ]》 《函數穩定性講解 - 函數索引思考, pay attention to function index used in PostgreSQL》 《函數穩定性講解 - Thinking PostgreSQL Function's Volatility Categories》