全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
云数据库 RDS 版

模糊查询、正则查询和相似查询优化

更新时间:2017-06-07 13:26:11

PostgreSQL 拥有很强的文本搜索能力,除了支持全文检索,还支持模糊查询和正则查询。PostgreSQL 不仅内置了一般数据库都没有的 pg_trgm 插件,还内置了表达式索引和 GIN 索引的功能,为加速各类需求的查询提供了有力条件。

对于正则查询,PostgreSQL 可以通过 pg_trgm 插件来加速查询。模糊查询包括前模糊(有前缀的模糊)、后模糊(有后缀的模糊)和前后模糊(无前后缀的模糊),针对不同的模糊查询需求,PostgreSQL 有不同的优化方法:

  • 对于前模糊和后模糊,PostgreSQL 与其他数据库一样,可以使用 B-tree 来加速查询。对于后模糊,也可以使用反转函数(reverse)的函数索引来加速查询。

  • 对于前后模糊,一种方法是使用 pg_trgm 插件,利用 GIN 索引加速查询(特别是对于输入 3 个或 3 个以上字符的模糊查询有很好的效果)。另一种方法是自定义 GIN 表达式索引的方法,适合于定制的模糊查询。

模糊查询和正则查询都是找出完全符合条件的记录,还有一种需求是相似查询。本文将通过示例介绍如何进行模糊查询、正则查询和相似查询的优化。

背景信息

pg_trgm 模糊查询的原理

pg_trgm 先将字符串的前端添加 2 个空格,末尾添加 1 个空格。每连续的 3 个字符为一个 TOKEN,然后将各 TOKEN 拆开。最后,对 TOKEN 建立 GIN 倒排索引。

您可以通过如下方法查看字符串的 TOKEN。

  1. test=# select show_trgm('123');
  2. show_trgm
  3. -------------------------
  4. {" 1"," 12",123,"23 "}
  5. (1 row)

pg_trgm 优化查询时要求字符个数的原因

使用 pg_trgm 优化前后模糊查询时,若要获得最好的效果,需满足如下条件:

  • 对于前模糊查询,例如 a%,至少需要提供 1 个字符。(搜索的是token=' a'

  • 对于后模糊查询,例如 %ab,至少需要提供 2 个字符。(搜索的是token='ab '

  • 对于前后模糊查询,例如 %abcd%,至少需要提供 3 个字符。( 这个使用数组搜索,搜索的是包含{" a"," ab",abc,bcd,"cd "}的 TOKEN。)

由于 pg_trgm 生成的 TOKEN 是三个字符,只有在以上三个条件下,才能匹配到对应的 TOKEN。

使用示例

  1. test=# select show_trgm('123');
  2. show_trgm
  3. -------------------------
  4. {" 1"," 12",123,"23 "}
  5. (1 row)

前模糊和后模糊查询的优化

前模糊查询的优化方法

PostgreSQL 支持使用 B-tree 来加速前模糊的查询。

  • 当使用类型默认的 index ops class 时,仅适合于collate="C"的查询(当数据库默认的 lc_collate <> C 时,索引和查询都需要明确指定 collate “C”)。

    注意:索引和查询条件的 collate 必须一致才能使用索引。

    使用示例

    1. test=# create table test(id int, info text);
    2. CREATE TABLE
    3. test=# insert into test select generate_series(1,1000000),md5(random()::text);
    4. INSERT 0 1000000
    5. test=# create index idx on test(info collate "C");
    6. CREATE INDEX
    7. test=# explain (analyze,verbose,timing,costs,buffers) select * from test where info like 'abcd%' collate "C";
    8. QUERY PLAN
    9. ----------------------------------------------------------------------------------------------------------------------
    10. Index Scan using idx on public.test (cost=0.42..16.76 rows=100 width=37) (actual time=0.057..0.093 rows=18 loops=1)
    11. Output: id, info
    12. Index Cond: ((test.info >= 'abcd'::text) AND (test.info < 'abce'::text))
    13. Filter: (test.info ~~ 'abcd%'::text COLLATE "C")
    14. Buffers: shared hit=18 read=3
    15. Planning time: 0.424 ms
    16. Execution time: 0.124 ms
    17. (7 rows)
  • 当数据库默认的 lc_collate <> C 时,还可以使用对应类型的 pattern ops 让 B-tree 索引支持模糊查询。使用 pattern ops 将使用字符查询而非 binary 查询的搜索方式。详情请参见文档 PostgreSQL 9.6.2 Documentation — 11.9. Operator Classes and Operator Families

    使用示例

    1. test=# drop table test;
    2. DROP TABLE
    3. test=# create table test(id int, info text);
    4. CREATE TABLE
    5. test=# insert into test select generate_series(1,1000000),md5(random()::text);
    6. INSERT 0 1000000
    7. test=# create index idx on test(info text_pattern_ops);
    8. CREATE INDEX
    9. test=# explain (analyze,verbose,timing,costs,buffers) select * from test where info like 'abcd%' collate "zh_CN";
    10. QUERY PLAN
    11. ----------------------------------------------------------------------------------------------------------------------
    12. Index Scan using idx on public.test (cost=0.42..16.76 rows=100 width=37) (actual time=0.038..0.059 rows=12 loops=1)
    13. Output: id, info
    14. Index Cond: ((test.info ~>=~ 'abcd'::text) AND (test.info ~<~ 'abce'::text))
    15. Filter: (test.info ~~ 'abcd%'::text COLLATE "zh_CN")
    16. Buffers: shared hit=12 read=3
    17. Planning time: 0.253 ms
    18. Execution time: 0.081 ms
    19. (7 rows)
    20. test=# explain (analyze,verbose,timing,costs,buffers) select * from test where info like 'abcd%' collate "C";
    21. QUERY PLAN
    22. ----------------------------------------------------------------------------------------------------------------------
    23. Index Scan using idx on public.test (cost=0.42..16.76 rows=100 width=37) (actual time=0.027..0.050 rows=12 loops=1)
    24. Output: id, info
    25. Index Cond: ((test.info ~>=~ 'abcd'::text) AND (test.info ~<~ 'abce'::text))
    26. Filter: (test.info ~~ 'abcd%'::text COLLATE "C")
    27. Buffers: shared hit=15
    28. Planning time: 0.141 ms
    29. Execution time: 0.072 ms
    30. (7 rows)

    另外,使用类型对应的 pattern ops 时,索引搜索不仅支持 LIKE 的写法,还支持规则表达式的写法,如下所示:

    1. test=# explain (analyze,verbose,timing,costs,buffers) select * from test where info ~ '^abcd';
    2. QUERY PLAN
    3. ----------------------------------------------------------------------------------------------------------------------
    4. Index Scan using idx on public.test (cost=0.42..16.76 rows=100 width=37) (actual time=0.031..0.061 rows=12 loops=1)
    5. Output: id, info
    6. Index Cond: ((test.info ~>=~ 'abcd'::text) AND (test.info ~<~ 'abce'::text))
    7. Filter: (test.info ~ '^abcd'::text)
    8. Buffers: shared hit=15
    9. Planning time: 0.213 ms
    10. Execution time: 0.083 ms
    11. (7 rows)

后模糊查询的优化方法

PostgreSQL 支持使用反转函数索引来加速后模糊的查询。

  • 当使用类型默认的 index ops class 时,仅适合于collate="C"的查询(当数据库默认的 lc_collate <> C 时,索引和查询都需要明确指定 collate “C”)。

    注意:索引和查询条件的 collate 必须一致才能使用索引。

    使用示例

    1. test=# create index idx1 on test(reverse(info) collate "C");
    2. CREATE INDEX
    3. test=# select * from test limit 1;
    4. id | info
    5. ----+----------------------------------
    6. 1 | b3275976cdd437a033d4329775a52514
    7. (1 row)
    8. test=# explain (analyze,verbose,timing,costs,buffers) select * from test where reverse(info) like '4152%' collate "C";
    9. QUERY PLAN
    10. --------------------------------------------------------------------------------------------------------------------------
    11. Index Scan using idx1 on public.test (cost=0.42..4009.43 rows=5000 width=37) (actual time=0.061..0.097 rows=18 loops=1)
    12. Output: id, info
    13. Index Cond: ((reverse(test.info) >= '4152'::text) AND (reverse(test.info) < '4153'::text))
    14. Filter: (reverse(test.info) ~~ '4152%'::text COLLATE "C")
    15. Buffers: shared hit=18 read=3
    16. Planning time: 0.128 ms
    17. Execution time: 0.122 ms
    18. (7 rows)
    19. test=# select * from test where reverse(info) like '4152%' collate "C";
    20. id | info
    21. --------+----------------------------------
    22. 847904 | abe2ecd90393b5275df8e34a39702514
    23. 414702 | 97f66d26545329321164042657d02514
    24. 191232 | 7820972c6220c2b01d46c11ebb532514
    25. 752742 | 93232ac39c6632e2540df44627c42514
    26. 217302 | 39e518893a1a7b1e691619bd1fc42514
    27. 1 | b3275976cdd437a033d4329775a52514
    28. 615718 | 4948f94c484c13dc6c4fae8a3db52514
    29. 308815 | fc2918ceff7c7a4dafd2e04031062514
    30. 149521 | 546d963842ea5ca593e622c810262514
    31. 811093 | 4b6eca2eb6b665af67b2813e91a62514
    32. 209000 | 1dfd0d4e326715c1739f031cca992514
    33. 937616 | 8827fd81f5b673fb5afecbe3e11b2514
    34. 419553 | bd6e01ce360af16137e8b6abc8ab2514
    35. 998324 | 7dff51c19dc5e5d9979163e7d14c2514
    36. 771518 | 8a54e30003a48539fff0aedc73ac2514
    37. 691566 | f90368348e3b6bf983fcbe10db2d2514
    38. 652274 | 8bf4a97b5f122a5540a21fa85ead2514
    39. 233437 | 739ed715fc203d47e37e79b5bcbe2514
    40. (18 rows)
  • 当数据库默认的 lc_collate <> C 时,还可以使用对应类型的 pattern ops 让 B-tree 索引支持模糊查询。使用 pattern ops 将使用字符查询而非 binary 查询的搜索方式。使用类型对应的 pattern ops 时,索引搜索不仅支持 LIKE 的写法,还支持规则表达式的写法。

    使用示例

    1. test=# create index idx1 on test(reverse(info) text_pattern_ops);
    2. CREATE INDEX
    3. test=# explain (analyze,verbose,timing,costs,buffers) select * from test where reverse(info) like '4152%';
    4. QUERY PLAN
    5. --------------------------------------------------------------------------------------------------------------------------
    6. Index Scan using idx1 on public.test (cost=0.42..4009.43 rows=5000 width=37) (actual time=0.026..0.049 rows=12 loops=1)
    7. Output: id, info
    8. Index Cond: ((reverse(test.info) ~>=~ '4152'::text) AND (reverse(test.info) ~<~ '4153'::text))
    9. Filter: (reverse(test.info) ~~ '4152%'::text)
    10. Buffers: shared hit=15
    11. Planning time: 0.102 ms
    12. Execution time: 0.072 ms
    13. (7 rows)
    14. test=# explain (analyze,verbose,timing,costs,buffers) select * from test where reverse(info) ~ '^4152';
    15. QUERY PLAN
    16. --------------------------------------------------------------------------------------------------------------------------
    17. Index Scan using idx1 on public.test (cost=0.42..4009.43 rows=5000 width=37) (actual time=0.031..0.063 rows=12 loops=1)
    18. Output: id, info
    19. Index Cond: ((reverse(test.info) ~>=~ '4152'::text) AND (reverse(test.info) ~<~ '4153'::text))
    20. Filter: (reverse(test.info) ~ '^4152'::text)
    21. Buffers: shared hit=15
    22. Planning time: 0.148 ms
    23. Execution time: 0.087 ms
    24. (7 rows)

前模糊和后模糊查询的单一索引优化方法

PostgreSQL 支持使用 pg_trgm 索引来加速同时包含前模糊和后模糊的查询。

为使索引过滤有较好的效果,前模糊查询至少需输入 1 个字符,后模糊查询至少需输入 2 个字符。若要高效支持多字节字符(如中文),数据库的 lc_ctype 不能为 “C”,只有 TOKEN 分割正确才能有较好的效果。

注意:索引和查询条件的 collate 必须一致才能使用索引。

使用示例

  1. 执行如下代码,创建一个表。

    1. test=# \l+ test
    2. List of databases
    3. Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
    4. ------+----------+----------+------------+------------+-------------------+--------+------------+-------------
    5. test | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | 245 MB | pg_default |
    6. (1 row)
    7. test=# create extension pg_trgm;
    8. test=# create table test001(c1 text);
    9. CREATE TABLE
  2. 执行如下代码,生成随机中文字符串的函数。

    1. test=# create or replace function gen_hanzi(int) returns text as $$
    2. declare
    3. res text;
    4. begin
    5. if $1 >=1 then
    6. select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);
    7. return res;
    8. end if;
    9. return null;
    10. end;
    11. $$ language plpgsql strict;
    12. CREATE FUNCTION
  3. 执行如下代码,生成随机数据。

    1. test=# insert into test001 select gen_hanzi(20) from generate_series(1,100000);
    2. INSERT 0 100000
    3. test=# create index idx_test001_1 on test001 using gin (c1 gin_trgm_ops);
    4. CREATE INDEX
    5. test=# select * from test001 limit 5;
    6. c1
    7. ------------------------------------------
    8. 埳噪办甾讷昃碇玾陧箖燋邢賀浮媊踮菵暔谉橅
    9. 秌橑籛鴎拟倶敤麁鼋醠轇坙騉鏦纗蘛婃坹娴儅
    10. 蔎緾鎧爪鵬二悲膼朠麻鸂鋬楨窷違繇糭嘓索籓
    11. 馳泅薬鐗愅撞窍浉渗蛁灎厀攚摐瞪拡擜詜隝緼
    12. 襳铺煃匶瀌懲荼黹樆惺箧搔羾憯墆鋃硍蔓恧顤
    13. (5 rows)
  4. 执行如下代码,进行模糊查询。

    1. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '你%';
    2. QUERY PLAN
    3. -----------------------------------------------------------------------------------------------------------------------
    4. Bitmap Heap Scan on public.test001 (cost=5.08..15.20 rows=10 width=61) (actual time=0.030..0.034 rows=3 loops=1)
    5. Output: c1
    6. Recheck Cond: (test001.c1 ~~ '你%'::text)
    7. Heap Blocks: exact=3
    8. Buffers: shared hit=7
    9. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..5.08 rows=10 width=0) (actual time=0.020..0.020 rows=3 loops=1)
    10. Index Cond: (test001.c1 ~~ '你%'::text)
    11. Buffers: shared hit=4
    12. Planning time: 0.119 ms
    13. Execution time: 0.063 ms
    14. (10 rows)
    15. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%恧顤';
    16. QUERY PLAN
    17. -----------------------------------------------------------------------------------------------------------------------
    18. Bitmap Heap Scan on public.test001 (cost=5.08..15.20 rows=10 width=61) (actual time=0.031..0.034 rows=1 loops=1)
    19. Output: c1
    20. Recheck Cond: (test001.c1 ~~ '%恧顤'::text)
    21. Rows Removed by Index Recheck: 1
    22. Heap Blocks: exact=2
    23. Buffers: shared hit=6
    24. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..5.08 rows=10 width=0) (actual time=0.020..0.020 rows=2 loops=1)
    25. Index Cond: (test001.c1 ~~ '%恧顤'::text)
    26. Buffers: shared hit=4
    27. Planning time: 0.136 ms
    28. Execution time: 0.062 ms
    29. (11 rows)

前后模糊查询的优化

大于或等于 3 个输入字符的前后模糊查询优化

PostgreSQL 支持使用 pg_trgm 插件来加速前后模糊的查询。为达到更好的加速效果,建议您输入 3 个或 3 个以上字符,详细原因请参见本文背景信息中关于 pg_trgm 优化查询时要求字符个数的原因。

若要让 pg_trgm 高效支持多字节字符(如中文),数据库的 lc_ctype 不能为 “C”,只有 TOKEN 分割正确才能达到加速效果。

使用示例

  1. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%燋邢賀%';
  2. QUERY PLAN
  3. -----------------------------------------------------------------------------------------------------------------------
  4. Bitmap Heap Scan on public.test001 (cost=5.08..15.20 rows=10 width=61) (actual time=0.038..0.038 rows=1 loops=1)
  5. Output: c1
  6. Recheck Cond: (test001.c1 ~~ '%燋邢賀%'::text)
  7. Heap Blocks: exact=1
  8. Buffers: shared hit=5
  9. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..5.08 rows=10 width=0) (actual time=0.025..0.025 rows=1 loops=1)
  10. Index Cond: (test001.c1 ~~ '%燋邢賀%'::text)
  11. Buffers: shared hit=4
  12. Planning time: 0.170 ms
  13. Execution time: 0.076 ms
  14. (10 rows)
  15. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%燋邢%';
  16. QUERY PLAN
  17. --------------------------------------------------------------------------------------------------------------------------------------
  18. Bitmap Heap Scan on public.test001 (cost=7615669.08..7615679.20 rows=10 width=61) (actual time=147.524..178.232 rows=1 loops=1)
  19. Output: c1
  20. Recheck Cond: (test001.c1 ~~ '%燋邢%'::text)
  21. Rows Removed by Index Recheck: 99999
  22. Heap Blocks: exact=1137
  23. Buffers: shared hit=14429
  24. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..7615669.08 rows=10 width=0) (actual time=147.377..147.377 rows=100000 loops=1)
  25. Index Cond: (test001.c1 ~~ '%燋邢%'::text)
  26. Buffers: shared hit=13292
  27. Planning time: 0.133 ms
  28. Execution time: 178.265 ms
  29. (11 rows)

小于 3 个输入字符的前后模糊查询优化

当需要前后模糊搜索 1 个或者 2 个字符时,pg_trgm 无法满足需求,但您可以使用表达式 GIN 索引。

使用表达式,将字符串拆成 1 个单字以及两个连续字符的数组,然后对数组建立 GIN 索引即可。

使用示例

  1. test=# create or replace function split001(text) returns text[] as $$
  2. declare
  3. res text[];
  4. begin
  5. select regexp_split_to_array($1,'') into res;
  6. for i in 1..length($1)-1 loop
  7. res := array_append(res, substring($1,i,2));
  8. end loop;
  9. return res;
  10. end;
  11. $$ language plpgsql strict immutable;
  12. CREATE FUNCTION
  13. test=# create index idx_test001_2 on test001 using gin (split001(c1));
  14. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where split001(c1) @> array['你好'];
  15. QUERY PLAN
  16. ------------------------------------------------------------------------------------------------------------------------
  17. Bitmap Heap Scan on public.test001 (cost=8.87..550.12 rows=500 width=61) (actual time=0.041..0.041 rows=0 loops=1)
  18. Output: c1
  19. Recheck Cond: (split001(test001.c1) @> '{你好}'::text[])
  20. Buffers: shared hit=4
  21. -> Bitmap Index Scan on idx_test001_2 (cost=0.00..8.75 rows=500 width=0) (actual time=0.039..0.039 rows=0 loops=1)
  22. Index Cond: (split001(test001.c1) @> '{你好}'::text[])
  23. Buffers: shared hit=4
  24. Planning time: 0.104 ms
  25. Execution time: 0.068 ms
  26. (9 rows)
  27. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where split001(c1) @> array['你'];
  28. QUERY PLAN
  29. -------------------------------------------------------------------------------------------------------------------------
  30. Bitmap Heap Scan on public.test001 (cost=8.87..550.12 rows=500 width=61) (actual time=0.063..0.183 rows=86 loops=1)
  31. Output: c1
  32. Recheck Cond: (split001(test001.c1) @> '{你}'::text[])
  33. Heap Blocks: exact=80
  34. Buffers: shared hit=84
  35. -> Bitmap Index Scan on idx_test001_2 (cost=0.00..8.75 rows=500 width=0) (actual time=0.048..0.048 rows=86 loops=1)
  36. Index Cond: (split001(test001.c1) @> '{你}'::text[])
  37. Buffers: shared hit=4
  38. Planning time: 0.101 ms
  39. Execution time: 0.217 ms
  40. (10 rows)
  41. test=# select * from test001 where split001(c1) @> array['你'];
  42. c1
  43. ------------------------------------------
  44. 殐踨洪冨垓丩贤閚偉垢胸鍘崩你萭隡劭芛雫袰
  45. 靅慨热脸罆淓寘鰻总襎戍謸枨陪丼倫柆套你仮
  46. ......

正则查询的优化

PostgreSQL 正则查询的语法为字符串 ~ 'pattern'字符串 ~* 'pattern'。详情请参见文档 PostgreSQL 9.6.2 Documentation — 9.7. Pattern Matching

另外,关于正则查询索引原理,请参见 PostgreSQL 源码

使用示例

  1. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 ~ '12[0-9]{3,9}';
  2. QUERY PLAN
  3. ------------------------------------------------------------------------------------------------------------------------
  4. Bitmap Heap Scan on public.test001 (cost=65.08..75.20 rows=10 width=61) (actual time=0.196..0.196 rows=0 loops=1)
  5. Output: c1
  6. Recheck Cond: (test001.c1 ~ '12[0-9]{3,9}'::text)
  7. Rows Removed by Index Recheck: 1
  8. Heap Blocks: exact=1
  9. Buffers: shared hit=50
  10. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..65.08 rows=10 width=0) (actual time=0.183..0.183 rows=1 loops=1)
  11. Index Cond: (test001.c1 ~ '12[0-9]{3,9}'::text)
  12. Buffers: shared hit=49
  13. Planning time: 0.452 ms
  14. Execution time: 0.221 ms
  15. (11 rows)
  16. test01=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 ~ '宸朾啣' collate "zh_CN";
  17. QUERY PLAN
  18. -----------------------------------------------------------------------------------------------------------------------
  19. Bitmap Heap Scan on public.test001 (cost=6.58..19.42 rows=10 width=61) (actual time=0.061..0.061 rows=1 loops=1)
  20. Output: c1
  21. Recheck Cond: (test001.c1 ~ '宸朾啣'::text COLLATE "zh_CN")
  22. Heap Blocks: exact=1
  23. Buffers: shared hit=5
  24. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..6.58 rows=10 width=0) (actual time=0.049..0.049 rows=1 loops=1)
  25. Index Cond: (test001.c1 ~ '宸朾啣'::text COLLATE "zh_CN")
  26. Buffers: shared hit=4
  27. Planning time: 0.238 ms
  28. Execution time: 0.082 ms
  29. (10 rows)

相似查询优化

相似查询是指通过相似度匹配输入的字符串和需要查询的字符串,例如查询 postgresql 字符串时,输入 p0stgresgl 也能根据相似度匹配到 postgresql。

PostgreSQL 支持使用 pg_trgm 插件加速相似查询。为达到更好的加速效果,建议您输入 3 个或 3 个以上字符,详细原因请参见本文背景信息中关于 pg_trgm 优化查询时要求字符个数的原因。

若要高效支持中文相似查询,数据库的 lc_ctype 不能为 “C”,只有 TOKEN 分割正确才能有较好的效果。

使用示例

  1. test=# create index idx_test001_3 on test001 using gist (c1 gist_trgm_ops);
  2. CREATE INDEX
  3. test=# explain (analyze,verbose,timing,costs,buffers) SELECT t, c1 <-> '癷磛鹚蠌鳃蠲123鶡埀婎鳊苿奶垨惸溴蔻筴熝憡' AS dist
  4. FROM test001 t
  5. ORDER BY dist LIMIT 5;
  6. QUERY PLAN
  7. -------------------------------------------------------------------------------------------------------------------------------------------------
  8. Limit (cost=0.28..0.52 rows=5 width=89) (actual time=37.462..37.639 rows=5 loops=1)
  9. Output: t.*, ((c1 <-> '癷磛鹚蠌鳃蠲123鶡埀婎鳊苿奶垨惸溴蔻筴熝憡'::text))
  10. Buffers: shared hit=1631
  11. -> Index Scan using idx_test001_3 on public.test001 t (cost=0.28..4763.28 rows=100000 width=89) (actual time=37.461..37.636 rows=5 loops=1)
  12. Output: t.*, (c1 <-> '癷磛鹚蠌鳃蠲123鶡埀婎鳊苿奶垨惸溴蔻筴熝憡'::text)
  13. Order By: (t.c1 <-> '癷磛鹚蠌鳃蠲123鶡埀婎鳊苿奶垨惸溴蔻筴熝憡'::text)
  14. Buffers: shared hit=1631
  15. Planning time: 0.089 ms
  16. Execution time: 37.668 ms
  17. (9 rows)
  18. test=# SELECT t, c1 <-> '癷磛鹚蠌鳃蠲123鶡埀婎鳊苿奶垨惸溴蔻筴熝憡' AS dist
  19. FROM test001 t
  20. ORDER BY dist LIMIT 5;
  21. t | dist
  22. --------------------------------------------+----------
  23. (癷磛鹚蠌鳃蠲你鶡埀婎鳊苿奶垨惸溴蔻筴熝憡) | 0.307692
  24. (坆桻悁斾耾瑚豌腏炁悿隖轲盃挜稐睟礓蜮铅湆) | 0.976744
  25. (癷鉜餯祂鼃恫蝅瓟顡廕梍蛸歡僷贊敔欓侑韌鐹) | 0.976744
  26. (癷嚯鳬戚蹪熼胘檙佌欔韜挹樷覄惶蹝顼鑜鞖媗) | 0.976744
  27. (癷饎瞲餿堒歃峽盾豼擔禞嵪豦咢脉馄竨济隘缄) | 0.976744
  28. (5 rows)

总结

  • 若只有前模糊查询需求(字符串 like 'xx%'),使用 collate “C” 的 B-tree 索引。当 collate 不为 “C” 时,可以使用类型对应的 pattern ops(如 text_pattern_ops)建立 B-tree 索引。

  • 若只有后模糊的查询需求(字符串 like '%abc'等价于reverse(字符串) like cba%),使用 collate “C” 的 reverse() 表达式的 B-tree 索引。当 collate 不为 “C” 时,可以使用类型对应的 pattern ops(如text_pattern_ops)建立 B-tree 索引。

  • 若有前后模糊查询需求,并且包含中文,请使用 lc_ctype <> “C” 的数据库,同时使用 pg_trgm 插件的 GIN 表达式索引。

  • 若有前后模糊查询需求,并且不包含中文,请使用 pg_trgm 插件的 GIN 表达式索引。

  • 若有正则查询需求,请使用 pg_trgm 插件的 GIN 表达式索引。

  • 若有输入条件少于 3 个字符的模糊查询需求,可以使用 GIN 表达式索引,通过数组包含的方式进行搜索,性能一样非常好。

模糊查询性能测试

如下示例对优化前后模糊查询后的性能进行了测试。该示例中的测试数据为 1 亿条记录,且每条记录有 15 个随机中文。

操作步骤

  1. 执行如下代码,生成测试数据。

    1. vi test.sql
    2. insert into test001 select gen_hanzi(15) from generate_series(1,2500000);
    3. pgbench -n -r -P 1 -f ./test.sql -c 40 -j 40 -t 1 test
    4. test=# select count(*) from test001;
    5. count
    6. -----------
    7. 100000000
    8. (1 row)
    9. test=# select * from test001 limit 10;
    10. c1
    11. --------------------------------
    12. 釾笉皜鰈确艄騚馺腃彊釲忰采汦擇
    13. 槮搮圮墔婂蹾飘孡鶒镇赀聵線麯櫕
    14. 孨鄈韞萅赫炧暤蟠檼駧餪崉娲譌筯
    15. 烸喖醝稦怩鷟棾奜妛曫仾飛饡绘韦
    16. 撑豁襉峊炠眏罱襄彊鰮莆壏妒辷阛
    17. 蜁愊鶱磹贰帵眲嚉榑苍潵檐簄椰魨
    18. 瑄翁蠃巨躋壾蛸湗鑂顂櫟砣八癱栵
    19. 馇巍笿鞒装棊嘢恓煓熴锠鋈蹃煿屓
    20. 訆韄踔牤嘇糺絢軿鵑燿螛梋鰢謇郼
    21. 撲蓨伤釱糕觩嬖蓷鰼繩圆醷熌靉掑
    22. (10 rows)
  2. 执行如下代码,创建索引。

    1. test=# set maintenance_work_mem ='32GB';
    2. test=# create index idx_test001_1 on test001 using gin (c1 gin_trgm_ops);
  3. 执行如下代码,查询表和索引的大小。

    1. test=# \di+
    2. List of relations
    3. Schema | Name | Type | Owner | Table | Size | Description
    4. --------+---------------+-------+----------+---------+-------+-------------
    5. public | idx_test001_1 | index | postgres | test001 | 12 GB |
    6. (1 row)
    7. test=# \dt+
    8. List of relations
    9. Schema | Name | Type | Owner | Size | Description
    10. --------+---------+-------+----------+---------+-------------
    11. public | test001 | table | postgres | 7303 MB |
    12. (1 row)
  4. 分别执行如下代码,进行模糊查询性能测试。

    • 执行如下代码,进行前模糊查询性能测试。

      响应时间:9 毫秒。

      返回 4701 行。

      1. select * from test001 where c1 like '你%';
      2. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '你%';
      3. QUERY PLAN
      4. ------------------------------------------------------------------------------------------------------------------------------
      5. Bitmap Heap Scan on public.test001 (cost=89.50..10161.50 rows=10000 width=46) (actual time=1.546..8.868 rows=4701 loops=1)
      6. Output: c1
      7. Recheck Cond: (test001.c1 ~~ '你%'::text)
      8. Rows Removed by Index Recheck: 85
      9. Heap Blocks: exact=4776
      10. Buffers: shared hit=4784
      11. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..87.00 rows=10000 width=0) (actual time=0.879..0.879 rows=4786 loops=1)
      12. Index Cond: (test001.c1 ~~ '你%'::text)
      13. Buffers: shared hit=8
      14. Planning time: 0.099 ms
      15. Execution time: 9.166 ms
      16. (11 rows)
    • 执行如下代码,进行后模糊查询性能测试。

      响应时间:0.25 毫秒。

      返回 2 行。

      1. select * from test001 where c1 like '%靉掑';
      2. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%靉掑';
      3. QUERY PLAN
      4. ----------------------------------------------------------------------------------------------------------------------------
      5. Bitmap Heap Scan on public.test001 (cost=89.50..10161.50 rows=10000 width=46) (actual time=0.049..0.223 rows=2 loops=1)
      6. Output: c1
      7. Recheck Cond: (test001.c1 ~~ '%靉掑'::text)
      8. Rows Removed by Index Recheck: 87
      9. Heap Blocks: exact=89
      10. Buffers: shared hit=94
      11. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..87.00 rows=10000 width=0) (actual time=0.031..0.031 rows=89 loops=1)
      12. Index Cond: (test001.c1 ~~ '%靉掑'::text)
      13. Buffers: shared hit=5
      14. Planning time: 0.113 ms
      15. Execution time: 0.249 ms
      16. (11 rows)
    • 执行如下代码,进行前后模糊查询性能测试。

      响应时间:0.2 毫秒。

      返回 1 行。

      1. select * from test001 where c1 like '%螛梋鰢%';
      2. test=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%螛梋鰢%';
      3. QUERY PLAN
      4. ----------------------------------------------------------------------------------------------------------------------------
      5. Bitmap Heap Scan on public.test001 (cost=89.50..10161.50 rows=10000 width=46) (actual time=0.044..0.175 rows=1 loops=1)
      6. Output: c1
      7. Recheck Cond: (test001.c1 ~~ '%螛梋鰢%'::text)
      8. Rows Removed by Index Recheck: 81
      9. Heap Blocks: exact=82
      10. Buffers: shared hit=87
      11. -> Bitmap Index Scan on idx_test001_1 (cost=0.00..87.00 rows=10000 width=0) (actual time=0.027..0.027 rows=82 loops=1)
      12. Index Cond: (test001.c1 ~~ '%螛梋鰢%'::text)
      13. Buffers: shared hit=5
      14. Planning time: 0.112 ms
      15. Execution time: 0.201 ms
      16. (11 rows)
本文导读目录