PostgreSQL 函数式索引使用注意 - 暨非immutable函数不适合索引的原因

背景

PostgreSQL支持表达式索引，但是表达式必须是immutable的，也即是当输入参数不变时，结果是永恒不变的。

因为当表达式涉及的变量不变时，索引本身不会变化。

给个例子，如果我们有一张表存储了商品价格，另一张表存储了商品折扣 ，如果我们想通过折扣后的价格范围搜索符合价格区间的商品ID，可以使用索引吗？

表达式索引，可以。但是前提是：输入一个商品ID时，商品原价永恒不变。

否则原价发生变化就可能出现索引内容与实际不一致的问题。

例子

create extension btree_gist;  

商品表

create table t_item (id int8 primary key, price jsonb);  

折扣表

create table t_item_discount (id int8, ts daterange, country text, discount float4);  

获取商品折后价格的函数

create or replace function get_price(int8,text,float4) returns float8 as $$  
  select (price->>$2)::float8*$3 from t_item where id=$1;  
$$ language sql strict immutable;  

函数索引，immutable函数

create index idx_t_item_discount_1 on t_item_discount using gist (ts, country, get_price(id,country,discount));  

写入商品

insert into t_item values (1, jsonb '{"global":200, "china":150}');  

写入折扣

insert into t_item_discount values (1, daterange('2018-01-01', '2018-01-10'), 'global', 0.4);  

强制索引扫描

set enable_bitmapscan=off;  
set enable_seqscan=off;  
  
postgres=# explain select ctid,get_price(id,country,discount),* from t_item_discount where ts @> '2018-01-01'::date and get_price(id,country,discount)<300 and country='china';  
                                                                QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_t_item_discount_1 on t_item_discount  (cost=0.12..8.40 rows=1 width=90)  
   Index Cond: ((ts @> '2018-01-01'::date) AND (country = 'china'::text) AND (get_price(id, country, discount) < '300'::double precision))  
(2 rows)  
  
postgres=# explain select ctid,get_price(id,country,discount),* from t_item_discount where ts @> '2018-01-01'::date and country='china' and get_price(id,country,discount)<300;  
                                                                QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx_t_item_discount_1 on t_item_discount  (cost=0.12..8.40 rows=1 width=90)  
   Index Cond: ((ts @> '2018-01-01'::date) AND (country = 'china'::text) AND (get_price(id, country, discount) < '300'::double precision))  
(2 rows)  
  
  
  
postgres=# select ctid,get_price(id,country,discount),* from t_item_discount where ts @> '2018-01-01'::date and country='global' and get_price(id,country,discount)<300;  
 ctid  |    get_price     | id |           ts            | country | discount   
-------+------------------+----+-------------------------+---------+----------  
 (0,1) | 80.0000011920929 |  1 | [2018-01-01,2018-01-10) | global  |      0.4  
(1 row)  

但是如果原价变化，索引并不会更新

postgres=# update t_item set price = jsonb '{"global":2000, "china":1500}' where id=1;  
UPDATE 1  

下面的结果显然是错误的

postgres=# select ctid,get_price(id,country,discount),* from t_item_discount where ts @> '2018-01-01'::date and country='global' and get_price(id,country,discount)<300;  
 ctid  |    get_price     | id |           ts            | country | discount   
-------+------------------+----+-------------------------+---------+----------  
 (0,1) | 800.000011920929 |  1 | [2018-01-01,2018-01-10) | global  |      0.4  
(1 row)  

postgres=# update t_item_discount set discount = discount where id=1;  
UPDATE 1  
postgres=# select ctid,get_price(id,country,discount),* from t_item_discount where ts @> '2018-01-01'::date and country='global' and get_price(id,country,discount)<300;  
 ctid  |    get_price     | id |           ts            | country | discount   
-------+------------------+----+-------------------------+---------+----------  
 (0,2) | 800.000011920929 |  1 | [2018-01-01,2018-01-10) | global  |      0.4  
(1 row)  

只有当表达式字段内容发生变化时，相应的表达式才会变化

postgres=# update t_item_discount set discount=discount+0.0000001 where id=1;  
UPDATE 1  
  
postgres=# select ctid,get_price(id,country,discount),* from t_item_discount where ts @> '2018-01-01'::date and country='global' and get_price(id,country,discount)<300;  
 ctid | get_price | id | ts | country | discount   
------+-----------+----+----+---------+----------  
(0 rows)  
  
postgres=# select float4send(discount),* from t_item_discount ;  
 float4send | id |           ts            | country | discount   
------------+----+-------------------------+---------+----------  
 \x3eccccd0 |  1 | [2018-01-01,2018-01-10) | global  |      0.4  
(1 row)  

参考

《PostgreSQL Oracle 兼容性之 - PL/SQL DETERMINISTIC 与PG函数稳定性(immutable, stable, volatile)》

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

[《函数稳定性讲解 - retalk PostgreSQL function’s [ volatile

stable

immutable ]》](../201212/20121226_01.md)

《函数稳定性讲解 - 函数索引思考, pay attention to function index used in PostgreSQL》

《函数稳定性讲解 - Thinking PostgreSQL Function’s Volatility Categories》

digoal’s 大量PostgreSQL文章入口