Postgres ignoring GIN index for LIKE query?

Question

I am working in Postgres 9.4. I have a table and index like this:

                                              Table "public.title"
             Column              |          Type           |                     Modifiers                      
---------------------------------+-------------------------+----------------------------------------------------
 id                              | integer                 | not null default nextval('title_id_seq'::regclass)
 pty_addr                        | character varying(1000) | not null

I have a GIN index on the pty_addr column:

"title_pty_add_15f367_gin" gin (pty_addr)

My query is this:

EXPLAIN ANALYZE SELECT * FROM "title" WHERE  pty_addr LIKE '%FOO%';

This query is performing a sequential scan, taking 1.2 seconds:

                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on title  (cost=0.00..193271.60 rows=599 width=4446) (actual time=1.191..1220.640 rows=7945 loops=1)
   Filter: ((pty_addr)::text ~~ '%FOO%'::text)
   Rows Removed by Filter: 3592055
 Planning time: 0.330 ms
 Execution time: 1220.992 ms

Why isn't this query using the index?

Answer 1

If you want to index for LIKE which are not prefix queries, or for ILIKE queries, then you should look into the pg_trgm extension, giving an index that would look like:

"title_pty_add_15f367_gin" gin (pty_addr gin_trgm_ops)

Also, there have been substantial improvements since 9.4. You will be doing yourself a favor to upgrade to at least 9.6.