volatile 函数影响子查询提升

我们知道 volatile 函数会影响SQL的执行性能，比如：volatile 类型函数无法建函数索引、volatile 函数针对每条记录都要执行一次。本篇的例子主要讲述 volatile 类型的函数还会影响子查询的提升。

1、构建例子

create table t1(id1 integer,name1 varchar(9),addr1 text);

create table t2(id2 integer,name2 varchar(9),addr2 text);

insert into t1 select generate_series(1,1000000),generate_series(1,1000000),'abc';

insert into t2 select generate_series(1,1000000),generate_series(1,1000000),'abc';

create index ind_t1 on t1(id1);

create index ind_t2 on t2(id2);

2、volatile 函数与执行计划

对于 t2 表的访问无法使用索引。

test=# \df+ replace

                                                                                      List of functions

 Schema |  Name   | Result data type  | Argument data types | Type | Volatility | Parallel | Owner  | Security | Access privileges | Language |           Source code           | Description

--------+---------+-------------------+---------------------+------+------------+----------+--------+----------+-------------------+----------+---------------------------------+-------------

 sys    | replace | character varying | text, text, text    | func | volatile   | safe     | system | invoker  |                   | c        | ora_replace_text                | 

test=# explain select id1,name1 from t1 a ,(select id2,replace(id2,'b','B') name2 from t2 ) b where a.id1=b.id2 and name1='123';

                             QUERY PLAN

---------------------------------------------------------------------

 Hash Join  (cost=17935.01..52120.02 rows=1 width=10)

   Hash Cond: (t2.id2 = a.id1)

   ->  Seq Scan on t2  (cost=0.00..20435.00 rows=1000000 width=36)

   ->  Hash  (cost=17935.00..17935.00 rows=1 width=10)

         ->  Seq Scan on t1 a  (cost=0.00..17935.00 rows=1 width=10)

               Filter: ((name1)::text = '123'::text)

(6 rows)

不使用子查询情况下的，可以使用索引：

test=# explain select  id1,name1,id2,replace(id2,'b','B') name2 from t1 a,t2 b where a.id1=b.id2 and name1='123';

                                  QUERY PLAN

------------------------------------------------------------------------------

 Nested Loop  (cost=0.42..17943.46 rows=1 width=46)

   ->  Seq Scan on t1 a  (cost=0.00..17935.00 rows=1 width=10)

         Filter: ((name1)::text = '123'::text)

   ->  Index Only Scan using ind_t2 on t2 b  (cost=0.42..8.44 rows=1 width=4)

         Index Cond: (id2 = a.id1)

(5 rows)

3、immutable 函数与执行计划

改成immutable 函数后，子查询可以提升，从而能够使用索引。

test=# \df+ replace

                                                                                      List of functions

 Schema |  Name   | Result data type  | Argument data types | Type | Volatility | Parallel | Owner  | Security | Access privileges | Language |           Source code           | Description

--------+---------+-------------------+---------------------+------+------------+----------+--------+----------+-------------------+----------+---------------------------------+-------------

 sys    | replace | character varying | text, text, text    | func | immutable  | safe     | system | invoker  |                   | c        | ora_replace_text                | 

test=# explain select id1,name1 from t1 a ,(select id2,replace(id2,'b','B') name2 from t2 ) b where a.id1=b.id2 and name1='123';

                                 QUERY PLAN

----------------------------------------------------------------------------

 Nested Loop  (cost=0.42..17943.45 rows=1 width=10)

   ->  Seq Scan on t1 a  (cost=0.00..17935.00 rows=1 width=10)

         Filter: ((name1)::text = '123'::text)

   ->  Index Only Scan using ind_t2 on t2  (cost=0.42..8.44 rows=1 width=4)

         Index Cond: (id2 = a.id1)

(5 rows)

巴特西

volatile 函数影响子查询提升

1、构建例子

2、volatile 函数与执行计划

3、immutable 函数与执行计划

最新文章

热门文章