Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit935f666

Browse files
committed
Handle equality operator in contrib/pg_trgm
Obviously, in order to equality operator be satisfiable, target string mustcontain all the trigrams of the search string. On this base, we implementequality operator in GiST/GIN indexes with recheck.Discussion:https://postgr.es/m/CAOBaU_YWwtT7tdggtROacjdOdeYHCz-tmSwuC-j-TOG-g97J0w%40mail.gmail.comAuthor: Julien RouhaudReviewed-by: Tom Lane, Alexander Korotkov, Georgios Kokolatos, Erik Rijkers
1 parent92bf7e2 commit935f666

File tree

9 files changed

+264
-10
lines changed

9 files changed

+264
-10
lines changed

‎contrib/pg_trgm/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@ OBJS = \
99
trgm_regexp.o
1010

1111
EXTENSION = pg_trgm
12-
DATA = pg_trgm--1.4--1.5.sql pg_trgm--1.3--1.4.sql\
12+
DATA = pg_trgm--1.5--1.6.sql pg_trgm--1.4--1.5.sql pg_trgm--1.3--1.4.sql\
1313
pg_trgm--1.3.sql pg_trgm--1.2--1.3.sql pg_trgm--1.1--1.2.sql\
1414
pg_trgm--1.0--1.1.sql
1515
PGFILEDESC = "pg_trgm - trigram matching"

‎contrib/pg_trgm/expected/pg_trgm.out

Lines changed: 200 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -4761,6 +4761,12 @@ insert into test2 values ('abcdef');
47614761
insert into test2 values ('quark');
47624762
insert into test2 values (' z foo bar');
47634763
insert into test2 values ('/123/-45/');
4764+
insert into test2 values ('line 1');
4765+
insert into test2 values ('%line 2');
4766+
insert into test2 values ('line 3%');
4767+
insert into test2 values ('%line 4%');
4768+
insert into test2 values ('%li%ne 5%');
4769+
insert into test2 values ('li_e 6');
47644770
create index test2_idx_gin on test2 using gin (t gin_trgm_ops);
47654771
set enable_seqscan=off;
47664772
explain (costs off)
@@ -4863,7 +4869,13 @@ select * from test2 where t ~ '(abc)*$';
48634869
quark
48644870
z foo bar
48654871
/123/-45/
4866-
(4 rows)
4872+
line 1
4873+
%line 2
4874+
line 3%
4875+
%line 4%
4876+
%li%ne 5%
4877+
li_e 6
4878+
(10 rows)
48674879

48684880
select * from test2 where t ~* 'DEF';
48694881
t
@@ -4918,7 +4930,11 @@ select * from test2 where t ~ '[a-z]{3}';
49184930
abcdef
49194931
quark
49204932
z foo bar
4921-
(3 rows)
4933+
line 1
4934+
%line 2
4935+
line 3%
4936+
%line 4%
4937+
(7 rows)
49224938

49234939
select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
49244940
t
@@ -4961,6 +4977,93 @@ select * from test2 where t ~ '/\d+/-\d';
49614977
/123/-45/
49624978
(1 row)
49634979

4980+
-- test = operator
4981+
explain (costs off)
4982+
select * from test2 where t = 'abcdef';
4983+
QUERY PLAN
4984+
------------------------------------------
4985+
Bitmap Heap Scan on test2
4986+
Recheck Cond: (t = 'abcdef'::text)
4987+
-> Bitmap Index Scan on test2_idx_gin
4988+
Index Cond: (t = 'abcdef'::text)
4989+
(4 rows)
4990+
4991+
select * from test2 where t = 'abcdef';
4992+
t
4993+
--------
4994+
abcdef
4995+
(1 row)
4996+
4997+
explain (costs off)
4998+
select * from test2 where t = '%line%';
4999+
QUERY PLAN
5000+
------------------------------------------
5001+
Bitmap Heap Scan on test2
5002+
Recheck Cond: (t = '%line%'::text)
5003+
-> Bitmap Index Scan on test2_idx_gin
5004+
Index Cond: (t = '%line%'::text)
5005+
(4 rows)
5006+
5007+
select * from test2 where t = '%line%';
5008+
t
5009+
---
5010+
(0 rows)
5011+
5012+
select * from test2 where t = 'li_e 1';
5013+
t
5014+
---
5015+
(0 rows)
5016+
5017+
select * from test2 where t = '%line 2';
5018+
t
5019+
---------
5020+
%line 2
5021+
(1 row)
5022+
5023+
select * from test2 where t = 'line 3%';
5024+
t
5025+
---------
5026+
line 3%
5027+
(1 row)
5028+
5029+
select * from test2 where t = '%line 3%';
5030+
t
5031+
---
5032+
(0 rows)
5033+
5034+
select * from test2 where t = '%line 4%';
5035+
t
5036+
----------
5037+
%line 4%
5038+
(1 row)
5039+
5040+
select * from test2 where t = '%line 5%';
5041+
t
5042+
---
5043+
(0 rows)
5044+
5045+
select * from test2 where t = '%li_ne 5%';
5046+
t
5047+
---
5048+
(0 rows)
5049+
5050+
select * from test2 where t = '%li%ne 5%';
5051+
t
5052+
-----------
5053+
%li%ne 5%
5054+
(1 row)
5055+
5056+
select * from test2 where t = 'line 6';
5057+
t
5058+
---
5059+
(0 rows)
5060+
5061+
select * from test2 where t = 'li_e 6';
5062+
t
5063+
--------
5064+
li_e 6
5065+
(1 row)
5066+
49645067
drop index test2_idx_gin;
49655068
create index test2_idx_gist on test2 using gist (t gist_trgm_ops);
49665069
set enable_seqscan=off;
@@ -5056,7 +5159,13 @@ select * from test2 where t ~ '(abc)*$';
50565159
quark
50575160
z foo bar
50585161
/123/-45/
5059-
(4 rows)
5162+
line 1
5163+
%line 2
5164+
line 3%
5165+
%line 4%
5166+
%li%ne 5%
5167+
li_e 6
5168+
(10 rows)
50605169

50615170
select * from test2 where t ~* 'DEF';
50625171
t
@@ -5111,7 +5220,11 @@ select * from test2 where t ~ '[a-z]{3}';
51115220
abcdef
51125221
quark
51135222
z foo bar
5114-
(3 rows)
5223+
line 1
5224+
%line 2
5225+
line 3%
5226+
%line 4%
5227+
(7 rows)
51155228

51165229
select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
51175230
t
@@ -5154,6 +5267,89 @@ select * from test2 where t ~ '/\d+/-\d';
51545267
/123/-45/
51555268
(1 row)
51565269

5270+
-- test = operator
5271+
explain (costs off)
5272+
select * from test2 where t = 'abcdef';
5273+
QUERY PLAN
5274+
------------------------------------------
5275+
Index Scan using test2_idx_gist on test2
5276+
Index Cond: (t = 'abcdef'::text)
5277+
(2 rows)
5278+
5279+
select * from test2 where t = 'abcdef';
5280+
t
5281+
--------
5282+
abcdef
5283+
(1 row)
5284+
5285+
explain (costs off)
5286+
select * from test2 where t = '%line%';
5287+
QUERY PLAN
5288+
------------------------------------------
5289+
Index Scan using test2_idx_gist on test2
5290+
Index Cond: (t = '%line%'::text)
5291+
(2 rows)
5292+
5293+
select * from test2 where t = '%line%';
5294+
t
5295+
---
5296+
(0 rows)
5297+
5298+
select * from test2 where t = 'li_e 1';
5299+
t
5300+
---
5301+
(0 rows)
5302+
5303+
select * from test2 where t = '%line 2';
5304+
t
5305+
---------
5306+
%line 2
5307+
(1 row)
5308+
5309+
select * from test2 where t = 'line 3%';
5310+
t
5311+
---------
5312+
line 3%
5313+
(1 row)
5314+
5315+
select * from test2 where t = '%line 3%';
5316+
t
5317+
---
5318+
(0 rows)
5319+
5320+
select * from test2 where t = '%line 4%';
5321+
t
5322+
----------
5323+
%line 4%
5324+
(1 row)
5325+
5326+
select * from test2 where t = '%line 5%';
5327+
t
5328+
---
5329+
(0 rows)
5330+
5331+
select * from test2 where t = '%li_ne 5%';
5332+
t
5333+
---
5334+
(0 rows)
5335+
5336+
select * from test2 where t = '%li%ne 5%';
5337+
t
5338+
-----------
5339+
%li%ne 5%
5340+
(1 row)
5341+
5342+
select * from test2 where t = 'line 6';
5343+
t
5344+
---
5345+
(0 rows)
5346+
5347+
select * from test2 where t = 'li_e 6';
5348+
t
5349+
--------
5350+
li_e 6
5351+
(1 row)
5352+
51575353
-- Check similarity threshold (bug #14202)
51585354
CREATE TEMP TABLE restaurants (city text);
51595355
INSERT INTO restaurants SELECT 'Warsaw' FROM generate_series(1, 10000);

‎contrib/pg_trgm/pg_trgm--1.5--1.6.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
/* contrib/pg_trgm/pg_trgm--1.5--1.6.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_trgm UPDATE TO '1.6'" to load this file. \quit
5+
6+
ALTEROPERATOR FAMILY gin_trgm_ops USING gin ADD
7+
OPERATOR11 pg_catalog.= (text,text);
8+
9+
ALTEROPERATOR FAMILY gist_trgm_ops USING gist ADD
10+
OPERATOR11 pg_catalog.= (text,text);

‎contrib/pg_trgm/pg_trgm.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
# pg_trgm extension
22
comment = 'text similarity measurement and index searching based on trigrams'
3-
default_version = '1.5'
3+
default_version = '1.6'
44
module_pathname = '$libdir/pg_trgm'
55
relocatable = true
66
trusted = true

‎contrib/pg_trgm/sql/pg_trgm.sql

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,12 @@ insert into test2 values ('abcdef');
101101
insert into test2values ('quark');
102102
insert into test2values (' z foo bar');
103103
insert into test2values ('/123/-45/');
104+
insert into test2values ('line 1');
105+
insert into test2values ('%line 2');
106+
insert into test2values ('line 3%');
107+
insert into test2values ('%line 4%');
108+
insert into test2values ('%li%ne 5%');
109+
insert into test2values ('li_e 6');
104110
createindextest2_idx_ginon test2 using gin (t gin_trgm_ops);
105111
set enable_seqscan=off;
106112
explain (costs off)
@@ -137,6 +143,23 @@ select * from test2 where t ~ ' z foo bar';
137143
select*from test2where t ~' z foo';
138144
select*from test2where t ~'qua(?!foo)';
139145
select*from test2where t ~'/\d+/-\d';
146+
-- test = operator
147+
explain (costs off)
148+
select*from test2where t='abcdef';
149+
select*from test2where t='abcdef';
150+
explain (costs off)
151+
select*from test2where t='%line%';
152+
select*from test2where t='%line%';
153+
select*from test2where t='li_e 1';
154+
select*from test2where t='%line 2';
155+
select*from test2where t='line 3%';
156+
select*from test2where t='%line 3%';
157+
select*from test2where t='%line 4%';
158+
select*from test2where t='%line 5%';
159+
select*from test2where t='%li_ne 5%';
160+
select*from test2where t='%li%ne 5%';
161+
select*from test2where t='line 6';
162+
select*from test2where t='li_e 6';
140163
dropindex test2_idx_gin;
141164

142165
createindextest2_idx_giston test2 using gist (t gist_trgm_ops);
@@ -175,6 +198,23 @@ select * from test2 where t ~ ' z foo bar';
175198
select*from test2where t ~' z foo';
176199
select*from test2where t ~'qua(?!foo)';
177200
select*from test2where t ~'/\d+/-\d';
201+
-- test = operator
202+
explain (costs off)
203+
select*from test2where t='abcdef';
204+
select*from test2where t='abcdef';
205+
explain (costs off)
206+
select*from test2where t='%line%';
207+
select*from test2where t='%line%';
208+
select*from test2where t='li_e 1';
209+
select*from test2where t='%line 2';
210+
select*from test2where t='line 3%';
211+
select*from test2where t='%line 3%';
212+
select*from test2where t='%line 4%';
213+
select*from test2where t='%line 5%';
214+
select*from test2where t='%li_ne 5%';
215+
select*from test2where t='%li%ne 5%';
216+
select*from test2where t='line 6';
217+
select*from test2where t='li_e 6';
178218

179219
-- Check similarity threshold (bug #14202)
180220

‎contrib/pg_trgm/trgm.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,7 @@
3737
#defineWordDistanceStrategyNumber8
3838
#defineStrictWordSimilarityStrategyNumber9
3939
#defineStrictWordDistanceStrategyNumber10
40+
#defineEqualStrategyNumber11
4041

4142
typedefchartrgm[3];
4243

‎contrib/pg_trgm/trgm_gin.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,7 @@ gin_extract_query_trgm(PG_FUNCTION_ARGS)
8989
caseSimilarityStrategyNumber:
9090
caseWordSimilarityStrategyNumber:
9191
caseStrictWordSimilarityStrategyNumber:
92+
caseEqualStrategyNumber:
9293
trg=generate_trgm(VARDATA_ANY(val),VARSIZE_ANY_EXHDR(val));
9394
break;
9495
caseILikeStrategyNumber:
@@ -221,6 +222,7 @@ gin_trgm_consistent(PG_FUNCTION_ARGS)
221222
#endif
222223
/* FALL THRU */
223224
caseLikeStrategyNumber:
225+
caseEqualStrategyNumber:
224226
/* Check if all extracted trigrams are presented. */
225227
res= true;
226228
for (i=0;i<nkeys;i++)
@@ -306,6 +308,7 @@ gin_trgm_triconsistent(PG_FUNCTION_ARGS)
306308
#endif
307309
/* FALL THRU */
308310
caseLikeStrategyNumber:
311+
caseEqualStrategyNumber:
309312
/* Check if all extracted trigrams are presented. */
310313
res=GIN_MAYBE;
311314
for (i=0;i<nkeys;i++)

‎contrib/pg_trgm/trgm_gist.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -232,6 +232,7 @@ gtrgm_consistent(PG_FUNCTION_ARGS)
232232
caseSimilarityStrategyNumber:
233233
caseWordSimilarityStrategyNumber:
234234
caseStrictWordSimilarityStrategyNumber:
235+
caseEqualStrategyNumber:
235236
qtrg=generate_trgm(VARDATA(query),
236237
querysize-VARHDRSZ);
237238
break;
@@ -338,7 +339,8 @@ gtrgm_consistent(PG_FUNCTION_ARGS)
338339
#endif
339340
/* FALL THRU */
340341
caseLikeStrategyNumber:
341-
/* Wildcard search is inexact */
342+
caseEqualStrategyNumber:
343+
/* Wildcard and equal search are inexact */
342344
*recheck= true;
343345

344346
/*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp