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:


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?

  • For what it's worth: the searches I need to perform will be case-insensitive.
    – Richard
    Oct 1, 2019 at 10:17
  • You can't natively build a gin index on varchar(1000). Are you using some extension to allow you to do this?
    – jjanes
    Oct 1, 2019 at 14:19

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.