I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with gin on table foo%"). In this cases my index is ignored. Here my search query:

EXPLAIN (ANALYZE, TIMING)
SELECT  "foo".* FROM "foo" WHERE "foo"."locale" = 'de'
AND f_unaccent(foo.name) ILIKE f_unaccent('v%')
AND foo.configuration->'bar' @> '{"is":["a"]}'
LIMIT 100;

This is my index:

CREATE INDEX index_foo_on_name_de_gin ON foo USING gin(f_unaccent(name) gin_trgm_ops) WHERE locale = 'de';

Why is the index ignored and uses seq scan and/or Bitmap heap scan? How can I add other index to solve this problem?

Why it does a recheck?

Recheck Cond: ((f_unaccent((name)::text) ~~* 'v%'::text) AND ((locale)::text = 'de'::text))

Function f_unaccent:

CREATE OR REPLACE FUNCTION f_unaccent(text)
         RETURNS text AS
         $func$
         SELECT unaccent('unaccent', $1)
         $func$  LANGUAGE sql IMMUTABLE SET search_path = public, pg_temp;

Query Plan:

 Limit  (cost=24412.85..67568.91 rows=100 width=301) (actual time=21838.473..21838.473 rows=0 loops=1)
   Buffers: shared hit=1 read=749976
   ->  Bitmap Heap Scan on foo  (cost=24412.85..4595502.73 rows=10592 width=301) (actual time=21838.470..21838.470 rows=0 loops=1)
         Recheck Cond: ((f_unaccent((name)::text) ~~* 'v%'::text) AND ((locale)::text = 'de'::text))
         Rows Removed by Index Recheck: 5416739
         Filter: ((configuration -> 'bar'::text) @> '{"is": ["a"]}'::jsonb)
         Rows Removed by Filter: 2196
         Heap Blocks: exact=749172
         Buffers: shared hit=1 read=749976
         ->  Bitmap Index Scan on index_foo_on_name_de_gin  (cost=0.00..24410.20 rows=10591544 width=0) (actual time=641.532..641.532 rows=5418935 loops=1)
               Index Cond: (f_unaccent((name)::text) ~~* 'v%'::text)
               Buffers: shared hit=1 read=804
 Planning time: 0.767 ms
 Execution time: 21838.549 ms

Table definition:

    Column     |            Type             |                          Modifiers                           | Storage  | Stats target | Description 
---------------+-----------------------------+--------------------------------------------------------------+----------+--------------+-------------
 id            | integer                     | not null default nextval('foo_id_seq'::regclass)             | plain    |              | 
 locale        | character varying           | not null                                                     | extended |              | 
 name          | character varying           | not null                                                     | extended |              | 
 configuration | jsonb                       | not null default '{}'::jsonb                                 | extended |              | 

"index_foo_on_configuration" gin (configuration)
"index_foo_on_name_de_gin" gin (f_unaccent(name::text) gin_trgm_ops) WHERE locale::text = 'de'::text