PostgreSQL模糊匹配走索引的操作

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

场景 lower(name) like ‘pf%’

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

1CREATE INDEX users_idx0 ON users (name);

全字匹配查询(走索引)

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)

 

加函数全字匹配(不走索引)

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)

 

模糊匹配(不走索引)

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)

 

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索引:函数走索引

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

加函数全字匹配(走索引)

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)

 

模糊匹配(不走索引)

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走索引

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

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

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

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

模糊匹配(走索引)

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%’

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:不走索引

1CREATE INDEX idx_name ON users USING btree (lower(name) varchar_pattern_ops);

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:可以走索引

CREATE EXTENSION pg_trgm;CREATE INDEX idx_users_name_trgm_gist ON users USING gist (name gist_trgm_ops);

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://www.jb51.net/article/204764.htm

因内容太旧或其它原因,不再提供查看全文,如有问题,请联系我们。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至253000106@qq.com举报,一经查实,本站将立刻删除。

发布者:SEO优化专员,转转请注明出处:https://www.chuangxiangniao.com/p/892119.html

(0)
上一篇 2025年1月3日 23:33:38
下一篇 2025年1月3日 23:33:58

AD推荐 黄金广告位招租... 更多推荐

相关推荐

  • LNMP中PHP-FPM配置优化

    LNMP架构(Linux、Nginx、MySQL、PHP)是Web应用部署的热门选择。提升LNMP性能的关键在于优化各个组件,特别是PHP-FPM。本文将探讨如何优化PHP-FPM以及其他相关组件,以增强整体Web应用性能。 PHP-FPM…

    2025年3月4日
    200
  • LNMP环境下Memcached应用

    在LNMP架构(Linux、Nginx、MySQL、PHP)中集成Memcached能显著提升网站性能。通过缓存数据库查询结果及API调用数据,降低数据库负载,从而加速数据检索。以下步骤详细阐述如何在LNMP环境下部署Memcached: …

    2025年3月4日
    200
  • 如何设计和实现高效的接口限流机制?

    构建高效的API限流机制 本文探讨如何设计一个高效的API频率限制器,并分析PHP生态中缺乏独立、通用的开源限流组件的原因。 许多开发者都面临着API访问频率控制的需求,以应对恶意请求或突发流量。 实现限流的核心在于记录每个请求的访问次数和…

    2025年3月4日
    200
  • 轻量级PHP接口限流方案如何设计?为什么PHP缺乏独立的限流类开源项目?

    高效PHP接口限流方案设计与实现 本文探讨如何构建一个轻量级的PHP接口限流机制,并分析PHP生态中缺乏独立的、单一功能限流类开源项目的原因。 核心目标是创建一个高效的接口频率控制系统。理想方案应避免引入大型框架,保持轻量级特性。 Redi…

    2025年3月4日
    200
  • LNMP中MySQL如何优化查询

    提升LNMP架构下MySQL数据库查询效率是至关重要的性能优化环节。本文将介绍一系列实用技巧,助您显著改善数据库性能及响应速度。 一、索引策略 索引创建: 针对频繁用于查询条件的字段创建索引,例如:CREATE INDEX idx_colu…

    2025年3月4日
    200
  • 如何优化Linux LAMP性能

    提升Linux LAMP(Linux, Apache, MySQL, PHP)服务器性能,需要从系统、网络、数据库及应用软件等多方面入手。本文提供一系列优化建议,助您打造高效稳定的LAMP架构。 一、系统内核与操作系统优化 保持系统更新: …

    2025年3月4日
    200
  • java框架的运用趋势与新技术结合

    java 框架应用趋势:微服务架构:提高可扩展性和灵活性。响应式编程:处理大量并发请求而不阻塞线程。云原生框架:在云环境中构建和部署应用程序。nosql 数据库集成:支持大量非结构化数据。人工智能和机器学习:增加智能和自动化功能。 Java…

    2025年3月4日
    200
  • 采用 Java 框架构建微服务架构面临的挑战?

    采用 java 框架构建微服务架构涉及以下挑战:服务间通信:选择合适的通信机制,如 rest api、http、grpc 或消息队列。分布式数据管理:维护数据一致性和避免分布式事务。服务发现和注册:集成 spring cloud eurek…

    2025年3月4日
    200
  • java框架如何防止代码注入

    java框架防止代码注入的方式包括:验证输入、转义特殊字符、查询参数化、反序列化保护。例如,spring security框架通过验证输入、转义特殊字符、使用身份验证管理器来保护登录端点。其他框架如apache struts、playfra…

    2025年3月4日
    200
  • Java框架在企业应用程序开发中的应用案例

    java框架在企业应用开发中广泛应用,提供组件、工具和库,简化开发过程。应用案例包括:spring framework:提供mvc框架用于构建web应用程序。spring data jpa:简化与关系数据库交互。hibernate:实现对象…

    2025年3月4日
    200

发表回复

登录后才能评论