1.背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
- 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker image:
2.业务场景1 介绍: 内容检索及匹配度排序
在内容检索(例如网站的文章检索、搜索引擎的内容搜索)的场景中, 除了内容本身的匹配与否作为判断条件, 还有一个重要的需求是按匹配度、内容的新鲜度等进行排序返回, 匹配度越高|新鲜度|权重越高的优先返回.
2.1实现和对照
场景1: 博客类网站, 设定了一些内容标签, 每篇文章在发表时需要给文章设定标签, 一篇文章可能会设定若干个标签. 同时文章发表时间越大表示越新鲜. 用户在浏览时, 选择某个标签, 按新鲜度顺序返回这个标签下的文章.
场景2: 还是上面的场景, 但是每个文章对应的每个标签都设置一个权重, 表示这篇文章在这个标签上的匹配程度. 用户在浏览时, 选择某个标签, 按匹配度以及新鲜度顺序返回这个标签下的文章.
场景3: 博客类网站, 按内容检索匹配的文章, 并按匹配度(rank)顺序返回文章.
2.1.1传统方法 设计和实验
传统数据库没有数组类型, 不支持一对多的数据结构存储.
传统数据库没有文本向量类型, 不支持文本向量的全文检索和文本向量的匹配度排序.
场景1
1、创建标签表
drop table if exists tbl_art_tag; drop table if exists tbl_tag; create table tbl_tag ( id int primary key, -- 标签ID info text -- 标签内容 );
2、写入1000个预设标签
insert into tbl_tag select generate_series(1,1000), md5(random()::text);
3、创建文章表, 记录每篇文章的每个标签.
drop table if exists tbl_art_tag; create unlogged table tbl_art_tag ( art_id int, -- 文章ID tid int references tbl_tag(id), -- 标签ID ts timestamp -- 文章时间戳 );
4、创建序列
drop sequence if exists seq; create sequence seq INCREMENT by 1;
5、编写pgbench脚本, 随机生成每篇文章的标签数量(1到1000个), 生成随机标签ID并写入到文章表中.
vi t1.sql \set tags random_gaussian(1, 1000, 2.5) select nextval('seq') as art_id \gset insert into tbl_art_tag select :art_id, ceil(random()*1000), now() from generate_series(1, :tags);
6、生成约1000万条数据.
pgbench -M prepared -n -r -P 1 -f ./t1.sql -c 10 -j 10 -t 2000
7、创建标签和时间戳字段索引
create index on tbl_art_tag (tid,ts);
8、用户在浏览时, 选择某个标签, 按新鲜度顺序返回这个标签下的top 100文章.
postgres=# explain analyze select art_id,ts from tbl_art_tag where tid=500 order by ts desc limit 100; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..103.31 rows=100 width=12) (actual time=0.134..0.597 rows=100 loops=1) -> Index Scan using tbl_art_tag_tid_ts_idx on tbl_art_tag (cost=0.43..10238.91 rows=9952 width=12) (actual time=0.131..0.575 rows=100 loops=1) Index Cond: (tid = 500) Planning Time: 0.441 ms Execution Time: 0.689 ms (5 rows)
9、性能压测
vi t2.sql \set tid random(1,1000) select art_id,ts from tbl_art_tag where tid=:tid order by ts desc limit 100;
pgbench -M prepared -n -r -P 1 -f ./t2.sql -c 10 -j 10 -T 120 transaction type: ./t2.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 9185044 latency average = 0.131 ms latency stddev = 0.058 ms initial connection time = 21.476 ms tps = 76555.526245 (without initial connection time) statement latencies in milliseconds: 0.000 \set tid random(1,1000) 0.130 select art_id,ts from tbl_art_tag where tid=:tid order by ts desc limit 100;
10、搜索多个标签的文章, 并按时间返回.
postgres=# explain analyze select art_id,ts from tbl_art_tag where tid in ( 490,500 ) order by ts desc limit 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=18800.31..18800.56 rows=100 width=12) (actual time=256.937..256.948 rows=100 loops=1) -> Sort (cost=18800.31..18850.07 rows=19904 width=12) (actual time=256.928..256.932 rows=100 loops=1) Sort Key: ts Sort Method: top-N heapsort Memory: 29kB -> Bitmap Heap Scan on tbl_art_tag (cost=229.93..18039.59 rows=19904 width=12) (actual time=15.951..253.327 rows=20040 loops=1) Recheck Cond: (tid = ANY ('{490,500}'::integer[])) Heap Blocks: exact=16804 -> Bitmap Index Scan on tbl_art_tag_tid_ts_idx (cost=0.00..224.95 rows=19904 width=0) (actual time=12.534..12.534 rows=20040 loops=1) Index Cond: (tid = ANY ('{490,500}'::integer[])) Planning Time: 2.119 ms Execution Time: 257.157 ms (11 rows)
11、性能测试
vi t22.sql \set tid1 random(1,1000) \set tid2 random(1,1000) select art_id,ts from tbl_art_tag where tid in(:tid1,:tid2) order by ts desc limit 100;
pgbench -M prepared -n -r -P 1 -f ./t22.sql -c 10 -j 10 -T 120 transaction type: ./t22.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 13281 latency average = 90.357 ms latency stddev = 10.483 ms initial connection time = 22.151 ms tps = 110.661414 (without initial connection time) statement latencies in milliseconds: 0.001 \set tid1 random(1,1000) 0.000 \set tid2 random(1,1000) 90.621 select art_id,ts from tbl_art_tag where tid in(:tid1,:tid2) order by ts desc limit 100;
12、思考一下, 如果要搜索在同一篇文章中有多个符合条件的标签的文章, 怎么写SQL?
postgres=# explain analyze select art_id,max(ts) as ts,count(*) from tbl_art_tag where tid in ( 490,500 ) group by 1 having count(*)>=2 order by ts desc limit 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=18477.15..18477.40 rows=100 width=20) (actual time=153.256..153.263 rows=100 loops=1) -> Sort (cost=18477.15..18486.66 rows=3807 width=20) (actual time=153.244..153.248 rows=100 loops=1) Sort Key: (max(ts)) DESC Sort Method: top-N heapsort Memory: 38kB -> HashAggregate (cost=18188.87..18331.65 rows=3807 width=20) (actual time=152.051..152.890 rows=5308 loops=1) Group Key: art_id Filter: (count(*) >= 2) Batches: 1 Memory Usage: 1681kB Rows Removed by Filter: 6786 -> Bitmap Heap Scan on tbl_art_tag (cost=229.93..18039.59 rows=19904 width=12) (actual time=13.819..143.743 rows=20040 loops=1) Recheck Cond: (tid = ANY ('{490,500}'::integer[])) Heap Blocks: exact=16804 -> Bitmap Index Scan on tbl_art_tag_tid_ts_idx (cost=0.00..224.95 rows=19904 width=0) (actual time=10.269..10.269 rows=20040 loops=1) Index Cond: (tid = ANY ('{490,500}'::integer[])) Planning Time: 0.346 ms Execution Time: 153.743 ms (16 rows)
13、性能测试
pgbench -M prepared -n -r -P 1 -f ./t222.sql -c 10 -j 10 -T 120 transaction type: ./t222.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 11918 latency average = 100.702 ms latency stddev = 12.866 ms initial connection time = 21.952 ms tps = 99.297985 (without initial connection time) statement latencies in milliseconds: 0.001 \set tid1 random(1,1000) 0.000 \set tid2 random(1,1000) 100.711 select art_id,max(ts) as ts,count(*) from tbl_art_tag where tid in ( :tid1, :tid2 ) group by 1 having count(*)>=2 order by ts d
场景2
留作业, 请同学自己设计完成.
场景3
不支持
2.1.2PolarDB|PG新方法1 设计和实验
场景1
使用传统方法场景1的数据基础, 将一篇文章的所有标签转换为数组存储.
1、创建rum插件, 支持rum索引接口. rum是在gin基础上增强的一种索引, 既有倒排, 又在每条tuple item指针后面附加了额外的信息(例如多值类型以外的其他字段值), 可用于额外的排序动作.
create extension rum;
2、创建文章表, 一对多表示文章和标签的关系.
drop table if exists tbl_art_tag1; create unlogged table tbl_art_tag1 ( art_id int primary key, tid int[], ts timestamp );
3、使用传统方法场景1的数据基础, 将一篇文章的所有标签转换为数组存储.
insert into tbl_art_tag1 select art_id, array_agg(tid), max(ts) from tbl_art_tag group by 1;
4、创建rum索引, 标签数组+时间戳.
CREATE INDEX ON tbl_art_tag1 USING rum (tid rum_anyarray_addon_ops, ts) WITH (attach = 'ts', to = 'tid');
5、用户在浏览时, 选择某个标签, 按新鲜度顺序返回这个标签下的top 100文章.
postgres=# explain analyze select art_id,ts from tbl_art_tag1 where tid @> array[500] order by ts <=> '2099-01-01' limit 100; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=4.40..32.74 rows=100 width=20) (actual time=5.699..6.239 rows=100 loops=1) -> Index Scan using tbl_art_tag1_tid_ts_idx on tbl_art_tag1 (cost=4.40..2184.46 rows=7693 width=20) (actual time=5.695..6.193 rows=100 loops=1) Index Cond: (tid @> '{500}'::integer[]) Order By: (ts <=> '2099-01-01 00:00:00'::timestamp without time zone) Planning Time: 0.378 ms Execution Time: 6.348 ms (6 rows)
6、性能压测
相比于传统方法, 性能从76555下降到了2845 tps.
原因是rum索引内的tuple item并不是按附加信息值(即这里的ts)顺序存储的, 应该多了一些sort开销.
而传统btree索引并没有额外的sort开销.
vi t3.sql \set tid random(1,1000) select art_id from tbl_art_tag1 where tid @> (array[:tid])::int[] order by ts <=> '2099-01-01' limit 100;
pgbench -M prepared -n -r -P 1 -f ./t3.sql -c 10 -j 10 -T 120 transaction type: ./t3.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 341387 latency average = 3.514 ms latency stddev = 1.119 ms initial connection time = 19.174 ms tps = 2845.241086 (without initial connection time) statement latencies in milliseconds: 0.000 \set tid random(1,1000) 3.515 select art_id from tbl_art_tag1 where tid @> (array[:tid])::int[] order by ts <=> '2099-01-01' limit 100;
7、传统方法和rum的标签表、索引空间占用对比:
rum的标签表只有传统方法十分之一左右, 索引则只有传统方法的一半不到.
postgres=# \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+--------------+-------+----------+-------------+---------------+--------+------------- public | tbl_art_tag | table | postgres | unlogged | heap | 424 MB | public | tbl_art_tag1 | table | postgres | unlogged | heap | 43 MB | public | tbl_tag | table | postgres | permanent | heap | 104 kB | (3 rows) postgres=# \di+ List of relations Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description --------+-------------------------+-------+----------+--------------+-------------+---------------+--------+------------- public | tbl_art_tag1_pkey | index | postgres | tbl_art_tag1 | unlogged | btree | 592 kB | public | tbl_art_tag1_tid_ts_idx | index | postgres | tbl_art_tag1 | unlogged | rum | 101 MB | public | tbl_art_tag_tid_ts_idx | index | postgres | tbl_art_tag | unlogged | btree | 267 MB | public | tbl_tag_pkey | index | postgres | tbl_tag | permanent | btree | 40 kB | (4 rows)
8、搜索多个标签的文章, 并按时间返回.
postgres=# explain analyze select art_id,ts from tbl_art_tag1 where tid && (array[490,500])::int[] order by ts desc limit 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=2575.92..2576.17 rows=100 width=12) (actual time=21.177..21.194 rows=100 loops=1) -> Sort (cost=2575.92..2606.82 rows=12359 width=12) (actual time=21.173..21.181 rows=100 loops=1) Sort Key: ts DESC Sort Method: top-N heapsort Memory: 32kB -> Bitmap Heap Scan on tbl_art_tag1 (cost=110.08..2103.57 rows=12359 width=12) (actual time=6.189..19.271 rows=12094 loops=1) Recheck Cond: (tid && '{490,500}'::integer[]) Heap Blocks: exact=1835 -> Bitmap Index Scan on tbl_art_tag1_tid_ts_idx (cost=0.00..106.99 rows=12359 width=0) (actual time=5.727..5.727 rows=12094 loops=1) Index Cond: (tid && '{490,500}'::integer[]) Planning Time: 0.550 ms Execution Time: 21.315 ms (11 rows)
11、性能测试
vi t33.sql \set tid1 random(1,1000) \set tid2 random(1,1000) select art_id,ts from tbl_art_tag1 where tid && (array[:tid1 , :tid2])::int[] order by ts desc limit 100;
pgbench -M prepared -n -r -P 1 -f ./t33.sql -c 10 -j 10 -T 120 transaction type: ./t33.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 170336 latency average = 7.044 ms latency stddev = 2.417 ms initial connection time = 24.006 ms tps = 1419.594182 (without initial connection time) statement latencies in milliseconds: 0.000 \set tid1 random(1,1000) 0.000 \set tid2 random(1,1000) 7.045 select art_id,ts from tbl_art_tag1 where tid && (array[:tid1 , :tid2])::int[] order by ts desc limit 100;
12、思考一下, 如果要搜索在同一篇文章中有多个符合条件的标签的文章, 怎么写SQL?
postgres=# explain analyze select art_id,ts from tbl_art_tag1 where tid @> (array[490,500])::int[] order by ts desc limit 100; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1818.22..1818.47 rows=100 width=12) (actual time=10.007..10.026 rows=100 loops=1) -> Sort (cost=1818.22..1825.51 rows=2917 width=12) (actual time=10.005..10.014 rows=100 loops=1) Sort Key: ts DESC Sort Method: top-N heapsort Memory: 34kB -> Bitmap Heap Scan on tbl_art_tag1 (cost=33.61..1706.73 rows=2917 width=12) (actual time=3.108..9.403 rows=3182 loops=1) Recheck Cond: (tid @> '{490,500}'::integer[]) Heap Blocks: exact=1474 -> Bitmap Index Scan on tbl_art_tag1_tid_ts_idx (cost=0.00..32.88 rows=2917 width=0) (actual time=2.867..2.869 rows=3182 loops=1) Index Cond: (tid @> '{490,500}'::integer[]) Planning Time: 0.204 ms Execution Time: 10.186 ms (11 rows)
13、性能测试
vi t333.sql \set tid1 random(1,1000) \set tid2 random(1,1000) select art_id,ts from tbl_art_tag1 where tid @> (array[:tid1, :tid2])::int[] order by ts desc limit 100;
pgbench -M prepared -n -r -P 1 -f ./t333.sql -c 10 -j 10 -T 120 transaction type: ./t333.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 281008 latency average = 4.269 ms latency stddev = 1.339 ms initial connection time = 23.848 ms tps = 2342.110257 (without initial connection time) statement latencies in milliseconds: 0.000 \set tid1 random(1,1000) 0.000 \set tid2 random(1,1000) 4.269 select art_id,ts from tbl_art_tag1 where tid @> (array[:tid1, :tid2])::int[] order by ts desc limit 100;
场景2
留作业, 请同学自己设计完成.
场景3
1、创建一张表用来存储原始文本、文本向量.
CREATE TABLE test_rum(t text, a tsvector);
2、写个触发器, 自动将原始文本转换为文本向量. 插入数据时不用管文本向量是怎么生成的.
CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON test_rum FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger('a', 'pg_catalog.english', 't');
3、插入测试文本.
INSERT INTO test_rum(t) VALUES ('The situation is most beautiful'); INSERT INTO test_rum(t) VALUES ('It is a beautiful'); INSERT INTO test_rum(t) VALUES ('It looks like a beautiful place');
4、查询数据
postgres=# select * from test_rum; t | a ---------------------------------+---------------------------------------- The situation is most beautiful | 'beauti':5 'situat':2 It is a beautiful | 'beauti':4 It looks like a beautiful place | 'beauti':5 'like':3 'look':2 'place':6 (3 rows)
5、创建rum索引
CREATE INDEX rumidx ON test_rum USING rum (a rum_tsvector_ops);
6、根据文本搜索需求, 搜索文本, 同时按匹配度(rank)顺序返回
postgres=# SELECT t, a <=> to_tsquery('english', 'beautiful | place') AS rank FROM test_rum WHERE a @@ to_tsquery('english', 'beautiful | place') ORDER BY a <=> to_tsquery('english', 'beautiful | place'); t | rank ---------------------------------+---------- It looks like a beautiful place | 8.22467 The situation is most beautiful | 16.44934 It is a beautiful | 16.44934 (3 rows)
通过执行计划可以看到, rum索引已经生效, 搜索和排序都是用了rum索引.
postgres=# explain SELECT t, a <=> to_tsquery('english', 'beautiful | place') AS rank FROM test_rum WHERE a @@ to_tsquery('english', 'beautiful | place') ORDER BY a <=> to_tsquery('english', 'beautiful | place'); QUERY PLAN ------------------------------------------------------------------------ Index Scan using rumidx on test_rum (cost=2.20..4.41 rows=1 width=36) Index Cond: (a @@ '''beauti'' | ''place'''::tsquery) Order By: (a <=> '''beauti'' | ''place'''::tsquery) (3 rows)
7、创建一个函数自动生成字符串
CREATE OR REPLACE FUNCTION generate_random_string(word_count INTEGER, word_length INTEGER) RETURNS TEXT AS $$ DECLARE alphabet TEXT[] := array['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z']; random_string TEXT := ''; BEGIN FOR i IN 1..word_count LOOP DECLARE random_word TEXT := ''; BEGIN FOR j IN 1..word_length LOOP random_word := random_word || alphabet[ceil(random() * array_length(alphabet, 1))]; END LOOP; random_string := random_string || random_word || ' '; END; END LOOP; RETURN TRIM(random_string); END; $$ LANGUAGE plpgsql;
8、插入100万条字符串数据
insert into test_rum(t) select generate_random_string(100,5) from generate_series(1,1000000); postgres=# select * from test_rum limit 10; -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | The situation is most beautiful a | 'beauti':5 'situat':2 -[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | It is a beautiful a | 'beauti':4 -[ RECORD 3 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | It looks like a beautiful place a | 'beauti':5 'like':3 'look':2 'place':6 -[ RECORD 4 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | sbrga khbvj twxim sktyf zmrga lwqmz rqtiv mrugi tlynt kchdh psuni wdxvi ffcxw qvyrk umnzl ywjpe dkdiv wmwuh xrigo pjylk yoqjq mizsr uiolh upqex pdgit vngxj ztopr yjgbc fclci hldvo xundu mayws ltgct xkvzx caibh dwytd sbfij cthcx mbcgk dgyju ofgmx lxntg onyqh hjtsc crxhc ktrcd xxppn ouwnd rqbeq azdqy tgoje rrlrm fufsy wrjuh vzpud lqbih zxbhl bvtwy fixnv vojjx onpdb sphjc pufyc zmcbw etfbb bgqnt ykaug xtnhj hweqs yxwuc gqlht echao aerzi blvik bfuki pjmsl fviuk gfooa wkpsw pbylk iyckm bompq gsjyo wlgvf vuoye mjlvv qxkxq nkthg fsaui ynmaj nngak boxvs lsqbo sogdf iccef ouuch mmtoe awwoy llenr tzlpl a | 'aerzi':73 'awwoy':98 'azdqi':50 'bfuki':75 'bgqnt':66 'blvik':74 'bompq':82 'boxv':92 'bvtwi':58 'caibh':35 'crxhc':45 'cthcx':38 'dgyju':40 'dkdiv':17 'dwytd':36 'echao':72 'etfbb':65 'fclci':29 'ffcxw':13 'fixnv':59 'fsaui':89 'fufsi':53 'fviuk':77 'gfooa':78 'gqlht':71 'gsjyo':83 'hjtsc':44 'hldvo':30 'hweq':69 'iccef':95 'iyckm':81 'kchdh':10 'khbvj':2 'ktrcd':46 'llenr':99 'lqbih':56 'lsqbo':93 'ltgct':33 'lwqmz':6 'lxntg':42 'mayw':32 'mbcgk':39 'mizsr':22 'mjlvv':86 'mmtoe':97 'mrugi':8 'nkthg':88 'nngak':91 'ofgmx':41 'onpdb':61 'onyqh':43 'ouuch':96 'ouwnd':48 'pbylk':80 'pdgit':25 'pjmsl':76 'pjylk':20 'psuni':11 'pufyc':63 'qvyrk':14 'qxkxq':87 'rqbeq':49 'rqtiv':7 'rrlrm':52 'sbfij':37 'sbrga':1 'sktyf':4 'sogdf':94 'sphjc':62 'tgoje':51 'tlynt':9 'twxim':3 'tzlpl':100 'uiolh':23 'umnzl':15 'upqex':24 'vngxj':26 'vojjx':60 'vuoy':85 'vzpud':55 'wdxvi':12 'wkpsw':79 'wlgvf':84 'wmwuh':18 'wrjuh':54 'xkvzx':34 'xrigo':19 'xtnhj':68 'xundu':31 'xxppn':47 'yjgbc':28 'ykaug':67 'ynmaj':90 'yoqjq':21 'ywjpe':16 'yxwuc':70 'zmcbw':64 'zmrga':5 'ztopr':27 'zxbhl':57 -[ RECORD 5 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | cjrtm yxkgi umhdb ovtvi ranbr xqgqn paxaa ulpnf gfyir vwzul kzldj edngz ktyzj nwoyb wfzas szmhd ljbcg vakcu amfbo mclwb ygiev nmbde oubyd dvzmd djdyn tmjot frxcm lemdl ufbio ljkcx uanqj dvmrn prbfv rfigm vmvxr qjyyw eucix mvotm zjpau phkyi huqyn vsyro uqbth goqsk trcwb pyzcs lusgw zsihz rdkiw fivxi xjsep iulju fpyhi lsxcs gdqoi xxvsc panma kxbnq bovrn kaucz dyokk aswtt mouqj uruwe awudz dzjjs ukitb vdvsq pisex nmyrx llzmu sldyz fakfg zyqjy fbqkt kyuht pnmub eozyd opulx xjamp fdcpm tyxwb znkra aqjsj qpfev sbqff oiblm kqkzj rvsmi xomei saazz fnrfw zreai cnzey ewslo agnst wozgd odido eewga klplv a | 'agnst':96 'amfbo':19 'aqjsj':84 'aswtt':62 'awudz':65 'bovrn':59 'cjrtm':1 'cnzey':94 'djdyn':25 'dvmrn':32 'dvzmd':24 'dyokk':61 'dzjjs':66 'edngz':12 'eewga':99 'eozyd':78 'eucix':37 'ewslo':95 'fakfg':73 'fbqkt':75 'fdcpm':81 'fivxi':50 'fnrfw':92 'fpyhi':53 'frxcm':27 'gdqoi':55 'gfyir':9 'goqsk':44 'huqyn':41 'iulju':52 'kaucz':60 'klplv':100 'kqkzj':88 'ktyzj':13 'kxbnq':58 'kyuht':76 'kzldj':11 'lemdl':28 'ljbcg':17 'ljkcx':30 'llzmu':71 'lsxcs':54 'lusgw':47 'mclwb':20 'mouqj':63 'mvotm':38 'nmbde':22 'nmyrx':70 'nwoyb':14 'odido':98 'oiblm':87 'opulx':79 'oubyd':23 'ovtvi':4 'panma':57 'paxaa':7 'phkyi':40 'pisex':69 'pnmub':77 'prbfv':33 'pyzc':46 'qjyyw':36 'qpfev':85 'ranbr':5 'rdkiw':49 'rfigm':34 'rvsmi':89 'saazz':91 'sbqff':86 'sldyz':72 'szmhd':16 'tmjot':26 'trcwb':45 'tyxwb':82 'uanqj':31 'ufbio':29 'ukitb':67 'ulpnf':8 'umhdb':3 'uqbth':43 'uruw':64 'vakcu':18 'vdvsq':68 'vmvxr':35 'vsyro':42 'vwzul':10 'wfzas':15 'wozgd':97 'xjamp':80 'xjsep':51 'xomei':90 'xqgqn':6 'xxvsc':56 'ygiev':21 'yxkgi':2 'zjpau':39 'znkra':83 'zreai':93 'zsihz':48 'zyqji':74 -[ RECORD 6 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | gptii hvrxw czbfs ucwek kveek gcwms uueli ofttk svmty waxay gzdah lnsds nydew oqqrp nniey jzexb abvye aqafc gvslu pexdh wqbla cqlrt nybot bbgep gyinl gfwok ygumu qmzml bgdcm ruslq jetgm jtani lvphh yfequ hzkkp inyjt xkatw uutyy dfeqk bwvto czajw kkfpm ulvgs gidly wdbok shepp wzegi zgfek eotml wrkar lfung hlfil otyao jimnc ypejg atorh hcxie uhsoe bsmvf ufenk rkspg yvcfi hfocq angmx qbzyh smffj efldt lulkt wvbtr qleck bypmd oeobi xxuwd bhsdi wnfge zddnm sjyaf oalef zgluq tepyk vavxn nzpqh adxqm yuker wbddf nzcde omxnc egsyv begad zyazn lkulg dvcae niqyq kytoy oqxnf usbtc rcvlc rtdyp pmofi fjyjh a | 'abvy':17 'adxqm':83 'angmx':64 'aqafc':18 'atorh':56 'bbgep':24 'begad':89 'bgdcm':29 'bhsdi':74 'bsmvf':59 'bwvto':40 'bypmd':71 'cqlrt':22 'czajw':41 'czbfs':3 'dfeqk':39 'dvcae':92 'efldt':67 'egsyv':88 'eotml':49 'fjyjh':100 'gcwms':6 'gfwok':26 'gid':44 'gptii':1 'gvslu':19 'gyinl':25 'gzdah':11 'hcxie':57 'hfocq':63 'hlfil':52 'hvrxw':2 'hzkkp':35 'inyjt':36 'jetgm':31 'jimnc':54 'jtani':32 'jzexb':16 'kkfpm':42 'kveek':5 'kytoy':94 'lfung':51 'lkulg':91 'lnsds':12 'lulkt':68 'lvphh':33 'niqyq':93 'nniey':15 'nybot':23 'nydew':13 'nzcde':86 'nzpqh':82 'oalef':78 'oeobi':72 'ofttk':8 'omxnc':87 'oqqrp':14 'oqxnf':95 'otyao':53 'pexdh':20 'pmofi':99 'qbzyh':65 'qleck':70 'qmzml':28 'rcvlc':97 'rkspg':61 'rtdyp':98 'ruslq':30 'shepp':46 'sjyaf':77 'smffj':66 'svmti':9 'tepyk':80 'ucwek':4 'ufenk':60 'uhso':58 'ulvg':43 'usbtc':96 'uueli':7 'uutyy':38 'vavxn':81 'waxay':10 'wbddf':85 'wdbok':45 'wnfge':75 'wqbla':21 'wrkar':50 'wvbtr':69 'wzegi':47 'xkatw':37 'xxuwd':73 'yfequ':34 'ygumu':27 'ypejg':55 'yuker':84 'yvcfi':62 'zddnm':76 'zgfek':48 'zgluq':79 'zyazn':90 -[ RECORD 7 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | pukxz cjzrt wluxj eqazl zvnrh lobbu jzvst vcpmw xoarq lbjhq vviss qfsnt jquem rsqes hgbza fvebq wftpg qdziu lejai sacek uhezq hbmxu wofun nrlaw soplm lcuup vczlu oedxf euthb hssfa itlml styog nnebt htoks goekj pkzlj mfjin yjtlt nqsck xemyn svhzp lnagi tpzvh nkarv aiadx qxrzs obaau jgnht yrljv dmshm olmyl zlnai adrjj qmkkw kpfpl mldrw ttmlk aqwhd bmuzl bechv bupch pzhap cbjqn ggmos kmmlh qujxv glusq ebwxw plhnv tdgdx pudbr mnccq zopgk nghke waokc qposi ddjqz ftkof yhfjw hevgc eyxec szgms woahp sqovp iuyyt wmnaq jkniq skbak ohebm cfzfh oxsqz dzhup ypqye naxaw thwqy cqhgo wlxmm xadir pmvpq ephrb a | 'adrjj':53 'aiadx':45 'aqwhd':58 'bechv':60 'bmuzl':59 'bupch':61 'cbjqn':63 'cfzfh':90 'cjzrt':2 'cqhgo':96 'ddjqz':77 'dmshm':50 'dzhup':92 'ebwxw':68 'ephrb':100 'eqazl':4 'euthb':29 'eyxec':81 'ftkof':78 'fvebq':16 'ggmos':64 'glusq':67 'goekj':35 'hbmxu':22 'hevgc':80 'hgbza':15 'hssfa':30 'htok':34 'itlml':31 'iuyyt':85 'jgnht':48 'jkniq':87 'jquem':13 'jzvst':7 'kmmlh':65 'kpfpl':55 'lbjhq':10 'lcuup':26 'lejai':19 'lnagi':42 'lobbu':6 'mfjin':37 'mldrw':56 'mnccq':72 'naxaw':94 'nghke':74 'nkarv':44 'nnebt':33 'nqsck':39 'nrlaw':24 'obaau':47 'oedxf':28 'ohebm':89 'olmyl':51 'oxsqz':91 'pkzlj':36 'plhnv':69 'pmvpq':99 'pudbr':71 'pukxz':1 'pzhap':62 'qdziu':18 'qfsnt':12 'qmkkw':54 'qposi':76 'qujxv':66 'qxrzs':46 'rsqes':14 'sacek':20 'skbak':88 'soplm':25 'sqovp':84 'styog':32 'svhzp':41 'szgms':82 'tdgdx':70 'thwqi':95 'tpzvh':43 'ttmlk':57 'uhezq':21 'vcpmw':8 'vczlu':27 'vviss':11 'waokc':75 'wftpg':17 'wluxj':3 'wlxmm':97 'wmnaq':86 'woahp':83 'wofun':23 'xadir':98 'xemyn':40 'xoarq':9 'yhfjw':79 'yjtlt':38 'ypqye':93 'yrljv':49 'zlnai':52 'zopgk':73 'zvnrh':5 -[ RECORD 8 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | ibutz qmbmp sfjqk gkcpb gwvwu ytoum ueuml xepij pxnvm zsxbp jasvu mmzbj uzoop zzhnk hsley thtuf vmudf kaygi atapb yruei vgwnx ldfkf xbivd wudtk emwqf wpdte sxjso vhumu khign kuyky tsvlz gsmpa ggerz rzsyi aeivx frumg ahkdm kwugm zamtl uzuxf pjenn pzcnu jjtno uljug ggmjl xrbvv ysdwi mssmm exjpu pmzro hsrpe kpfkm lzfta vnijf twpmz ypzux usmxi vcfrg jfilh jeuif vwjmz opmpe ijgjf velyx fnqxe xkahq qlacq bmnwm lczfq yvzsi wvxic omexp vojpg bburj jjmjs essbp edqjf nprnw ynksp cucrd udexz fizco jikmi flicv kekoq rmgde iglnc qnjfp tvigf fxsbg sropz yvkdy ipmwa slkft npkxu xelep oxzna wrdts xuybl vwqrx a | 'aeivx':35 'ahkdm':37 'atapb':19 'bburj':74 'bmnwm':68 'cucrd':80 'edqjf':77 'emwqf':25 'essbp':76 'exjpu':49 'fizco':82 'flicv':84 'fnqxe':65 'frumg':36 'fxsbg':90 'ggerz':33 'ggmjl':45 'gkcpb':4 'gsmpa':32 'gwvwu':5 'hsley':15 'hsrpe':51 'ibutz':1 'iglnc':87 'ijgjf':63 'ipmwa':93 'jasvu':11 'jeuif':60 'jfilh':59 'jikmi':83 'jjmjs':75 'jjtno':43 'kaygi':18 'kekoq':85 'khign':29 'kpfkm':52 'kuyki':30 'kwugm':38 'lczfq':69 'ldfkf':22 'lzfta':53 'mmzbj':12 'mssmm':48 'npkxu':95 'nprnw':78 'omexp':72 'opmp':62 'oxzna':97 'pjenn':41 'pmzro':50 'pxnvm':9 'pzcnu':42 'qlacq':67 'qmbmp':2 'qnjfp':88 'rmgde':86 'rzsyi':34 'sfjqk':3 'slkft':94 'sropz':91 'sxjso':27 'thtuf':16 'tsvlz':31 'tvigf':89 'twpmz':55 'udexz':81 'ueuml':7 'uljug':44 'usmxi':57 'uzoop':13 'uzuxf':40 'vcfrg':58 'velyx':64 'vgwnx':21 'vhumu':28 'vmudf':17 'vnijf':54 'vojpg':73 'vwjmz':61 'vwqrx':100 'wpdte':26 'wrdts':98 'wudtk':24 'wvxic':71 'xbivd':23 'xelep':96 'xepij':8 'xkahq':66 'xrbvv':46 'xuybl':99 'ynksp':79 'ypzux':56 'yruei':20 'ysdwi':47 'ytoum':6 'yvkdi':92 'yvzsi':70 'zamtl':39 'zsxbp':10 'zzhnk':14 -[ RECORD 9 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- t | zesxj jwmiy lwdhs mniqq pjotx gzkgg bmgpx emand souux lhqxg hdpry fxrgt iykze wxyfo drmgf sadxf axfwe yzqbl pzfsg rwgkv pcten koiin ncsjo cuspk hdstv irswe yhoer bkgyp ryndt tspgx tlraa cpmew rtawa nywad jpnjg dravu vyumk idbah kbegj hvzmw xwaob yccgr edwda sxmoa knfnj tggmh mgdbm igbku imoxi wnarq fockx ufehp fgohg avxca ntbzj muhis igirz hxzns ynlff vzxdm owanl nabkn opttw zkxyx eavcw zvbfk irhzd jjpyo hlgqy nruch ovikn qzyef mpkyr uaebu njycu biqyj zjsju kdtsp ibtls mpfly rlgva pivqg petpc rrmny edgya kvhqi lfvkl aerux drqeg nqpxe izcuq psqmo fpakt pfaeq nltpr hsykx jeiul bkudj yftnb zqybi a | 'aerux':88 'avxca':54 'axfw':17 'biqyj':76 'bkgyp':28 'bkudj':98 'bmgpx':7 'cpmew':32 'cuspk':24 'dravu':36 'drmgf':15 'drqeg':89 'eavcw':65 'edgya':85 'edwda':43 'emand':8 'fgohg':53 'fockx':51 'fpakt':93 'fxrgt':12 'gzkgg':6 'hdpri':11 'hdstv':25 'hlgqi':69 'hsykx':96 'hvzmw':40 'hxzns':58 'ibtl':79 'idbah':38 'igbku':48 'igirz':57 'imoxi':49 'irhzd':67 'irsw':26 'iykz':13 'izcuq':91 'jeiul':97 'jjpyo':68 'jpnjg':35 'jwmiy':2 'kbegj':39 'kdtsp':78 'knfnj':45 'koiin':22 'kvhqi':86 'lfvkl':87 'lhqxg':10 'lwdhs':3 'mgdbm':47 'mniqq':4 'mpfli':80 'mpkyr':73 'muhi':56 'nabkn':62 'ncsjo':23 'njycu':75 'nltpr':95 'nqpxe':90 'nruch':70 'ntbzj':55 'nywad':34 'opttw':63 'ovikn':71 'owanl':61 'pcten':21 'petpc':83 'pfaeq':94 'pivqg':82 'pjotx':5 'psqmo':92 'pzfsg':19 'qzyef':72 'rlgva':81 'rrmni':84 'rtawa':33 'rwgkv':20 'ryndt':29 'sadxf':16 'souux':9 'sxmoa':44 'tggmh':46 'tlraa':31 'tspgx':30 'uaebu':74 'ufehp':52 'vyumk':37 'vzxdm':60 'wnarq':50 'wxyfo':14 'xwaob':41 'yccgr':42 'yftnb':99 'yhoer':27 'ynlff':59 'yzqbl':18 'zesxj':1 'zjsju':77 'zkxyx':64 'zqybi':100 'zvbfk':66 -[ RECORD 10 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ t | hevbf aqlwc vqmfz dypon dxraa rkhuy ogvmk ebicg yxjst nbykd taavf qofvn mnawt yyvfl ehcom hsape gwbpc njizy niarb jupek tpzsm hzomo wnsdj avjzp xmckm gtwjx eblgr odhto isosm piqxy lgbmj tjqim ilnnv gylpu twjdi gmeui fwcyl lqzgt izzqh oqfca toqql cfhpc ldgbo ryxqi hitsl rtnwu wrydi qzmjq nmjqp yltgh esmgi eeiry hijdl sjmho bxcxv vwtma roaee tnljb ovrhb diaku uowuh rkeym tmolb xwqnw izkje hydni ynlbr pidfz htlux tiirs fjaan gpvnb jtwki cjbsj soppu tbhir zuwpx cpazm adltt rgxft feyiy hehrk hxojf hbhcn uxnoq udjxn blqrl krrti hqtzy juidi yhnfj osesl letzy ogzjs mpoce kmbxu bvcux ljeay zgcmw tykcq a | 'adltt':79 'aqlwc':2 'avjzp':24 'blqrl':87 'bvcux':97 'bxcxv':55 'cfhpc':42 'cjbsj':74 'cpazm':78 'diaku':60 'dxraa':5 'dypon':4 'ebicg':8 'eblgr':27 'eeiri':52 'ehcom':15 'esmgi':51 'feyiy':81 'fjaan':71 'fwcyl':37 'gmeui':36 'gpvnb':72 'gtwjx':26 'gwbpc':17 'gylpu':34 'hbhcn':84 'hehrk':82 'hevbf':1 'hijdl':53 'hitsl':45 'hqtzi':89 'hsape':16 'htlux':69 'hxojf':83 'hydni':66 'hzomo':22 'ilnnv':33 'isosm':29 'izkj':65 'izzqh':39 'jtwki':73 'juidi':90 'jupek':20 'kmbxu':96 'krrti':88 'ldgbo':43 'letzi':93 'lgbmj':31 'ljeay':98 'lqzgt':38 'mnawt':13 'mpoce':95 'nbykd':10 'niarb':19 'njizi':18 'nmjqp':49 'odhto':28 'ogvmk':7 'ogzj':94 'oqfca':40 'osesl':92 'ovrhb':59 'pidfz':68 'piqxi':30 'qofvn':12 'qzmjq':48 'rgxft':80 'rkeym':62 'rkhuy':6 'roaee':57 'rtnwu':46 'ryxqi':44 'sjmho':54 'soppu':75 'taavf':11 'tbhir':76 'tiir':70 'tjqim':32 'tmolb':63 'tnljb':58 'toqql':41 'tpzsm':21 'twjdi':35 'tykcq':100 'udjxn':86 'uowuh':61 'uxnoq':85 'vqmfz':3 'vwtma':56 'wnsdj':23 'wrydi':47 'xmckm':25 'xwqnw':64 'yhnfj':91 'yltgh':50 'ynlbr':67 'yxjst':9 'yyvfl':14 'zgcmw':99 'zuwpx':77
9、压测"搜索文本, 同时按匹配度(rank)顺序返回"的性能.
vi t4.sql SELECT t, a <=> to_tsquery('english', 'beautiful | place') AS rank FROM test_rum WHERE a @@ to_tsquery('english', 'beautiful | place') ORDER BY a <=> to_tsquery('english', 'beautiful | place') limit 10;
pgbench -M prepared -n -r -P 1 -f ./t4.sql -c 10 -j 10 -T 120 transaction type: ./t4.sql scaling factor: 1 query mode: prepared number of clients: 10 number of threads: 10 duration: 120 s number of transactions actually processed: 16124603 latency average = 0.074 ms latency stddev = 0.049 ms initial connection time = 20.692 ms tps = 134394.399840 (without initial connection time) statement latencies in milliseconds: 0.074 SELECT t, a <=> to_tsquery('english', 'beautiful | place') AS rank
2.1.3对照
1、文章标签存储空间对比
| 实现方式 | 1000万条数据 表占用空间 | 索引占有空间 | 说明 |
| 传统方法 | 424 MB | 267 MB | 文章和标签采用一对一(scalar type)存储结构,btree索引,占用空间大 |
| rum 索引接口 | 43 MB | 101 MB | 文章和标签采用一对多(array type)存储结构,rum索引,占用空间小 |
2、文章标签查询性能对比
| 实现方式 | 单一标签查询tps | 2标签并集查询tps | 2标签交集查询tps | 说明 |
| 传统方法 | 76555 | 110 | 99 | 传统btree索引单一标签没有额外的sort开销, 但是多标签场景不支持sort, 需要大量tuple的查询和sort. |
| rum 索引接口 | 2845 | 1419 | 2342 | rum索引内的tuple item并不是按附加信息值(即这里的ts)顺序存储的, 单一标签凸显了sort开销. 但是当是有多标签查询时, 倒排索引就凸显出了它的性能优势 |
3、文本向量搜索对比
| 实现方式 | 100万条文本向量搜索和rank排序 TPS |
| 传统方法 | 不支持 |
| rum 索引接口 | 134394 |
3.知识点
rum
gin
tsvector
array
4.思考
时间换空间?
空间换时间?
5.参考
- https://github.com/postgrespro/rum
- 《PolarDB 开源版通过 rum 实现高效率搜索和高效率排序的解决方案》
- 《重新发现PostgreSQL之美 - 9 面向多值列的倒排索引GIN|RUM》
- 《PostgreSQL RUM 索引原理》
- 《PostgreSQL 任意字段组合搜索 - rum 或 多字段 bitmapscan 对比》
- 《PostgreSQL rum 索引结构 - 比gin posting list|tree 的ctid(行号)多了addition info》
- 《PostgreSQL 相似搜索插件介绍大汇总 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》
- 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》
- 《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化，普通、数组等组合字段生成新数组》
- 《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1 - 菜鸟 某仿真系统》
- 《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 3 rum, smlar应用场景分析》
- 《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》
- 《PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)》