From 6e2f3ae8842392c46ccc91a9ce4bba92296890cb Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 31 Jan 2011 21:33:55 -0500 Subject: 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 --- doc/src/sgml/pgtrgm.sgml | 18 ++++++++++++++++-- 1 file changed, 16 insertions(+), 2 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/pgtrgm.sgml b/doc/src/sgml/pgtrgm.sgml index 27a23067205..9210899ec24 100644 --- a/doc/src/sgml/pgtrgm.sgml +++ b/doc/src/sgml/pgtrgm.sgml @@ -137,8 +137,10 @@ The pg_trgm module provides GiST and GIN index operator classes that allow you to create an index over a text column for the purpose of very fast similarity searches. These index types support - the above-described similarity operators (and no other operators, so you may - want a regular B-tree index too). + the above-described similarity operators, and additionally support + trigram-based index searches for LIKE and ILIKE + queries. (These indexes do not support equality nor simple comparison + operators, so you may need a regular B-tree index too.) @@ -181,6 +183,18 @@ SELECT t, t <-> 'word' AS dist a small number of the closest matches is wanted. + + Beginning in PostgreSQL 9.1, these index types also support + index searches for LIKE and ILIKE, for example + +SELECT * FROM test_trgm WHERE t LIKE '%foo%bar'; + + The index search works by extracting trigrams from the search string + and then looking these up in the index. The more trigrams in the search + string, the more effective the index search is. Unlike B-tree based + searches, the search string need not be left-anchored. + + The choice between GiST and GIN indexing depends on the relative performance characteristics of GiST and GIN, which are discussed elsewhere. -- cgit v1.2.3