服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - PostgreSQL模糊匹配走索引的操作

PostgreSQL模糊匹配走索引的操作

2021-04-02 21:53JackGo_73 PostgreSQL

这篇文章主要介绍了PostgreSQL模糊匹配走索引的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

场景 lower(name) like 'pf%'

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table users (id int primary key, name varchar(255));
Create or replace function random_string(length integer) returns text as
$$
declare
 chars text[] := '{0,1,2,3,4,5,6,7,8,9,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,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}';
 result text := '';
 i integer := 0;
begin
 if length < 0 then
 raise exception 'Given length cannot be less than 0';
 end if;
 for i in 1..length loop
 result := result || chars[1+random()*(array_length(chars, 1)-1)];
 end loop;
 return result;
end;
$$ language plpgsql;
insert into users values(generate_series(1,50000), random_string(15));

普通bt：不走索引

pg_trgm模块提供函数和操作符测定字母数字文本基于三元模型匹配的相似性，还有支持快速搜索相似字符串的索引操作符类。三元模型是一组从一个字符串中获得的三个连续的字符。我们可以通过计数两个字符串共享的三元模型的数量来测量它们的相似性。这个简单的想法证明在测量许多自然语言词汇的相似性时是非常有效的。

?
1
CREATE INDEX users_idx0 ON users (name);

全字匹配查询（走索引）

?
1
2
3
4
5
6
explain select * from users where name='pfDNQVmhqDrF1EY';
        QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using users_idx0 on users (cost=0.29..8.31 rows=1 width=20)
 Index Cond: ((name)::text = 'pfDNQVmhqDrF1EY'::text)
(2 rows)

加函数全字匹配（不走索引）

?
1
2
3
4
5
6
explain select * from users where lower(name)='pfDNQVmhqDrF1EY';
      QUERY PLAN
-----------------------------------------------------------
 Seq Scan on users (cost=0.00..1069.00 rows=250 width=20)
 Filter: (lower((name)::text) = 'pfDNQVmhqDrF1EY'::text)
(2 rows)

模糊匹配（不走索引）

?
1
2
3
4
5
explain select * from users where name like 'pf%';
      QUERY PLAN
--------------------------------------------------------
 Seq Scan on users (cost=0.00..944.00 rows=5 width=20)
 Filter: ((name)::text ~~ 'pf%'::text)
?
1
2
3
4
5
explain select * from users where name like 'pf_';
      QUERY PLAN
--------------------------------------------------------
 Seq Scan on users (cost=0.00..944.00 rows=5 width=20)
 Filter: ((name)::text ~~ 'pf_'::text)

字段带函数的bt索引：函数走索引

?
1
2
drop index users_idx0;
CREATE INDEX users_dex1 ON users (lower(name));

加函数全字匹配（走索引）

?
1
2
3
4
5
6
7
8
explain select * from users where lower(name)='pfDNQVmhqDrF1EY';
        QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on users (cost=6.23..324.34 rows=250 width=20)
 Recheck Cond: (lower((name)::text) = 'pfDNQVmhqDrF1EY'::text)
 -> Bitmap Index Scan on users_dex1 (cost=0.00..6.17 rows=250 width=0)
   Index Cond: (lower((name)::text) = 'pfDNQVmhqDrF1EY'::text)
(4 rows)

模糊匹配（不走索引）

?
1
2
3
4
5
6
explain select * from users where lower(name) like 'pf%';
      QUERY PLAN
-----------------------------------------------------------
 Seq Scan on users (cost=0.00..1069.00 rows=250 width=20)
 Filter: (lower((name)::text) ~~ 'pf%'::text)
(2 rows)

声明操作符类的bt索引：like走索引

定义索引的同时可以为索引的每个字段声明一个操作符类。

?
1
CREATE INDEX name ON table (column opclass [sort options] [, …]);

这个操作符类指明该索引用于该字段时要使用的操作符。

?
1
CREATE INDEX users_dex2 ON users (lower(name) varchar_pattern_ops);

模糊匹配（走索引）

?
1
2
3
4
5
6
7
8
explain select * from users where lower(name) like 'pf%';
            QUERY PLAN
------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on users (cost=4.82..144.00 rows=5 width=20)
 Filter: (lower((name)::text) ~~ 'pf%'::text)
 -> Bitmap Index Scan on users_dex2 (cost=0.00..4.82 rows=53 width=0)
   Index Cond: ((lower((name)::text) ~>=~ 'pf'::text) AND (lower((name)::text) ~<~ 'pg'::text))
(4 rows)

场景2 name like '%pf%'

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Create or replace function random_string(length integer) returns text as
$$
declare
 chars text[] := '{0,1,2,3,4,5,6,7,8,9,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,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}';
 result text := '';
 i integer := 0;
begin
 if length < 0 then
 raise exception 'Given length cannot be less than 0';
 end if;
 for i in 1..length loop
 result := result || chars[1+random()*(array_length(chars, 1)-1)];
 end loop;
 return result;
end;
$$ language plpgsql;
create table users (id int primary key, name varchar(255));
insert into users values(generate_series(1,50000), random_string(15));

声明操作符bt：不走索引

?
1
CREATE INDEX idx_name ON users USING btree (lower(name) varchar_pattern_ops);
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
explain (analyze true,format yaml, verbose true, buffers true) select * from users where lower(name) like '%pf%';\
      QUERY PLAN
-----------------------------------------------------------
 - Plan:             +
  Node Type: "Seq Scan"        +
  Parallel Aware: false        +
  Relation Name: "users"        +
  Schema: "public"          +
  Alias: "users"          +
  Startup Cost: 0.00         +
  Total Cost: 1069.00         +
  Plan Rows: 5           +
  Plan Width: 20          +
  Actual Startup Time: 0.320       +
  Actual Total Time: 86.841       +
  Actual Rows: 710          +
  Actual Loops: 1          +
  Output:            +
  - "id"            +
  - "name"           +
  Filter: "(lower((users.name)::text) ~~ '%pf%'::text)"+
  Rows Removed by Filter: 49290      +
  Shared Hit Blocks: 319        +
  Shared Read Blocks: 0        +
  Shared Dirtied Blocks: 0        +
  Shared Written Blocks: 0        +
  Local Hit Blocks: 0         +
  Local Read Blocks: 0         +
  Local Dirtied Blocks: 0        +
  Local Written Blocks: 0        +
  Temp Read Blocks: 0         +
  Temp Written Blocks: 0        +
 Planning Time: 0.188         +
 Triggers:            +
 Execution Time: 86.975

声明pg_trgm操作符bt：可以走索引

?
1
2
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm_gist ON users USING gist (name gist_trgm_ops);
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
explain (analyze true, verbose true, buffers true) select * from users where name like '%pf%';
                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.users (cost=32.19..371.08 rows=505 width=20) (actual time=19.314..53.132 rows=193 loops=1)
 Output: id, name
 Recheck Cond: ((users.name)::text ~~ '%pf%'::text)
 Rows Removed by Index Recheck: 49807
 Heap Blocks: exact=319
 Buffers: shared hit=972
 -> Bitmap Index Scan on idx_users_name_trgm_gist (cost=0.00..32.06 rows=505 width=0) (actual time=19.175..19.175 rows=50000 loops=1)
   Index Cond: ((users.name)::text ~~ '%pf%'::text)
   Buffers: shared hit=653
 Planning time: 0.188 ms
 Execution time: 53.231 ms
(11 rows)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。

原文链接：https://blog.csdn.net/u014539401/article/details/72794503

  • 索引
  • PostgreSQL
  • 模糊匹配

    • 延伸 · 阅读

    精彩推荐
    • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

      RDS PostgreSQL一键大版本升级技术解密

      一、PostgreSQL行业位置 （一）行业位置 在讨论PostgreSQL（下面简称为PG）在整个数据库行业的位置之前，我们先看一下阿里云数据库在全球的数据库行业里的...

      未知1192023-05-07
    • PostgreSQLPostgresql查询效率计算初探

      Postgresql查询效率计算初探

      这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Postgresql具有一定的参考学习价...

      轨迹4622020-05-03
    • PostgreSQLpostgresql 数据库中的数据转换

      postgresql 数据库中的数据转换

      postgres8.3以后，字段数据之间的默认转换取消了。如果需要进行数据变换的话，在postgresql数据库中，我们可以用"::"来进行字段数据的类型转换。...

      postgresql教程网12482021-10-08
    • PostgreSQLPostgresql开启远程访问的步骤全纪录

      Postgresql开启远程访问的步骤全纪录

      postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

      我勒个去6812020-04-30
    • PostgreSQLpostgresql 中的to_char()常用操作

      postgresql 中的to_char()常用操作

      这篇文章主要介绍了postgresql 中的to_char()常用操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      J符离13432021-04-12
    • PostgreSQL分布式 PostgreSQL之Citus 架构

      分布式 PostgreSQL之Citus 架构

      节点 Citus 是一种 PostgreSQL 扩展，它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群，允许 PostgreSQL 保存比单...

      未知802023-05-07
    • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

      深入理解PostgreSQL的MVCC并发处理方式

      这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

      PostgreSQL教程网3622020-04-25
    • PostgreSQLPostgreSQL标准建表语句分享

      PostgreSQL标准建表语句分享

      这篇文章主要介绍了PostgreSQL标准建表语句分享，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      码上得天下7962021-02-27
    最近更新
    编辑推荐
    阅读排行
    热门标签
    5432 　 postgresql命令 　 数据库教程 　 入门命令 　 死锁进程 　 PostgreSQL教程 　 表继承 　 事物隔离 　 性能提升技巧 　 PL/pgSQL 　 过程语言 　 partitioning 　 PostgreSQL安装教程 　 跨库访问 　 postgresql数据库 　 并发处理 　 MVCC 　 Citus 　 OID 　 XID 　 表空间大小 　 Postgre 　 phpPgAdmin 　 phpPgAdmin错误 　 phpPgAdmin问题 　 phpPgAdmin配置文件 　 app应用 　 PostgreSQL配置 　 Postgresql下载 　 PostgreSQL入门教程 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图