PostgreSQL實踐-實時廣告位推薦2(任意欄位組合、任意維度組合搜尋、輸出TOP-K)
標籤
PostgreSQL , gin , 倒排 , rum , gin_fuzzy_search_limit , 隨機取樣 , 分割槽索引 , 分段索引 , score分段
背景
任意欄位組合查詢的幾種優化方法:
1、列存
2、RUM
3、GIN
4、多個INDEX的BITMAP AND|OR SCAN
5、BLOOM FILTER
《PostgreSQL 實踐 – 廣告位推薦 1》 採用了RUM的方法,採用rum的目的是避免GIN的CPU RECHECK,但是當我們查詢時如果業務方允許使用GIN的取樣限制,則沒有必要使用RUM了。
《[未完待續] PostgreSQL 全文檢索 大結果集優化 – fuzzy match》
本例子採用一種新的設計來實現電商個性化推薦(例如,開啟首頁,開啟一個店鋪,開啟一個頁面時,根據使用者的行為,實時推薦對應頁面涉及的內容中的優選內容(被推薦的可能是商品、類目等))。
設計1
基本思想是使用GIN倒排索引,同時引入fuzzy match引數來過濾海量資料,在一個較小的結果集內排序輸出。
注意此法需要業務方允許在取樣中輸出才可以。
1、字典表
create table tbl_dict (
dim text, -- 維度
val int8 not null unique, -- 維度內的對映值(為了讓所有維度可以打到一個陣列裡面,取值空間唯一)
info text -- 原始值、描述
);
create index idx_tbl_dict_1 on tbl_dict(dim,info);
獲得維度值
select val from tbl_dict where dim=? and info=?;
2、行為標籤表
create table tbl_lab (
id serial8 primary key, -- 主鍵
dict int8[], -- N個dim,則有N個元素
score float4, -- 打分
itemid int8 -- 比如商品ID(或其他終端使用者要的ID)
);
-- 不能顆粒化的維度,依舊保留放在tbl_lab表中。
篩選資料原始方法:
select itemid from tbl_lab where dim1=? and dim10=? and dim12=? order by score desc limit 100;
轉換為
set gin_fuzzy_search_limit=2000;
select * from tbl_lab where dict = any (array(
select val from tbl_dict where (dim,info) in ((`1`,?), (`10`,?), (`12`,?))
))
order by score desc limit 100;
3、建立GIN索引
create index idx_tbl_lab_dict on tbl_lab using gin (dict);
4、寫入測試資料
假設有100個維度,每個維度有若干個取值空間的值,總共構成了1000萬個取值。
insert into tbl_dict select (random()*99)::int, generate_series(1,10000000), md5(random()::text);
create or replace function get_val(text) returns int8 as $$
select val from tbl_dict tablesample system (0.1) where dim=$1 limit 1;
$$ language sql strict;
create or replace function get_vals() returns int8[] as $$
select array_agg(get_val(id::text)) from generate_series(0,99) t(id);
$$ language sql strict;
寫入1億標籤記錄
vi test.sql
insert into tbl_lab select get_vals(), random()*1000, random()*100000000 from generate_series(1,100);
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -t 17857
空間佔用
public | tbl_lab | table | postgres | 81 GB |
public | idx_tbl_lab_dict | index | postgres | tbl_lab | 425 GB |
5、篩選資料,同時使用fuzzy match縮小結果集,根據分值排序輸出TOP N
create or replace function get_vals1(int) returns int8[] as $$
select array_agg(get_val(id::text)) from (select generate_series(0,99) order by random() limit $1) t(id);
$$ language sql strict stable;
set gin_fuzzy_search_limit=2000;
select * from tbl_lab where dict @> get_vals1(5)
order by score desc limit 100;
postgres=# set gin_fuzzy_search_limit =1;
SET
Time: 0.213 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
80
(1 row)
Time: 647.802 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
76
(1 row)
Time: 1087.094 ms (00:01.087)
postgres=# set gin_fuzzy_search_limit =10;
SET
Time: 0.174 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
83
(1 row)
Time: 198.663 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
3244
(1 row)
Time: 78.824 ms
postgres=# set gin_fuzzy_search_limit =100;
SET
Time: 0.202 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
4718
(1 row)
Time: 54.961 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
4881
(1 row)
Time: 49.879 ms
postgres=# set gin_fuzzy_search_limit =1000;
SET
Time: 0.176 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
5783
(1 row)
Time: 46.311 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
5784
(1 row)
Time: 45.930 ms
postgres=# set gin_fuzzy_search_limit =5000;
SET
Time: 0.219 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
9156
(1 row)
Time: 48.888 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
9382
(1 row)
Time: 49.479 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
9265
(1 row)
Time: 48.514 ms
postgres=# set gin_fuzzy_search_limit =20000;
SET
Time: 0.231 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
22432
(1 row)
Time: 58.063 ms
postgres=# select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
count
-------
22746
(1 row)
Time: 56.720 ms
5000左右較好,數值太少,訪問的資料反而多。應該是個演算法問題:
postgres=# set gin_fuzzy_search_limit =10;
SET
Time: 0.188 ms
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1702903.64..1702903.65 rows=1 width=8) (actual time=135.104..135.104 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=145266
-> Bitmap Heap Scan on public.tbl_lab (cost=3868.90..1701675.35 rows=491316 width=0) (actual time=135.044..135.082 rows=78 loops=1)
Recheck Cond: (tbl_lab.dict @> `{122562}`::bigint[])
Heap Blocks: exact=78
Buffers: shared hit=145266
-> Bitmap Index Scan on idx_tbl_lab_dict (cost=0.00..3746.07 rows=491316 width=0) (actual time=96.252..96.252 rows=78 loops=1)
Index Cond: (tbl_lab.dict @> `{122562}`::bigint[])
Buffers: shared hit=145248
Planning Time: 0.190 ms
JIT:
Functions: 5
Generation Time: 1.091 ms
Inlining: true
Inlining Time: 5.746 ms
Optimization: true
Optimization Time: 22.590 ms
Emission Time: 10.321 ms
Execution Time: 136.271 ms
(20 rows)
Time: 136.887 ms
postgres=# set gin_fuzzy_search_limit =5000;
SET
Time: 0.222 ms
postgres=# explain (analyze,verbose,timing,costs,buffers) select count(*) from tbl_lab where dict @> array[122562]::int8[] ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1702903.64..1702903.65 rows=1 width=8) (actual time=48.953..48.953 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=187
-> Bitmap Heap Scan on public.tbl_lab (cost=3868.90..1701675.35 rows=491316 width=0) (actual time=45.491..48.031 rows=9290 loops=1)
Recheck Cond: (tbl_lab.dict @> `{122562}`::bigint[])
Heap Blocks: exact=9223
Buffers: shared hit=187
-> Bitmap Index Scan on idx_tbl_lab_dict (cost=0.00..3746.07 rows=491316 width=0) (actual time=5.027..5.027 rows=9290 loops=1)
Index Cond: (tbl_lab.dict @> `{122562}`::bigint[])
Buffers: shared hit=166
Planning Time: 0.165 ms
JIT:
Functions: 5
Generation Time: 1.154 ms
Inlining: true
Inlining Time: 6.152 ms
Optimization: true
Optimization Time: 22.501 ms
Emission Time: 10.273 ms
Execution Time: 50.183 ms
(20 rows)
Time: 50.771 ms
6、效能測試
vacuum tbl_lab;
vi test.sql
select * from tbl_lab where dict @> get_vals1(5)
order by score desc limit 100;
pgbench -M extended -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120
7、更新、刪除維度內容、分值
例子
update tbl_lab set score=? where id=?
update tbl_lab set dict=array_replace(dict,old_val,new_val) where id=?
設計2
使用分段rum索引,在不損傷精度的情況下,提高最大吞吐的任意維度TOP-K輸出。
1、使用rum階梯(類似分段、分割槽)索引
假設score的取值範圍是0到100,我們將score分為100段,每段1個百分點. (實際可按業務的階段來設定步長)
同樣的手法,在以下案例中也使用過。
《PostgreSQL 相似搜尋設計與效能 – 地址、QA、POI等文字 毫秒級相似搜尋實踐》
《PostgreSQL 相似搜尋分散式架構設計與實踐 – dblink非同步呼叫與多機並行(遠端 遊標+記錄 UDF例項)》
如果業務上可以定一個指標,比如說打分在50以下的完全不需要展示,那麼索引甚至可以只需要針對50以上的記錄。
do language plpgsql $$
declare
begin
for i in 1..100 loop
execute format(`create index idx_tbl_lab__%s on tbl_lab using rum (dict rum_anyarray_ops) where score >%s and score <=%s`, i, (i-1), i);
end loop;
end;
$$;
2、使用UDF進行查詢,分階段查詢,從高到低
如果業務上可以定一個指標,比如說打分在50以下的完全不需要展示,那麼查詢LOOP也只需要針對50以上的。
create or replace function get_dict (
int8[], -- 維度值
int -- 返回多少行
) returns setof tbl_lab as $$
declare
r_cnt int := 0;
v_tmp int := 0;
begin
for i in 1..100 loop
return query select * from tbl_lab where dict @> $1 and score >(100-i) and score<=(101-i)
order by score desc limit $2;
GET DIAGNOSTICS v_tmp = ROW_COUNT;
r_cnt := r_cnt + v_tmp ;
if r_cnt >= $2 then
return;
end if;
end loop;
return;
end;
$$ language plpgsql strict;
3、測試效能
postgres=# select score,itemid from get_dict(get_vals1(1),100)
;
score | itemid
---------+----------
99.9529 | 36742578
99.9507 | 69844786
99.941 | 83415934
99.9284 | 46894536
99.9181 | 24389328
...
98.105 | 62905250
98.1028 | 83484134
98.1006 | 67573139
98.0984 | 19020938
98.0983 | 90873124
98.093 | 4732945
98.0885 | 25186764
98.0316 | 97861252
98.0246 | 50682057
(173 rows)
Time: 6.397 ms
postgres=# select score,itemid from get_dict(get_vals1(1),20) ;
score | itemid
---------+----------
99.991 | 27411195
99.9559 | 20090883
99.9478 | 55444281
99.9341 | 70071418
99.9255 | 632316
99.9252 | 70685672
99.8897 | 36828714
99.8714 | 48261720
99.8506 | 92092732
99.811 | 57477121
99.7868 | 52143704
99.7526 | 13161677
99.7496 | 92728450
99.7318 | 73244372
99.6917 | 1948099
99.6274 | 48124431
99.5875 | 76672257
99.5636 | 7682029
99.5593 | 4137987
99.5535 | 93647650
(20 rows)
Time: 1.252 ms
壓測
vi test.sql
select score,itemid from get_dict(get_vals1(1),100);
壓測結果
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120
number of transactions actually processed: 964316
latency average = 6.969 ms
latency stddev = 14.058 ms
tps = 8029.326932 (including connections establishing)
tps = 8029.687576 (excluding connections establishing)
小結
1、gin_fuzzy_search_limit 起到了限流效果(具備一定隨機性,並不精確),同時效能的提升不明顯(5000左右效能較佳),並且測試中發現gin_fuzzy_search_limit設定為很小時,效能並不好。
2、使用rum分段索引,可以實現高效率的過濾,本輪測試1億資料(81 GB資料,81 GB索引),100個維度,按SCORE排序,隨機獲取TOP 100,8029的QPS,得到精準的排序後的資料。
如果需要更好的併發,需要更多的只讀節點來支援。
測試例項為6140 RMB每月的RDS PG 10最高規格。(56核, 480G記憶體, 2T儲存.)
本方案與《PostgreSQL ADHoc(任意欄位組合)查詢 與 字典化 (rum索引加速) – 實踐與方案1》 不同之處,僅僅在於本例輸出的結果需要按SCORE排序,所以我們使用分段索引,以及UDF輸出,巧妙的提升了整體的處理吞吐。
同樣的分段加速手法,在以下案例中也使用過。
《PostgreSQL 相似搜尋設計與效能 – 地址、QA、POI等文字 毫秒級相似搜尋實踐》
《PostgreSQL 相似搜尋分散式架構設計與實踐 – dblink非同步呼叫與多機並行(遠端 遊標+記錄 UDF例項)》
參考
《PostgreSQL ADHoc(任意欄位組合)查詢 與 字典化 (rum索引加速) – 實踐與方案1》
《HTAP資料庫 PostgreSQL 場景與效能測試之 20 – (OLAP) 使用者畫像圈人場景 – 多個欄位任意組合條件篩選與透視》
《時間、空間、物件多維屬性 海量資料任意多維 高效檢索 – 阿里雲RDS PostgreSQL最佳實踐》
《多欄位,任意組合條件查詢(無需建模) – 毫秒級實時圈人 最佳實踐》
《寶劍贈英雄 – 任意組合欄位等效查詢, 探探PostgreSQL多列展開式B樹 (GIN)》
《[未完待續] PostgreSQL 全文檢索 大結果集優化 – fuzzy match》
相關文章
- PostgreSQL任意列組合條件行數估算實踐-取樣估算SQL
- pandas 分組合並欄位(qbit)
- 【Django】組合條件的搜尋功能實現Django
- 巧用欄位對映實現指定欄位的搜尋
- Thinkphp TPM 職位搜尋和職位推薦PHP
- C#實現任意源組播與特定源組播C#
- Redis 實戰 —— 10. 實現內容搜尋、定向廣告和職位搜尋Redis
- 基於PostgreSQL實時干預搜尋排序實踐SQL排序
- 組合語言下--如何防止除法溢位組合語言
- 十進位制轉換任意進位制--鏈棧實現
- 遞迴演算法實踐---實現排列組合遞迴演算法
- 51_初識搜尋引擎_上機動手實戰多搜尋條件組合查詢
- 專案實戰:彈出廣告任意頁面展示
- 適合不同人群 各價位實用一體電腦推薦
- C++實現任意進位制的相互轉換C++
- SqlServer根據特定欄位分組後,對需要欄位進行分組拼接SQLServer
- 實現 使用者名稱或郵箱等 任意一個欄位均可登入
- 手把手教你如何組合線上學習:實時反饋玩轉組合優化優化
- 組合語言 2組合語言
- JavaCompositeEntityPattern(組合實體模式)Java模式
- 任意列搜尋之列儲存優化優化
- [Elasticsearch] 多欄位搜尋 (二) - 最佳欄位查詢及其調優(轉)Elasticsearch
- 組合計數思維題
- PostgreSQL生成任意基數數獨-2SQL
- 輸出一個百位數,1-5之間所有組合型別,數字不重複型別
- 組合
- Redux實現組合計數器Redux
- LNMP組合詳解與實戰LNMP
- 如何實現條件組合元件元件
- 【組合數學】組合數學簡介 ( 組合思想 2 : 數學歸納法 | 數學歸納法推廣 | 多重歸納思想 )
- 10個實用的Win10組合快捷鍵推薦 提升電腦使用效率Win10
- 【力扣】組合總數(另一種整數溢位)力扣
- MySQL · 最佳實踐 · 如何索引 JSON 欄位 (阿里雲 RDS-資料庫核心組 )MySql索引JSON阿里資料庫
- mysql的中文全文搜尋實踐(二)薦MySql
- IT組合和專案組合管理(轉)
- 【數學】組合數學 - 排列組合
- list集合根據某欄位分組
- [053][組合語言]adc 帶進位加法 sbb帶進位減法指令組合語言