Why isn't Postgres using my gin index?

With this table:

=> \d "user"
                                          Table "public.user"
        Column        |            Type             |                     Modifiers                     
 id                   | integer                     | not null default nextval('user_id_seq'::regclass)
 email                | character varying(255)      | 
    "user_pkey" PRIMARY KEY, btree (id)
    "user_email_key" UNIQUE CONSTRAINT, btree (email)
    "user_email_idx" gin (email gin_trgm_ops)

This query isn't using the gin index:

=> explain select email from "user" where email ilike '%j%';
                          QUERY PLAN                          
 Seq Scan on "user"  (cost=0.00..3986.42 rows=11886 width=22)
   Filter: ((email)::text ~~* '%j%'::text)
(2 rows)




Caveats The only downside of this approach is that the input query must be at least 3 letters, as Postgres will need to be able to extract at least one trigram from the input query in order to use our trigram index.

Because you are not actually utilizing trigram index or your version of postgres < 9.1.

select email from "user" where similarity(email, 'xyz@gmail.com') > 0.5;

where 0.5 is your threshold, 0-completely different, 1-exact match

UPD: Consider that you are giving 1 character as matching term, because one symbol can match lots of documents, it may show poor performance

Need Your Help

Finalize method guidelines and best practices in C#

c# .net coding-style finalize

I have recently encountered an issue that is related to code running in the Finalize method overload.

C character values arithmetic

c char ascii expression atoi

I have been reading from the book "The C Programming Language" learning C, and I stumbled upon the arithmetic s[i] - '0' which they said that it gives the numeric value of the character stored in ...