PostgreSQL View 包含volatile functions 阻止谓词推进(Predicate PushDown)

  • zhangweizhao

    2024-12-02 23:03:25
  • 数据库开发技术
  • 原创

《Oracle、Oceanbase、Kingbase、GaussDB、达梦数据库比较系列(二十七):子查询中的函数投影裁剪》 测试在inline subquery中包含函数列投影时裁剪或叫SLP(select list pruning) ,如果函数的Volate属性是volatile的影响，函数的不稳定性除了影响投影还有join 的view包含该函数时，影响谓词条件的推入等，最近在highgoDB遇到了一个SQL性能问题，其实所有pg系数据库如gaussdb,opengauss,kingbase等都存在。下面演示一下这个问题。
构建测试数据

-- pg
create table t1 as
 select x as id, 'anbob'||x name,'03-07-2023'::timestamptz +(b::integer||' minute')::interval  ctime from generate_series(1,10000) as x(b);

create index idx_t1_ctime on t1(ctime);

create table t2(id int,ctime date,name text);

insert into t2 values(1,now(),'anbob');
insert into t2 values(2,now()+'1 day'::interval,'weejar');
insert into t2 values(3,'03-07-2023 01:00:00'::timestamptz,'anbob');


CREATE OR REPLACE FUNCTION f2(id integer)
 returns bigint
 language 'plpgsql'
 as
 $$
 begin
 return case when id>10 then 1 else 2 end;
 end;
 $$ ;

create or replace view v1 as
select id,name,ctime,f2(id) flag from t1 where name<>'anbob100';

select * from t2 , v1 
where t2.id=t2.id and t2.ctime >= to_date('2023-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')                                                                                                                                          
             AND t2.ctime < to_date('2023-03-08', 'yyyy-mm-dd hh24:mi:ss') and v1.ctime>t2.ctime-('100 minute')::interval
and v1.ctime 'anbob1' or t2.name<>'weejar1');


-- oracle
create table t1 tablespace users as
 select rownum id, 'anbob'||rownum name, to_date('03-07-2023','mm-dd-yyyy')+rownum/24/60   ctime from dual connect by rownum<=10000; create index idx_t1_ctime on t1(ctime); create table t2(id int,ctime date,name varchar2(100)) tablespace users; insert into t2 values(1,sysdate,'anbob'); insert into t2 values(2,sysdate+1,'weejar'); insert into t2 values(3,to_date('03-07-2023 01:00:00','mm-dd-yyyy hh24:mi:ss'),'anbob'); CREATE OR REPLACE FUNCTION f2(id integer) return int is begin return case when id>10 then 1 else 2 end;
 end;

create or replace view v1 as
select id,name,ctime,f2(id) flag from t1 where name<>'anbob100';

Note:
创建了两张表和一个view, 其中view中有一列是函数，注意这里的函数我仅为了演示使用的是常量，可以改稳定性，不排除如果是不能改稳定的函数时，这类问题就需要改SQL了。

先看oracle的执行计划

select * from t2 , v1 
where t2.id=t2.id and t2.ctime >= to_date('2023-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')                                                                                                                                          
             AND t2.ctime < to_date('2023-03-08', 'yyyy-mm-dd hh24:mi:ss') and v1.ctime>t2.ctime-100/24/60
and v1.ctime 'anbob1' or t2.name<>'weejar1');

Plan hash value: 1917871493

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    28 |  1092 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                       |              |       |       |            |          |
|   2 |   NESTED LOOPS                |              |    28 |  1092 |     6   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |              |   260 |  1092 |     6   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | T2           |     1 |    17 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | IDX_T1_CTIME |   260 |       |     2   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| T1           |    37 |   814 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('2023-03-08','yyyy-mm-dd hh24:mi:ss')>TO_DATE('2023-03-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - filter(("T2"."NAME"<>'anbob1' OR "T2"."NAME"<>'weejar1') AND
              "T2"."CTIME"=TO_DATE(' 2023-03-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              INTERNAL_FUNCTION("T2"."CTIME")-.0694444444444444444444444444444444444445   5 - access("CTIME">INTERNAL_FUNCTION("T2"."CTIME")-.0694444444444444444444444444444
              444444445 AND "CTIME"
       filter("CTIME"<"T2"."CTIME")
   6 - filter("NAME"<>'anbob100')

Note：
#5 显示谓词已推到view v1,使用了ctime上的索引。

hint禁用oracle的特性观察一下

SQL> select /*+no_merge(v1)*/* from t2 , v1
where t2.id=t2.id and t2.ctime >= to_date('2023-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
             AND t2.ctime < to_date('2023-03-08', 'yyyy-mm-dd hh24:mi:ss') 4 and v1.ctime>t2.ctime-100/24/60
  5  and v1.ctime 'anbob1' or t2.name<>'weejar1');

SQL> @x2

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------  
Plan hash value: 2510977736

-------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |    28 |  2128 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                          |              |    28 |  2128 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                    | T2           |     1 |    17 |     3   (0)| 00:00:01 |
|   3 |   VIEW PUSHED PREDICATE                | V1           |     1 |    59 |     4   (0)| 00:00:01 |
|*  4 |    FILTER                              |              |       |       |            |          |
|*  5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1           |     4 |    88 |     4   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | IDX_T1_CTIME |    25 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("T2"."NAME"<>'anbob1' OR "T2"."NAME"<>'weejar1') AND
              "T2"."CTIME"=TO_DATE(' 2023-03-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - filter("T2"."CTIME">INTERNAL_FUNCTION("T2"."CTIME")-.06944444444444444444444444444444444
              44445 AND INTERNAL_FUNCTION("T2"."CTIME")-.0694444444444444444444444444444444444445TO_DATE('
              2023-03-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter("NAME"<>'anbob100')
   6 - access("CTIME">INTERNAL_FUNCTION("T2"."CTIME")-.0694444444444444444444444444444444444445
               AND "CTIME"       filter("CTIME"<"T2"."CTIME") select /*+no_merge(v1) NO_PUSH_PRED(v1)*/* from t2 , v1 where t2.id=t2.id and t2.ctime >= to_date('2023-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')                                                                                                                                          
             AND t2.ctime < to_date('2023-03-08', 'yyyy-mm-dd hh24:mi:ss') and v1.ctime>t2.ctime-100/24/60
and v1.ctime 'anbob1' or t2.name<>'weejar1');

SQL> @x2
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------  
Plan hash value: 1639856690

-------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |    28 |  2128 |    14   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                          |              |    28 |  2128 |    14   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                    | T2           |     1 |    17 |     3   (0)| 00:00:01 |
|*  3 |   VIEW                                 | V1           |    37 |  2183 |    11   (0)| 00:00:01 |
|*  4 |    FILTER                              |              |       |       |            |          |
|*  5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1           |  1439 | 31658 |    11   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | IDX_T1_CTIME |  1439 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("T2"."NAME"<>'anbob1' OR "T2"."NAME"<>'weejar1') AND
              "T2"."CTIME"=TO_DATE(' 2023-03-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              INTERNAL_FUNCTION("T2"."CTIME")-.0694444444444444444444444444444444444445              'yyyy-mm-dd hh24:mi:ss'))
   3 - filter("V1"."CTIME"<"T2"."CTIME" AND "V1"."CTIME">INTERNAL_FUNCTION("T2"."CTIME")-.06944
              44444444444444444444444444444444445)
   4 - filter(TO_DATE('2023-03-08','yyyy-mm-dd hh24:mi:ss')>TO_DATE(' 2023-03-07 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))
   5 - filter("NAME"<>'anbob100')
   6 - access("CTIME"
28 rows selected.

达梦

explain 
select * from t2 , v1 
where t2.id=t2.id and t2.ctime >= to_date('2023-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')                                                                                                                                          
             AND t2.ctime < to_date('2023-03-08', 'yyyy-mm-dd hh24:mi:ss') and v1.ctime>t2.ctime-100/24/60
and v1.ctime 'anbob1' or t2.name<>'weejar1');

1   #NSET2: [1, 375, 134]
2     #PRJT2: [1, 375, 134]; exp_num(7), is_atom(FALSE)
3       #SLCT2: [1, 375, 134]; T1.NAME <> 'anbob100'
4         #NEST LOOP INDEX JOIN2: [1, 375, 134]
5           #SLCT2: [1, 1, 65]; (NOT(T2.NAME IS NULL) AND NOT(T2.ID IS NULL) AND T2.CTIME >= var2 AND T2.CTIME < var4)
6             #CSCN2: [1, 3, 65]; INDEX33708464(T2); btr_scan(1)
7           #BLKUP2: [1, 375, 13]; IDX_T1_CTIME(T1)
8             #SSEK2: [1, 375, 13]; scan_type(ASC), IDX_T1_CTIME(T1), scan_range(exp_cast(T2.CTIME-exp_cast(100/24/60)),exp_cast(T2.CTIME))

used time: 30.703(ms). Execute id is 0.

Highgo V9（pg14）

SET track_io_timing = TRUE;
RESET enable_hashjoin;
RESET enable_sort;

explain (analyze,verbose)
select * from t2 , v1 
where t2.id=t2.id and t2.ctime >= to_date('2023-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')                                                                                                                                          
             AND t2.ctime < to_date('2023-03-08', 'yyyy-mm-dd hh24:mi:ss') and v1.ctime>t2.ctime-('100 minute')::interval
and v1.ctime 'anbob1' or t2.name<>'weejar1');

                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2964.80 rows=1111 width=43) (actual time=12.208..12.209 rows=0 loops=1)
   Output: t2.id, t2.ctime, t2.name, t1.id, t1.name, t1.ctime, (f2(t1.id))
   Join Filter: ((t1.ctime < t2.ctime) AND (t1.ctime > (t2.ctime - '01:40:00'::interval)))
   Rows Removed by Join Filter: 9999
   ->  Seq Scan on public.t2  (cost=0.00..1.07 rows=1 width=14) (actual time=0.021..0.021 rows=1 loops=1)
         Output: t2.id, t2.ctime, t2.name
         Filter: ((t2.id IS NOT NULL) AND (t2.ctime >= to_date('2023-03-07 00:00:00'::text, 'yyyy-mm-dd hh24:mi:ss'::text)) AND (t2.ctime < to_date('2023-03-08'::text, 'yyyy-mm-dd hh24:mi:ss'::text)) AND ((t
2.name <> 'anbob1'::text) OR (t2.name <> 'weejar1'::text)))
         Rows Removed by Filter: 2
   ->  Seq Scan on public.t1  (cost=0.00..2688.75 rows=9999 width=29) (actual time=0.038..10.733 rows=9999 loops=1)
Output: t1.id, t1.name, t1.ctime, f2(t1.id)
         Filter: (t1.name <> 'anbob100'::text)
         Rows Removed by Filter: 1
 Planning Time: 0.186 ms
 Execution Time: 12.288 ms
(14 rows)

Note:
这里t1表使用的全表扫seq scan. 使用了nested loop join最外层 join里过滤时间， 而t1表的大小就决定了SQL的运行时间，在生产上刚好我们的表是个大分区表有几亿条记录，运行时间要等十几分钟。开始以为是function cost影响，修改后function cost并不会影响v1的访问路径。

修改函数volatile到immutable

alter function f2 immutable;

SET track_io_timing = TRUE;
explain (analyze,verbose)
select * from t2 , v1 
where t2.id=t2.id and t2.ctime >= to_date('2023-03-07 00:00:00', 'yyyy-mm-dd hh24:mi:ss')                                                                                                                                          
             AND t2.ctime < to_date('2023-03-08', 'yyyy-mm-dd hh24:mi:ss') and v1.ctime>t2.ctime-('100 minute')::interval
and v1.ctime 'anbob1' or t2.name<>'weejar1');


                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..342.22 rows=1111 width=43) (actual time=0.055..0.056 rows=0 loops=1)
   Output: t2.id, t2.ctime, t2.name, t1.id, t1.name, t1.ctime, f2(t1.id)
   ->  Seq Scan on public.t2  (cost=0.00..1.07 rows=1 width=14) (actual time=0.024..0.025 rows=1 loops=1)
         Output: t2.id, t2.ctime, t2.name
         Filter: ((t2.id IS NOT NULL) AND (t2.ctime >= to_date('2023-03-07 00:00:00'::text, 'yyyy-mm-dd hh24:mi:ss'::text)) AND (t2.ctime < to_date('2023-03-08'::text, 'yyyy-mm-dd hh24:mi:ss'::text)) AND ((t
2.name <> 'anbob1'::text) OR (t2.name <> 'weejar1'::text)))
         Rows Removed by Filter: 2
   ->  Index Scan using idx_t1_ctime on public.t1  (cost=0.29..52.29 rows=1111 width=21) (actual time=0.026..0.026 rows=0 loops=1)
Output: t1.id, t1.name, t1.ctime
 Index Cond: ((t1.ctime > (t2.ctime - '01:40:00'::interval)) AND (t1.ctime < t2.ctime))
         Filter: (t1.name <> 'anbob100'::text)
 Planning Time: 0.252 ms
 Execution Time: 0.102 ms
(12 rows)

Note：
这里ctime条件pushdown到了v1 view中，在访问T1时，使用上了索引，并且也做了投影裁剪，执行效率明显提升，而生产环境上的案例SQL直接从10几分钟提到秒级。这里索引访问显示有Index Cond和Filter两个算子，目标是一样的，但是实现方式完全不同。Index Cond 用于从index索引中查找行位置的条件，Postgres 使用索引的结构化特性快速跳转到它要查找的行。不同的索引类型使用不同的策略。而在 “Filter” 中，根据row行的值检索并丢弃行。因此，您可以在同一操作中找到 “Index Cond” 和 “Filter”。索引改为volatile,执行计划同immutable.

执行计划美化后，看起来更直观

不能推进的原因
当开源数据库找不到相关官方文档，查找源代码是个不错的方向 https://github.com/postgres/postgres/blob/master/src/backend/optimizer/path/allpaths.c
查找相关set_subquery_pathlist 里面的 subquery_is_pushdown_safe() 和 qual_is_pushdown_safe()函数.和contain_volatile_functions()


请使用浏览器的分享功能分享到微信等