标签
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 ]》
《函数稳定性讲解 - 函数索引思考, pay attention to function index used in PostgreSQL》
《函数稳定性讲解 - Thinking PostgreSQL Function's Volatility Categories》
