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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

标签

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》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
SQL 机器学习/深度学习 存储
【重新发现PostgreSQL之美】- 38 肝者,将军之官,谋虑出焉. 优化器
大家好,这里是重新发现PostgreSQL之美 - 38 肝者,将军之官,谋虑出焉. 优化器
|
SQL Oracle 关系型数据库
PostgreSQL JOIN limit 优化器 成本计算 改进 - mergejoin startup cost 优化
标签 PostgreSQL , join , limit , startup cost , cbo , 优化器改进 背景 PostgreSQL limit N的成本估算,是通过计算总成本A,以及估算得到的总记录数B得到: (N/B)*A 大概意思就是占比的方法计算 对于单表查询...
1137 0
|
算法 关系型数据库 C语言
PostgreSQL 当有多个索引可选时,优化器如何选择
标签 PostgreSQL , 索引 , 复合索引 , 选择 , 成本 , 优化器 背景 当一个表有很多索引时,并且一个QUERY可以使用到其中的多个索引时,数据库会如何做出选择?最终选择哪个,或者哪几个索引呢? 《PostgreSQL 多查询条件,多个索引的选择算法与问题诊断方法》 选择单个索引时,PATH可以选择index scan , index only scan, bitmap scan。
2781 0
|
存储 关系型数据库 PostgreSQL
PostgreSQL优化器之从一个关于扫描方式选择引发的思考
# 一个关于PostgreSQL使用组合索引的问题 近期阅读了《数据库查询优化器的艺术》这本书,对PG和Mysql优化器技术的轮廓有了一定了解。在阅读的过程中,因为知识背景和书本身的表述问题产生了许多困惑,这里就分享对其中一个困惑的探索过程作为看完书的总结。 在这本书的第十八章,关于PG和Mysql的优化器对于索引的优化能力对比中的一段让我困惑不已。如图一所示,单独使用组合索引的后半部分作为查
4620 0
|
关系型数据库 物联网 PostgreSQL
PostgreSQL技术周刊第16期:PostgreSQL 优化器代码概览
PostgreSQL(简称PG)的开发者们:云栖社区已有5000位PG开发者,发布了3000+PG文章(文章列表),沉淀了700+的PG精品问答(问答列表)。 PostgreSQL技术周刊会为大家介绍最新的PG技术与动态、预告活动、最热问答、直播教程等,欢迎大家订阅PostgreSQL技术周刊。
3496 0
|
SQL 算法 关系型数据库
PostgreSQL 优化器代码概览
## 简介 PostgreSQL 的开发源自上世纪80年代,它最初是 Michael Stonebraker 等人在美国国防部支持下创建的POSTGRE项目。上世纪末,Andrew Yu 等人在它上面搭建了第一个SQL Parser,这个版本称为Postgre95,也是加州大学伯克利分校版本的PostgreSQL的基石[1]。
1549 0
|
SQL 关系型数据库 PostgreSQL

相关产品

  • 云原生数据库 PolarDB