PostgreSQL 优化器知识之-stable 函数调用次数

本文来自阿里云云栖社区，未经许可禁止转载。

更多资讯，尽在云栖科技快讯~

来科技快讯看新闻鸭~

快点关注我认识我爱上我啊~~~

标签

PostgreSQL , 三态 , stable , 调用次数

背景

immutable 函数，输入参数静态时，返回结果不变，所以它在plan前先计算，得到一个常量后，放到QUERY里面再生成执行计划。

stable 函数，输入参数静态时，如果在一个事务中多次调用它，返回结果不变。

显然一个immutable 函数在同一个SQL中，永远只调用一次，（在bind , execute的模式下则只有prepare的时候被调用一次）

但是一个stable函数，虽然输入参数静态时，如果在一个事务中多次调用它，返回结果不变。但是它可能被多次调用。

例如在同一个QUERY中，如果要扫描多条记录时，stable函数可能被多次调用。

1、全表扫描，扫描多少条记录，就需要调用多少次。（同时对于有静态参数输入或无参数的stable函数，需要再加一次执行计划时的评估）

2、索引扫描：

需要区分STABLE函数输入的是静态还是动态参数(或volatile函数的返回值作为参数)。

静态参数，索引扫描时只需要调用两次（不管扫描多少索引条目），一次是执行计划，一次是索引扫描前计算stable函数的返回值，然后就是索引扫了。

如果stable函数输入的是动态参数，不支持索引。所以只可能走全表。

例子

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)