Skip to content

Commit 935f666

Browse files
akorotkovakorotkov
committed
Handle equality operator in contrib/pg_trgm
Obviously, in order to equality operator be satisfiable, target string must contain all the trigrams of the search string. On this base, we implement equality operator in GiST/GIN indexes with recheck. Discussion: https://postgr.es/m/CAOBaU_YWwtT7tdggtROacjdOdeYHCz-tmSwuC-j-TOG-g97J0w%40mail.gmail.com Author: Julien Rouhaud Reviewed-by: Tom Lane, Alexander Korotkov, Georgios Kokolatos, Erik Rijkers
1 parent 92bf7e2 commit 935f666

File tree

9 files changed

+264
-10
lines changed

9 files changed

+264
-10
lines changed

‎contrib/pg_trgm/Makefile

+1-1
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

+200-4
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

+10
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+
ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
7+
OPERATOR 11 pg_catalog.= (text, text);
8+
9+
ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
10+
OPERATOR 11 pg_catalog.= (text, text);

‎contrib/pg_trgm/pg_trgm.control

+1-1
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

+40
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,12 @@ insert into test2 values ('abcdef');
101101
insert into test2 values ('quark');
102102
insert into test2 values (' z foo bar');
103103
insert into test2 values ('/123/-45/');
104+
insert into test2 values ('line 1');
105+
insert into test2 values ('%line 2');
106+
insert into test2 values ('line 3%');
107+
insert into test2 values ('%line 4%');
108+
insert into test2 values ('%li%ne 5%');
109+
insert into test2 values ('li_e 6');
104110
create index test2_idx_gin on 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 test2 where t ~ ' z foo';
138144
select * from test2 where t ~ 'qua(?!foo)';
139145
select * from test2 where t ~ '/\d+/-\d';
146+
-- test = operator
147+
explain (costs off)
148+
select * from test2 where t = 'abcdef';
149+
select * from test2 where t = 'abcdef';
150+
explain (costs off)
151+
select * from test2 where t = '%line%';
152+
select * from test2 where t = '%line%';
153+
select * from test2 where t = 'li_e 1';
154+
select * from test2 where t = '%line 2';
155+
select * from test2 where t = 'line 3%';
156+
select * from test2 where t = '%line 3%';
157+
select * from test2 where t = '%line 4%';
158+
select * from test2 where t = '%line 5%';
159+
select * from test2 where t = '%li_ne 5%';
160+
select * from test2 where t = '%li%ne 5%';
161+
select * from test2 where t = 'line 6';
162+
select * from test2 where t = 'li_e 6';
140163
drop index test2_idx_gin;
141164

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

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

‎contrib/pg_trgm/trgm.h

+1
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,7 @@
3737
#define WordDistanceStrategyNumber 8
3838
#define StrictWordSimilarityStrategyNumber 9
3939
#define StrictWordDistanceStrategyNumber 10
40+
#define EqualStrategyNumber 11
4041

4142
typedef char trgm[3];
4243

‎contrib/pg_trgm/trgm_gin.c

+3
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,7 @@ gin_extract_query_trgm(PG_FUNCTION_ARGS)
8989
case SimilarityStrategyNumber:
9090
case WordSimilarityStrategyNumber:
9191
case StrictWordSimilarityStrategyNumber:
92+
case EqualStrategyNumber:
9293
trg = generate_trgm(VARDATA_ANY(val), VARSIZE_ANY_EXHDR(val));
9394
break;
9495
case ILikeStrategyNumber:
@@ -221,6 +222,7 @@ gin_trgm_consistent(PG_FUNCTION_ARGS)
221222
#endif
222223
/* FALL THRU */
223224
case LikeStrategyNumber:
225+
case EqualStrategyNumber:
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
case LikeStrategyNumber:
311+
case EqualStrategyNumber:
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

+3-1
Original file line numberDiff line numberDiff line change
@@ -232,6 +232,7 @@ gtrgm_consistent(PG_FUNCTION_ARGS)
232232
case SimilarityStrategyNumber:
233233
case WordSimilarityStrategyNumber:
234234
case StrictWordSimilarityStrategyNumber:
235+
case EqualStrategyNumber:
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
case LikeStrategyNumber:
341-
/* Wildcard search is inexact */
342+
case EqualStrategyNumber:
343+
/* Wildcard and equal search are inexact */
342344
*recheck = true;
343345

344346
/*

0 commit comments

Comments
 (0)