标簽
PostgreSQL , 表達式 , 函數穩定性 , immutable
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E8%83%8C%E6%99%AF 背景
PostgreSQL支援表達式索引，但是表達式必須是immutable的，也即是當輸入參數不變時，結果是永恒不變的。
因為當表達式涉及的變量不變時，索引本身不會變化。
給個例子，如果我們有一張表存儲了商品價格，另一張表存儲了商品折扣 ，如果我們想通過折扣後的價格範圍搜尋符合價格區間的商品ID，可以使用索引嗎？
表達式索引，可以。但是前提是：輸入一個商品ID時，商品原價永恒不變。
否則原價發生變化就可能出現索引内容與實際不一緻的問題。
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E4%BE%8B%E5%AD%90 例子
create extension btree_gist;
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E5%95%86%E5%93%81%E8%A1%A8 商品表
create table t_item (id int8 primary key, price jsonb);
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E6%8A%98%E6%89%A3%E8%A1%A8 折扣表
create table t_item_discount (id int8, ts daterange, country text, discount float4);
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E8%8E%B7%E5%8F%96%E5%95%86%E5%93%81%E6%8A%98%E5%90%8E%E4%BB%B7%E6%A0%BC%E7%9A%84%E5%87%BD%E6%95%B0 擷取商品折後價格的函數
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;
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E5%87%BD%E6%95%B0%E7%B4%A2%E5%BC%95immutable%E5%87%BD%E6%95%B0 函數索引，immutable函數
create index idx_t_item_discount_1 on t_item_discount using gist (ts, country, get_price(id,country,discount));
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E5%86%99%E5%85%A5%E5%95%86%E5%93%81 寫入商品
insert into t_item values (1, jsonb '{"global":200, "china":150}');
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E5%86%99%E5%85%A5%E6%8A%98%E6%89%A3 寫入折扣
insert into t_item_discount values (1, daterange('2018-01-01', '2018-01-10'), 'global', 0.4);
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E5%BC%BA%E5%88%B6%E7%B4%A2%E5%BC%95%E6%89%AB%E6%8F%8F 強制索引掃描
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)
https://github.com/digoal/blog/blob/master/201807/20180703_01.md#%E4%BD%86%E6%98%AF%E5%A6%82%E6%9E%9C%E5%8E%9F%E4%BB%B7%E5%8F%98%E5%8C%96%E7%B4%A2%E5%BC%95%E5%B9%B6%E4%B8%8D%E4%BC%9A%E6%9B%B4%E6%96%B0 但是如果原價變化，索引并不會更新
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)
![MySQL - order by排序優化[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)