科技

PostgreSQL 電商業務(任意維度商品圈選應用) - json包range陣列的命中優化 - 展開+索引優化

JSON PostgreSQL · 發表 2018-10-05 17:11:23

摘要：標籤 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索引，本文末尾有案例
);

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

其中時間區間可以使用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)

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;

例子

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)

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;

例子

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)

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)

6、壓測

寫入5.3億資料

insert into t_item select * from t_item ;
.....
insert into t_item select * from t_item ;

單表約 186 GB

postgres=# \dt+ t_item
List of relations
 Schema |Name| Type|Owner|Size| Description
--------+--------+-------+----------+--------+-------------
 public | t_item | table | postgres | 186 GB |
(1 row)

7、使用平行計算

postgres=# alter function get_price;
ALTER FUNCTION
postgres=# alter function get_discountparallel 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

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)

56 core 虛擬機器，耗時462秒。

索引優化

將資料展開為兩張表(其中一張可以使用原始表，不需要新建)

其中表1的資料，需要業務方維護，當原價、折扣、調價係數發生變化時，需要實時的更新這裡的記錄。

表1

折扣區間展開表：

商品ID
國家
時間區間
折後價

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

表2

常規價格查原始表：

商品ID
國家
日常價
原價
調價比例

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

SQL

求 "某個國家、某個時間點、調價+折扣後的價格" 落在某個價格範圍的商品。

select id from t_item1 where country ='china' and price < 50::float8 and ts @> 10000000::int8
union
select id from t_item2where 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)