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?

There is no FROM clause in your query?

Erwin Brandstetter
– Erwin Brandstetter

2014-07-03 17:46:44 +00:00
Commented Jul 3, 2014 at 17:46 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 3, 2014 at 17:46
Simplified query, I assume I forgot it ;-)

Markus L
– Markus L

2014-07-24 07:54:46 +00:00
Commented Jul 24, 2014 at 7:54 — Markus L
– Markus L, Commented Jul 24, 2014 at 7:54
Well, edit to fix it then?

Erwin Brandstetter
– Erwin Brandstetter

2014-07-24 15:07:23 +00:00
Commented Jul 24, 2014 at 15:07 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 24, 2014 at 15:07

Erwin Brandstetter · Accepted Answer · 2014-07-03 17:50:25Z

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)

answered Jul 3, 2014 at 17:50

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

GIST index not used in SELECT statement with LIKE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related