GIST index not used in SELECT statement with LIKE

Question

In PostgreSQL 8.3 (sorry for the outdated version - I cannot change that) I have a table like this:

CREATE TABLE tablephonebook
(
  id bigserial NOT NULL,
  name1 text,
  name2 text,
  number text,
  ...
  CONSTRAINT tablephonebook_pkey PRIMARY KEY (id)
)

The table has ca. 50 000 records.

I've created a GIST index successfully:

CREATE INDEX trgm_idx ON tablephonebook USING gist (name1 gist_trgm_ops, name2 gist_trgm_ops);

Doing a text search with the LIKE (or ILIKE) operator takes too long and doesn't use the GIST index:

EXPLAIN ANALYZE SELECT id, name1, name2
  WHERE name1 ILIKE '%south%'
     OR name2 ILIKE '%south%'
   FROM tablephonebook
  ORDER BY id
  LIMIT 1000;

Limit  (cost=0.00..10737.05 rows=903 width=80) (actual time=333.125..333.125 rows=0 loops=1)
  ->  Seq Scan on tablephonebook  (cost=0.00..10737.05 rows=903 width=80) (actual time=333.123..333.123 rows=0 loops=1)"
      Filter: ((name1 ~~* '%south%'::text) OR (name2 ~~* '%south%'::text))
Total runtime: 333.155 ms

What am I doing wrong? I've read that LIKE / ILIKE makes use of this word index.

As an alternate approach I tried to use the full text search with "to_tsquery" (and get magnificent speed) but I wasn't able to find substring matches which is a requirement. Is that correct that full text search can only find whole words?

Answer 1

Your outdated version is the problem.
This works as expected with modern PostgreSQL:

SQL Fiddle demo for pg 9.3.

But not in pg 8.3:

SQL Fiddle demo for pg 8.3.

Support for this was added with Postgres 9.1. Per release notes:

E.28.3.13.2. Performance

Add support for `LIKE` and `ILIKE` index searches to `contrib/pg_trgm` (Alexander Korotkov)