Skip to main content
edited tags
Link
user1822
user1822
Source Link
Richard
  • 257
  • 1
  • 4
  • 9

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?