這篇文章主要介紹了PostgreSQL模糊匹配走索引的模糊操作，具有很好的匹配參考價(jià)值，希望對大家有所幫助。走索一起跟隨小編過(guò)來(lái)看看吧。操作

場(chǎng)景 lower(name) like 'pf%'

create table users (id int primary key,模糊 name varchar(255));
Create or replace function random_string(len(′?｀*)gth 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 intege(???)r := 0;
begin
if length < 0 then
raise exception 'G( ?▽?)iven length cannot be le(?⊿?)ss than 0';
end if;
for i in 1..length loop='loop'(/ω＼)
result := result || chars[1+random()*(array_length(chars, 1)-1)];??
end loop="";
return result;
end;
$$ lan??guage plpgsq??l;
insert into users values(gene( ?° ?? ?°)rate_series(1,50000), random_string(15))(╬?益?);

普通bt：不走索引

pg_trgm模塊提供函數和操作符測定字母數字文本基于三元模型匹配的相似性，還有支持快速搜索相似字符串的匹配索引操作符類(lèi)。三元模型是走索一組從一個(gè)字符??串中獲得的三個(gè)連續的字符。我們可以通過(guò)ˉ\_(ツ)_/ˉ計數兩個(gè)字ヽ(′ー｀)ノ符串共享的操作三元模型的數量??來(lái)測量它們的相似性。這個(gè)簡(jiǎn)單的模糊想法證明在測量許多自然語(yǔ)言詞匯的相似性時(shí)是非常有效的。

1CREATE INDEX users_i??dx0 ON users (n??ame);

全字匹配查詢(xún)（走索引）

e(°ロ°) !xplain select * from userヾ(＾-＾)ノs where name='pfDNQVmhqDrF1EY';
QUERY PLAN
-------------------(′▽?zhuān)?)--(′▽?zhuān)?----------------------------------------??------------
Index Scan using users_idx0 on 匹配users (cost=0.29..8.31 rows=1 width=20)
Index Cond: ((name)::text = 'pfDNQVmhqDrF1EY'::text)
(2 rows)

加函數(shu)全字匹配（不走(╥_╥)索引）

explain select * from users where lower(name)='pfDNQVmhqDrF1EY';
QUERY PLAN
-------------------------------(°□°)----------------------------
Seq Scan on users (co(′?｀)st=0.00..1069.00 rows=250 width=20)
Filter: (lower((name)::text) = 'pfDNQVmhqDrF1EY'::text)
(2 rows)

模糊(??-)?匹配（??不走索引）

expla(′?ω?`)in 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_';
QUE??RY PLAN
-----(╬?益?)---------------------------------------------------
Seq Sca??n on us?ers (cost=0.00..9┐(′д｀)┌44.00 rows=5 width=20)ヽ(′ー｀)ノ
Filter: ((name)::text ~~ 'pf_'::t??ext)

字段帶函數的bt索引：函數走索引

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

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

explain select * from users where lower(name)='pfDNQVmhqD??rF1EY';
QUERY PLAN
--------------------(′_ゝ`)-------------------------------------------------(′ω｀*)------
Bitmap Heap Scan on users (cost=6.23..32??4.34 rows=250 width=20)
Recheck Cond: (lower((name)::text) = 'pfDNQVmhqDrF1EY'::text)
-> Bitmap Index Scan on users_dex1 (cost=0(′?｀).(?????)0ヽ(′▽?zhuān)?/0..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
-----(O_O)------------(?????)---------------------------------------??---
Seqヽ(′?｀)ノ Scan on use??rs (cost=0.00..1069.00 rows=250 width=2(°ロ°) !0)
Filter: (loweヽ(′▽?zhuān)?ノr((name)::text) ~~ 'pf%'::t┐(′?｀)┌ext)
(2 rows??)

聲明操作符類(lèi)的bt索引：like走索引

定義索引的同時(shí)可以為索(???)引的(de)每個(gè)字段聲明一個(gè)操作符類(lèi)。

1CREATE INDEX name ON table (column opclass [sort opt??ions] [,走索 …]);

這個(gè)操作符類(lèi)指明該索引用于該字段時(shí)要使用的操作符。

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

模糊匹配??（走索引）

explain?? select * from users wh??eヽ(′?｀)ノre 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 row(′?｀*)s=53 width=0)
Index Cond: ((lower((name)::text) ~>=~ 'pf'::text) AND (lower((name)::text(′?｀)) ~<~ 'pg'::text))
(4 rows)

場(chǎng)景2 name like '%pf%'

Crea(′ω｀)te?? 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}';ヽ(′ー｀)ノ
re(′?｀)sult text := '';
i integer := 0;
begin
if length < 0 then
raise exception 'Given length canヽ(′?｀)ノnot 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_seri(╯°□°)╯es(1,50000), random_string(15));

聲明操作符?bt：不走索引

1CREATE INDEX idx_name ON users USING btree (lower(name) varchar_patte??rn_ops);

explain (analyze true,format yaml, ve??rbose 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: 71( ?ヮ?)0 +
Actual loop='loop's: 1 +
Output: +
- "id" +
- "name" +
Fi??lter: "(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.1??88 +
Trigge(°□°)rs: +
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, buffe(′▽?zhuān)?rs true) select * from users where name like '%pf%';
QUERY PLAN
---------------??------------------------??-------------------ヽ(′▽?zhuān)?ノ-----------------??(╯‵□′)╯-------------??---------------??-----------------------------------
Bitmap Heap Scan on public.users (cost=32.19..371.08 rows=505 width=20) (actual time=19.314..53.132 rows=193 loop="loop"s=1)
Output: id, name
Recheck Cond: ((users.name)::text ~~ '%pf(′?｀)%'::text)
Rows?? Removed by Index Recheck: 49807
Heap Blocks: exact=319
B(⊙_⊙)uffers: shared hit=972
-> Bitmap Index Scan onヽ(′▽?zhuān)?ノ idx_users_name_trgm_gist (cost=0.00..32.06 rows=505 width=0) (actual time=19.175..19.175 rows=50000 loop="loop"s=1)
Index Cond: ((users.namヾ(′▽?zhuān)??e)::text ~~ '%pf%'::text)
Buffers: shared hit=653
Planning time: 0.188 ms
Execution time: 53.231 ms
(11??(′_｀) rows)

文章來(lái)源：腳本之家

來(lái)源地址：https://www.jb51.net/article/204764.htm