PostgreSQL 函数稳定性与constraint_excluded分区表逻辑推理过滤的CASE

背景

PostgreSQL 函数稳定性我在以前写过一些文章来讲解，而且在PG的优化器中，也有大量的要用函数稳定性来做出优化选择的地方。

http://www.tudou.com/programs/view/p6E3oQEsZv0/

本文要分享的这个CASE也和函数稳定性有关，当我们在使用分区表时，PostgreSQL可以根据分区表的约束，以及用户在SQL中提供的条件进行比对，通过逻辑推理过滤掉一些不需要扫描的表。

逻辑推理在前面也讲过。

《PostgreSQL 优化器逻辑推理能力 源码解析》

这里先抛一个结论，约束检查时，条件中如果有函数，必须是immutable级别的，这样的条件才能进行逻辑推理，过滤掉不需要查询的表。

为什么stable不行呢？

因为执行计划是有缓存的，过滤掉的查询不需要进入执行计划的生成，所以必须保证被过滤的函数在多次调用时得到的结果是一致的，这样可以保证生成的执行计划和不过滤生成的执行计划在输入同样条件时，得到的结果也是一致的。

OK那么就来看个例子吧：

postgres=# create table p1(id int, t int);  
CREATE TABLE  
postgres=# create table c1(like p1) inherits(p1);  
NOTICE:  merging column "id" with inherited definition  
NOTICE:  merging column "t" with inherited definition  
CREATE TABLE  
postgres=# create table c2(like p1) inherits(p1);  
NOTICE:  merging column "id" with inherited definition  
NOTICE:  merging column "t" with inherited definition  
CREATE TABLE  
postgres=# select to_timestamp(123);  
      to_timestamp        
------------------------  
 1970-01-01 08:02:03+08  
(1 row)  
  
postgres=# alter table c1 add constraint ck check(to_char(to_timestamp(t::double precision), 'yyyymmdd'::text) >= '20150101'::text AND to_char(to_timestamp(t::double precision), 'yyyymmdd'::text) < '20150102'::text);  
ALTER TABLE  
postgres=# alter table c2 add constraint ck check(to_char(to_timestamp(t::double precision), 'yyyymmdd'::text) >= '20150102'::text AND to_char(to_timestamp(t::double precision), 'yyyymmdd'::text) < '20150103'::text);  
ALTER TABLE  
postgres=# explain select * from p1 where to_char((to_timestamp(t::double precision)), 'yyyymmdd'::text)='20150101'::text;  
                                             QUERY PLAN                                                
-----------------------------------------------------------------------------------------------------  
 Append  (cost=0.00..110.40 rows=23 width=8)  
   ->  Seq Scan on p1  (cost=0.00..0.00 rows=1 width=8)  
         Filter: (to_char(to_timestamp((t)::double precision), 'yyyymmdd'::text) = '20150101'::text)  
   ->  Seq Scan on c1  (cost=0.00..55.20 rows=11 width=8)  
         Filter: (to_char(to_timestamp((t)::double precision), 'yyyymmdd'::text) = '20150101'::text)  
   ->  Seq Scan on c2  (cost=0.00..55.20 rows=11 width=8)  
         Filter: (to_char(to_timestamp((t)::double precision), 'yyyymmdd'::text) = '20150101'::text)  
(7 rows)  

原因是这两个函数都是stable的

                                                                                         List of functions  
   Schema   |     Name     |     Result data type     | Argument data types |  Type  | Security | Volatility |  Owner   | Language |    Source code     |               Description                  
------------+--------------+--------------------------+---------------------+--------+----------+------------+----------+----------+--------------------+------------------------------------------  
 pg_catalog | to_timestamp | timestamp with time zone | double precision    | normal | invoker  | stable  | postgres | internal | float8_timestamptz | convert UNIX epoch to timestamptz  
 pg_catalog | to_char | text             | timestamp with time zone, text    | normal | invoker  | stable     | postgres | internal | timestamptz_to_char | format timestamp with time zone to text  

stable的函数能保证在一个事务中，使用同样的参数多次调用得到的结果一致，但是不能保证任意时刻。

例如一个会话中，多次调用可能不一致。（那么有执行计划缓存的话，过滤掉这样的子分区就危险了）。

这两个函数为什么是stable 的呢，因为它和一些环境因素有关。

好了，那么了解这个之后，就知道为什么前面的查询没有排除这些约束了。

解决办法 ：

1. 新增用户定义的函数，改SQL以及约束。

create or replace function im_to_char(timestamptz,text) returns text as $$  
select to_char($1,$2);  
$$ language sql immutable;  
  
create or replace function im_to_timestamp(double precision) returns timestamptz as $$  
select to_timestamp($1);  
$$ language sql immutable;  
  
postgres=# alter table c1 drop constraint ck;  
ALTER TABLE  
postgres=# alter table c2 drop constraint ck;  
ALTER TABLE  
  
postgres=# alter table c1 add constraint ck check(im_to_char(im_to_timestamp(t::double precision), 'yyyymmdd'::text) >= '20150101'::text AND im_to_char(im_to_timestamp(t::double precision), 'yyyymmdd'::text) < '20150102'::text);  
ALTER TABLE  
postgres=# alter table c2 add constraint ck check(im_to_char(im_to_timestamp(t::double precision), 'yyyymmdd'::text) >= '20150102'::text AND im_to_char(im_to_timestamp(t::double precision), 'yyyymmdd'::text) < '20150103'::text);  
ALTER TABLE  
  
postgres=# explain select * from p1 where im_to_char((im_to_timestamp(t::double precision)), 'yyyymmdd'::text)='20150101'::text;  
                                                QUERY PLAN                                                   
-----------------------------------------------------------------------------------------------------------  
 Append  (cost=0.00..1173.90 rows=12 width=8)  
   ->  Seq Scan on p1  (cost=0.00..0.00 rows=1 width=8)  
         Filter: (im_to_char(im_to_timestamp((t)::double precision), 'yyyymmdd'::text) = '20150101'::text)  
   ->  Seq Scan on c1  (cost=0.00..1173.90 rows=11 width=8)  
         Filter: (im_to_char(im_to_timestamp((t)::double precision), 'yyyymmdd'::text) = '20150101'::text)  
(5 rows)  

2. 一个冒险的做法是直接修改这两个函数的稳定性。

alter function to_timestamp(double precision) immutable;  
alter function to_char(timestamptz, text) immutable;  

搞定

postgres=# explain select * from p1 where to_char((to_timestamp(t::double precision)), 'yyyymmdd'::text)='20150101'::text;  
                                             QUERY PLAN                                                
-----------------------------------------------------------------------------------------------------  
 Append  (cost=0.00..55.20 rows=12 width=8)  
   ->  Seq Scan on p1  (cost=0.00..0.00 rows=1 width=8)  
         Filter: (to_char(to_timestamp((t)::double precision), 'yyyymmdd'::text) = '20150101'::text)  
   ->  Seq Scan on c1  (cost=0.00..55.20 rows=11 width=8)  
         Filter: (to_char(to_timestamp((t)::double precision), 'yyyymmdd'::text) = '20150101'::text)  
(5 rows)  

digoal’s 大量PostgreSQL文章入口