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('This is a search example with gin on table foo%')
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))