Queries using GIN index on f_unaccent() seem slow?

Question

I have almost 20M rows in one table that contains product, including their names.

I wish to search fulltext and fast by the name so I created this index:

CREATE INDEX uprice_item_occurrence_unaccent_name_trgm_idx ON price_item_occurrence USING gin (f_unaccent(name) gin_trgm_ops);;

I was hopping the following query would take less then (lets say) 500ms

select * from price_item_occurrence as oo
where f_unaccent(oo.name) % f_unaccent('iphone');

But it takes almost 2s:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) % f_unaccent('iphone');
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on price_item_occurrence oo  (cost=1956.42..63674.14 rows=16570 width=287) (actual time=247.918..1880.759 rows=94 loops=1)
   Recheck Cond: (f_unaccent((name)::text) % 'iphone'::text)
   Rows Removed by Index Recheck: 87838
   Heap Blocks: exact=76663
   ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..1952.28 rows=16570 width=0) (actual time=195.418..195.418 rows=88962 loops=1)
         Index Cond: (f_unaccent((name)::text) % 'iphone'::text)
 Planning time: 0.444 ms
 Execution time: 1880.833 ms

It's possible that the DB is busy, but i am not sure.

I tried to play with select set_limit(0.9); (increased) and it helps a bit but not much.

I am using Postgres 10, can change the Postgres config and I am open for suggestions.

I tried it with ilike and it improved by some:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) ilike ('%' || f_unaccent('iphone') || '%');
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on price_item_occurrence oo  (cost=3135.08..416823.45 rows=166075 width=286) (actual time=50.258..670.085 rows=65917 loops=1)
   Recheck Cond: (f_unaccent((name)::text) ~~* '%iphone%'::text)
   Rows Removed by Index Recheck: 10
   Heap Blocks: exact=59750
   ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..3093.56 rows=166075 width=0) (actual time=37.385..37.385 rows=67700 loops=1)
         Index Cond: (f_unaccent((name)::text) ~~* '%iphone%'::text)
 Planning time: 0.545 ms
 Execution time: 675.776 ms
(8 rows)

About 2x faster.

i tried limit 10:

postgres=# explain analyze select * from price_item_occurrence as oo where f_unaccent(oo.name) % f_unaccent('iphone') limit 10;
                                                                                QUERY PLAN                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=373.27..410.51 rows=10 width=287) (actual time=268.718..589.131 rows=10 loops=1)
   ->  Bitmap Heap Scan on price_item_occurrence oo  (cost=373.27..62493.45 rows=16680 width=287) (actual time=268.715..589.123 rows=10 loops=1)
         Recheck Cond: (f_unaccent((name)::text) % 'iphone'::text)
         Rows Removed by Index Recheck: 18917
         Heap Blocks: exact=17100
         ->  Bitmap Index Scan on uprice_item_occurrence_unaccent_name_trgm_idx  (cost=0.00..369.10 rows=16680 width=0) (actual time=165.958..165.958 rows=69268 loops=1)
               Index Cond: (f_unaccent((name)::text) % 'iphone'::text)
 Planning time: 0.397 ms
 Execution time: 589.187 ms
(9 rows)

this is also faster, maybe almost good enough

Answer 1

I have seen GIN indexes to generally perform much faster than GiST for these queries. Try this index instead:

CREATE INDEX price_item_occurrence_name_trgm_gin idx ON price_item_occurrence
USING GIN (f_unaccent(name) gin_trgm_ops);

Related:

• Does PostgreSQL support “accent insensitive” collations?
• Using ILIKE with unaccent and with only right end wildcard
• How is LIKE implemented?
• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
• Trigram index for for ILIKE patterns not working as expected

All the basic advice for performance optimization applies. For starters, your table needs to be VACUUMed and ANALYZEd enough to be fast.

• Configuring PostgreSQL for read performance