PostgreSQL 优化器知识之 - stable 函数调用次数-阿里云开发者社区

开发者社区> 德哥> 正文

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)  

参考

《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》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
postman 接口返回值有多个相同key时(不唯一),怎么提取第2个value给下一接口调用?
postman 接口返回值有多个相同key时(不唯一),怎么提取第2个value给下一接口调用?
3416 0
UITableView性能优化-中级篇
老实说,UITableView性能优化 这个话题,最经常遇到的还是在面试中,常见的回答例如: Cell复用机制 Cell高度预先计算 缓存Cell高度 圆角切割 等等. . .
410 0
C#跨窗体相互调用对方函数
对象对象程序设计,为跨窗体调用提供了良药:即构造函数。只要将某一个窗体对象以参数形式传入到另一个窗体对象的构造函数,即可实现。 一、实际案例需求 图1 Form1窗体设计   图2 Form2窗体设计   如图1、图2,现在要求图1按下按键,自己锁住,弹出图2;然后图2按下按键,自己关闭,解放图1的按键。
915 0
[实变函数]4.1 可测函数 (measurable function) 及其性质
1 记号 (notations)       (1) 广义实数: $\overline{\bbR}=\bbR\cup\sed{-\infty}\cup\sed{+\infty}$.      (2) 本章主要考虑     $$\bex     f:E\to \overline{\bbR},   ...
650 0
函数调用
#!/usr/bin/pythondef Cube(x=None,y=None,z=None):        if x==None:                x=1        if y==None:                y=2        if z==None:       ...
497 0
函数计算助力新浪微博完成每日数十亿次个性图片处理
新浪微博(Sina Weibo)是基于用户关系的社交媒体平台,用户可以通过 PC、手机等多种移动终端接入,以文字、图片、视频等多媒体形式,实现信息的即时分享、传播互动。任何用户都可以创作并发布微博,人人都是世界的主角。微博基于公开平台架构,提供简单、前所未有的方式使用户能够公开实时发表内容,通过裂变式传播,让用户与他人互动并与世界紧密相连。
933 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载