标签
PostgreSQL , range , jsonb , gist , btree_gist , 展开 , array
背景
电商，任意维度商品圈选应用，其中一个查询请求是这样的：
求 "某个国家、某个时间点、调价+折扣后的价格" 落在某个价格范围的商品。
首先需要有的要素包括：
1、商品ID
2、不同国家的商品价格
3、商品原价
4、商品日常价
5、不同时间段的价格折扣
6、调价系数
例子
1、表结构设计
create table t_item (
id int8 primary key, -- 商品ID
country jsonb, -- 每个国家的价格取值范围
price jsonb, -- 每个时间段的折扣，（时间可能重叠，根据优先级LIMIT 1个折扣）
ratio float4 -- 调价比例
-- 其他属性scalar类型, 使用rum或gin索引，本文末尾有案例
);
AI 代码解读
2、数据样本
insert into t_item values (
1,
jsonb '{"global":{"min": 100, "max":200}, "china":{"min": 120, "max":260}, "us":{"min": 170, "max":300}}',
jsonb '{"100|[1514764800,1515542400)":0.4, "200|[1514764800,1515542400)":0.9, "0|[-62135596800,253402214400)":1}',
0.1
);
AI 代码解读
其中时间区间可以使用epoch表示
postgres=# select extract(epoch from date '2018-01-01');
date_part
------------
1514764800
(1 row)
postgres=# select extract(epoch from date '2018-01-10');
date_part
------------
1515542400
(1 row)
postgres=# select extract(epoch from date '0001-01-01');
date_part
--------------
-62135596800
(1 row)
postgres=# select extract(epoch from date '9999-12-31');
date_part
--------------
253402214400
(1 row)
AI 代码解读
3、由于不同时间段的折扣不一样，并且优先级也不一样，所以，使用一个函数来获取某个时间点的这块。
当输入的时间点有多个时间区间包括它时，取优先级最高的那个折扣，并返回，如果没有任何匹配的时间区间，则返回1。
create or replace function get_discount(
jsonb, -- 每个时间段的折扣字段
int8 -- epoch 时间值
) returns float4 as $$
declare
res float4;
begin
-- select split_part(key,'|',1) as priority, split_part(key,'|',2) as ts, value from jsonb_each_text($1);
select value into res from jsonb_each_text($1) where split_part(key,'|',2)::int8range @> $2 order by split_part(key,'|',1)::numeric desc limit 1;
if found then
return res;
end if;
return 1;
end;
$$ language plpgsql strict parallel safe;
AI 代码解读
例子
postgres=# select get_discount(jsonb '{"100|[1514764800,1515542400)":0.4, "200|[1514764800,1515542400)":0.9, "0|[-62135596800,253402214400)":1}', 100000);
get_discount
--------------
1
(1 row)
postgres=# select get_discount(jsonb '{"100|[1514764800,1515542400)":0.4, "200|[1514764800,1515542400)":0.9, "0|[-62135596800,253402214400)":1}', 1515542200);
get_discount
--------------
0.9
(1 row)
AI 代码解读
4、不同的国家，价格不一样，输入国家编码，返回对应国家的价格，如果输入的编码在JSONB中没有，则返回global的价格。
create or replace function get_price(
jsonb, -- 国家价格区间
text -- 国家编码
) returns float8 as $$
select case when ($1->$2->>'max')::float8 is not null then ($1->$2->>'max')::float8 else ($1->'global'->>'max')::float8 end;
$$ language sql strict parallel safe;
AI 代码解读
例子
postgres=# select get_price(jsonb '{"global":{"min": 100, "max":200}, "china":{"min": 120, "max":260}, "us":{"min": 170, "max":300}}', 'hello');
get_price
-----------
200
(1 row)
postgres=# select get_price(jsonb '{"global":{"min": 100, "max":200}, "china":{"min": 120, "max":260}, "us":{"min": 170, "max":300}}', 'china');
get_price
-----------
260
(1 row)
AI 代码解读
5、求 "某个国家、某个时间点、调价+折扣后的价格" 落在某个价格范围的商品。
SQL
postgres=# select * from t_item where get_price(country, 'china') * get_discount(price, 1515542200) * (1+ratio) < 100;
id | country | price | ratio
----+---------+-------+-------
(0 rows)
postgres=# select * from t_item where get_price(country, 'china') * get_discount(price, 1515542200) * (1+ratio) < 1000;
id | country | price | ratio
----+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------+-------
1 | {"us": {"max": 300, "min": 170}, "china": {"max": 260, "min": 120}, "global": {"max": 200, "min": 100}} | {"100|[1514764800,1515542400)": 0.4, "200|[1514764800,1515542400)": 0.9, "0|[-62135596800,253402214400)": 1} | 0.1
(1 row)
AI 代码解读
6、压测
写入5.3亿数据
insert into t_item select * from t_item ;
.....
insert into t_item select * from t_item ;
AI 代码解读
单表约 186 GB
postgres=# \dt+ t_item
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+----------+--------+-------------
public | t_item | table | postgres | 186 GB |
(1 row)
AI 代码解读
7、使用并行计算
postgres=# alter function get_price ;
ALTER FUNCTION
postgres=# alter function get_discount parallel safe;
ALTER FUNCTION
postgres=# set max_parallel_workers_per_gather =56;
SET
postgres=# alter table t_item set (parallel_workers =56);
ALTER TABLE
postgres=# set min_parallel_table_scan_size =0;
SET
postgres=# set min_parallel_index_scan_size =0;
SET
postgres=# set parallel_setup_cost =0;
SET
postgres=# set parallel_tuple_cost =0;
SET
AI 代码解读
8、最差的情况，没有一条命中的数据，耗时为处理完5.3亿条记录的耗时
postgres=# explain select * from t_item where get_price(country, 'china') * get_discount(price, 1515542200) * (1+ratio) < 100 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..51024073.42 rows=178956971 width=332)
Workers Planned: 32
-> Parallel Seq Scan on t_item (cost=0.00..33127376.32 rows=5592405 width=332)
Filter: (((get_price(country, 'china'::text) * get_discount(price, '1515542200'::bigint)) * ('1'::double precision + ratio)) < '100'::double precision)
(4 rows)
postgres=# explain analyze select * from t_item where get_price(country, 'china') * get_discount(price, 1515542200) * (1+ratio) < 100 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..47285151.00 rows=178956971 width=332) (actual time=444448.106..444448.106 rows=0 loops=1)
Workers Planned: 56
Workers Launched: 56
-> Parallel Seq Scan on t_item (cost=0.00..29388453.90 rows=3195660 width=332) (actual time=444292.055..444292.055 rows=0 loops=57)
Filter: (((get_price(country, 'china'::text) * get_discount(price, '1515542200'::bigint)) * ('1'::double precision + ratio)) < '100'::double precision)
Rows Removed by Filter: 9418788
Planning Time: 0.072 ms
Execution Time: 462253.627 ms
(8 rows)
AI 代码解读
56 core 虚拟机，耗时462秒。
索引优化
将数据展开为两张表(其中一张可以使用原始表，不需要新建)
其中表1的数据，需要业务方维护，当原价、折扣、调价系数发生变化时，需要实时的更新这里的记录。
表1
折扣区间展开表：
商品ID 国家 时间区间 折后价AI 代码解读
create table t_item1 (
id int8,
country text,
ts int8range,
price float8,
exclude using gist (id with =, country with =, ts with &&) -- 排他约束，同一个商品ID同一个国家不允许有TS相交的折扣数据
);
create extension IF NOT EXISTS btree_gist;
create index idx_t_item1_1 on t_item1 using gist (country,price,ts);
AI 代码解读
表2
常规价格查原始表：
商品ID 国家 日常价 原价 调价比例AI 代码解读
create table t_item2 (
id int8,
country text,
price1 float8,
price2 float8,
ratio float4,
primary key (country, id)
);
create index idx_t_item2_1 on t_item2 (country, (least(price1*ratio,price2*ratio)));
AI 代码解读
SQL
求 "某个国家、某个时间点、调价+折扣后的价格" 落在某个价格范围的商品。
select id from t_item1 where country ='china' and price < 50::float8 and ts @> 10000000::int8
union
select id from t_item2 where country='china' and (least(price1*ratio,price2*ratio)) < 50;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=5.91..5.94 rows=3 width=8)
Group Key: t_item1.id
-> Append (cost=0.14..5.90 rows=3 width=8)
-> Index Scan using idx_t_item1_1 on t_item1 (cost=0.14..2.37 rows=1 width=8)
Index Cond: ((country = 'china'::text) AND (price < '50'::double precision) AND (ts @> '10000000'::bigint))
-> Index Scan using idx_t_item2_1 on t_item2 (cost=0.15..3.49 rows=2 width=8)
Index Cond: ((country = 'china'::text) AND (LEAST((price1 * ratio), (price2 * ratio)) < '50'::double precision))
(7 rows)
AI 代码解读
小结
第一种设计，简化了程序开发，但是无法使用索引扫描，性能会比较差。
第二种设计，当调价比例、原价、折扣数据发生变化时，程序需要维护价格的变更到t_item1表，程序开发上会增加一定的负担，（当然也可以使用数据库触发器来更新，程序偷一下懒，但是不推荐这么做）。
参考
《PostgreSQL 函数式索引使用注意 - 暨非immutable函数不适合索引的原因》
《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化，普通、数组等组合字段生成新数组》
《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》
《PostgreSQL 店铺运营实践 - JSON[]数组 内部标签数据等值、范围检索100倍+加速示例 (含，单值+多值列合成)》
《会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束》
https://www.postgresql.org/docs/10/static/datatype-json.html#JSON-INDEXING
