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

digoal 2018-07-03

398

作者

digoal

日期

2018-07-03

背景

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

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)
```