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)      | 
Indexes:
    "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)

Why?

Answers


https://hashrocket.com/blog/posts/exploring-postgres-gin-index

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