diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2011-01-31 21:33:55 -0500 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2011-01-31 21:34:49 -0500 |
| commit | 6e2f3ae8842392c46ccc91a9ce4bba92296890cb (patch) | |
| tree | 5a672b0b9a6f1b5f0d1b264285cfb5a74574e13e /doc/src | |
| parent | 6238473adb119a5a11061e40e159e8c5187fe492 (diff) | |
| download | postgresql-6e2f3ae8842392c46ccc91a9ce4bba92296890cb.tar.gz postgresql-6e2f3ae8842392c46ccc91a9ce4bba92296890cb.zip | |
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
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/pgtrgm.sgml | 18 |
1 files changed, 16 insertions, 2 deletions
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 <filename>pg_trgm</filename> 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 <literal>LIKE</> and <literal>ILIKE</> + queries. (These indexes do not support equality nor simple comparison + operators, so you may need a regular B-tree index too.) </para> <para> @@ -182,6 +184,18 @@ SELECT t, t <-> '<replaceable>word</>' AS dist </para> <para> + Beginning in <productname>PostgreSQL</> 9.1, these index types also support + index searches for <literal>LIKE</> and <literal>ILIKE</>, for example +<programlisting> +SELECT * FROM test_trgm WHERE t LIKE '%foo%bar'; +</programlisting> + 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. + </para> + + <para> The choice between GiST and GIN indexing depends on the relative performance characteristics of GiST and GIN, which are discussed elsewhere. As a rule of thumb, a GIN index is faster to search than a GiST index, but |