Support LIKE and ILIKE index searches via contrib/pg_trgm indexes.

Unlike Btree-based LIKE optimization, this works for non-left-anchored
search patterns.  The effectiveness of the search depends on how many
trigrams can be extracted from the pattern.  (The worst case, with no
trigrams, degrades to a full-table scan, so this isn't a panacea.  But
it can be very useful.)

Alexander Korotkov, reviewed by Jan Urbanski

1 files changed, 25 insertions, 0 deletions

diff --git a/contrib/pg_trgm/sql/pg_trgm.sql b/contrib/pg_trgm/sql/pg_trgm.sql
index 5e5539c005..b8209344c3 100644
--- a/contrib/pg_trgm/sql/pg_trgm.sql
+++ b/contrib/pg_trgm/sql/pg_trgm.sql
@@ -45,3 +45,28 @@ set enable_seqscan=off;
 select t,similarity(t,'qwertyu0988') as sml from test_trgm where t % 'qwertyu0988' order by sml desc, t;
 select t,similarity(t,'gwertyu0988') as sml from test_trgm where t % 'gwertyu0988' order by sml desc, t;
 select t,similarity(t,'gwertyu1988') as sml from test_trgm where t % 'gwertyu1988' order by sml desc, t;
+
+create table test2(t text);
+insert into test2 values ('abcdef');
+insert into test2 values ('quark');
+create index test2_idx_gin on test2 using gin (t gin_trgm_ops);
+set enable_seqscan=off;
+explain (costs off)
+  select * from test2 where t like '%BCD%';
+explain (costs off)
+  select * from test2 where t ilike '%BCD%';
+select * from test2 where t like '%BCD%';
+select * from test2 where t like '%bcd%';
+select * from test2 where t ilike '%BCD%';
+select * from test2 where t ilike 'qua%';
+drop index test2_idx_gin;
+create index test2_idx_gist on test2 using gist (t gist_trgm_ops);
+set enable_seqscan=off;
+explain (costs off)
+  select * from test2 where t like '%BCD%';
+explain (costs off)
+  select * from test2 where t ilike '%BCD%';
+select * from test2 where t like '%BCD%';
+select * from test2 where t like '%bcd%';
+select * from test2 where t ilike '%BCD%';
+select * from test2 where t ilike 'qua%';