Return to Question

RESULTS WITH CHANGES

• Update f_unnacent function
• Added btree index CREATE INDEX index_foo_on_name_de ON foo (f_unaccent(name) text_pattern_ops) WHERE locale = 'de';
• Added gin index on configuration CREATE INDEX index_foo_on_configuration ON foo USING gin(configuration jsonb_path_ops);
• Removed old index

A) Query:

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

A) Query Plan:

 Limit  (cost=0.00..121248.83 rows=100 width=301) (actual time=16319.267..16319.267 rows=0 loops=1)
   Buffers: shared hit=262079 read=1449294
   ->  Seq Scan on foo  (cost=0.00..12842675.96 rows=10592 width=301) (actual time=16319.261..16319.261 rows=0 loops=1)
         Filter: (((locale)::text = 'de'::text) AND ((configuration -> 'bar'::text) @> '{"is": ["a"]}'::jsonb) AND (f_unaccent((name)::text) ~~* 'v%'::text))
         Rows Removed by Filter: 41227048
         Buffers: shared hit=262079 read=1449294
 Planning time: 0.765 ms
 Execution time: 16319.313 ms and more!!!

B) Query without configuration:

EXPLAIN (ANALYZE, BUFFERS)
SELECT  "foo".* FROM "foo" WHERE "foo"."locale" = 'de' 
AND f_unaccent(foo.name) ILIKE f_unaccent('v%') LIMIT 100;

B) Query Plan:

 Limit  (cost=0.00..119.31 rows=100 width=301) (actual time=0.227..2.912 rows=100 loops=1)
   Buffers: shared read=31
   ->  Seq Scan on foo  (cost=0.00..12636540.72 rows=10591544 width=301) (actual time=0.221..2.864 rows=100 loops=1)
         Filter: (((locale)::text = 'de'::text) AND (f_unaccent((name)::text) ~~* 'v%'::text))
         Rows Removed by Filter: 691
         Buffers: shared read=31
 Planning time: 0.501 ms
 Execution time: 2.985 ms

C) Query without configuration and limit:

EXPLAIN (ANALYZE, BUFFERS)
SELECT  "foo".* FROM "foo" WHERE "foo"."locale" = 'de' 
AND f_unaccent(foo.name) ILIKE f_unaccent('v%');

C) Query Plan:

 Bitmap Heap Scan on foo  (cost=346203.46..4864616.26 rows=10591544 width=301) (actual time=23526.443..30050.008 rows=2196 loops=1)
   Recheck Cond: ((locale)::text = 'de'::text)
   Rows Removed by Index Recheck: 14094842
   Filter: (f_unaccent((name)::text) ~~* 'v%'::text)
   Rows Removed by Filter: 10781095
   Heap Blocks: exact=572873 lossy=847868
   Buffers: shared read=1494015
   ->  Bitmap Index Scan on index_foo_on_name_de  (cost=0.00..343555.58 rows=10592603 width=0) (actual time=1788.454..1788.454 rows=10783291 loops=1)
         Buffers: shared read=73274
 Planning time: 0.528 ms
 Execution time: 30050.168 ms

Without foo.configuration filter the query is very fast (1.021 ms). But I need this filter. Here the query without the filter:

EXPLAIN (ANALYZE, BUFFERS)
SELECT  "foo".* FROM "foo" WHERE "foo"."locale" = 'de'
AND f_unaccent(foo.name) ILIKE f_unaccent('v%')
LIMIT 100;