Postgres ignoring GIN index for LIKE query? 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?
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.