沉浸式学习PostgreSQL|PolarDB 13: 博客、网站按标签内容检索, 并按匹配度排序

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 在内容检索(例如网站的文章检索、搜索引擎的内容搜索)的场景中, 除了内容本身的匹配与否作为判断条件, 还有一个重要的需求是按匹配度、内容的新鲜度等进行排序返回, 匹配度越高|新鲜度|权重越高的优先返回.场景1: 博客类网站, 设定了一些内容标签, 每篇文章在发表时需要给文章设定标签, 一篇文章可能会设定若干个标签. 同时文章发表时间越大表示越新鲜. 用户在浏览时, 选择某个标签, 按新鲜度顺序返回这个标签下的文章.

作者

digoal

日期

2023-09-07

标签

PostgreSQL , PolarDB , 数据库 , 教学


背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

  • 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

ARM机器使用以下docker image:

业务场景1 介绍: 内容检索及匹配度排序

在内容检索(例如网站的文章检索、搜索引擎的内容搜索)的场景中, 除了内容本身的匹配与否作为判断条件, 还有一个重要的需求是按匹配度、内容的新鲜度等进行排序返回, 匹配度越高|新鲜度|权重越高的优先返回.

实现和对照

场景1: 博客类网站, 设定了一些内容标签, 每篇文章在发表时需要给文章设定标签, 一篇文章可能会设定若干个标签. 同时文章发表时间越大表示越新鲜. 用户在浏览时, 选择某个标签, 按新鲜度顺序返回这个标签下的文章.

场景2: 还是上面的场景, 但是每个文章对应的每个标签都设置一个权重, 表示这篇文章在这个标签上的匹配程度. 用户在浏览时, 选择某个标签, 按匹配度以及新鲜度顺序返回这个标签下的文章.

场景3: 博客类网站, 按内容检索匹配的文章, 并按匹配度(rank)顺序返回文章.

传统方法 设计和实验

传统数据库没有数组类型, 不支持一对多的数据结构存储.

传统数据库没有文本向量类型, 不支持文本向量的全文检索和文本向量的匹配度排序.

场景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、性能测试

vi t222.sql  
\set tid1 random(1,1000)   
\set tid2 random(1,1000)   
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 desc limit 100;
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

不支持

PolarDB|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

对照

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

知识点

rum

gin

tsvector

array

思考

时间换空间?

空间换时间?

参考

https://github.com/postgrespro/rum

202212/20221221_01.md 《PolarDB 开源版通过 rum 实现高效率搜索和高效率排序的解决方案》
202105/20210531_02.md 《重新发现PostgreSQL之美 - 9 面向多值列的倒排索引GIN|RUM》
202011/20201128_02.md 《PostgreSQL RUM 索引原理》
202005/20200520_02.md 《PostgreSQL 任意字段组合搜索 - rum 或 多字段 bitmapscan 对比》
201907/20190706_01.md 《PostgreSQL rum 索引结构 - 比gin posting list|tree 的ctid(行号)多了addition info》
201809/20180904_01.md 《PostgreSQL 相似搜索插件介绍大汇总 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》
201808/20180803_01.md 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》
201805/20180518_02.md 《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化,普通、数组等组合字段生成新数组》
201802/20180228_01.md 《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1 - 菜鸟 某仿真系统》
201701/20170116_04.md 《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 3 rum, smlar应用场景分析》
201612/20161231_01.md 《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》
201610/20161019_01.md 《PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)》
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
7天前
|
关系型数据库 分布式数据库 数据库
云原生数据库PolarDB快速入门实验
【2月更文挑战第2天】很基础的PolarDB入门操作实验考试,假期闲着无聊考着玩玩。云原生数据库 PolarDB MySQL 版是阿里云自研产品,100%兼容 MySQL。PolarDB产品具有多主多写、多活容灾、HTAP 等特性,交易性能最高可达开源数据库的6倍,分析性能最高可达开源数据库的400倍,TCO 低于自建数据库50%。
|
13天前
|
SQL 存储 关系型数据库
MySQL技能完整学习列表——1、数据库基础概念——1、关系型数据库(Relational Database)
MySQL技能完整学习列表——1、数据库基础概念——1、关系型数据库(Relational Database)
16 0
|
14天前
|
SQL 关系型数据库 分布式数据库
PolarDB for PostgreSQL逻辑复制问题之逻辑复制冲突如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。
|
14天前
|
存储 关系型数据库 分布式数据库
PolarDB for PostgreSQL查询问题之条件查询失败如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。
|
14天前
|
关系型数据库 分布式数据库 PolarDB
PolarDB for PostgreSQL启动问题之启动失败如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。
|
14天前
|
SQL 关系型数据库 分布式数据库
PolarDB for PostgreSQL报错问题之poll报错如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。
|
14天前
|
Oracle 关系型数据库 分布式数据库
PolarDB for PostgreSQL报错问题之跨节点执行报错如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。
|
14天前
|
关系型数据库 Java 分布式数据库
PolarDB for PostgreSQL参数问题之参数删除失败如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。
|
14天前
|
关系型数据库 分布式数据库 数据库
PolarDB for PostgreSQL报错问题之psql连接数据库报错如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。
|
19天前
|
SQL 算法 JavaScript
【数据库SQL server】关系型数据库的基本知识
【数据库SQL server】关系型数据库的基本知识
82 0

热门文章

最新文章

相关产品

  • 云原生数据库 PolarDB