0

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

  • Your index definiton says (name gist_trgm_ops). I assume that's a typo and it's actually on (f_unaccent(name) gist_trgm_ops)? And please explain more closely what you want to find exactly. The similarity operator % and ILIKE vastly differ. Also: your exact definition of f_unaccent(). Did you base it on this answer? stackoverflow.com/a/11007216/939860 – Erwin Brandstetter Apr 9 '18 at 23:17
  • (name gist_trgm_ops) is not a typo but maybe its incorrect, the index is still used in the query. I want to find products (names) that contain the 'iphone' text (its just an example). I know that ILIKE and % are different but i can imagine to use both. – Michal Apr 10 '18 at 11:31
  • Please don't change the nature of the question after answers have been posted. Switching to GIN makes it a different question. You can always start a new question - with consistent information. – Erwin Brandstetter Apr 10 '18 at 12:13
2

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:

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

  • kk thx i try and let you know – Michal Apr 10 '18 at 11:27
  • Ok so to my shame i realised that i already had a gin index same as you suggested, and its already used in the example above uprice_item_occurrence_unaccent_name_trgm_idx sorry for misleading information, i update the question. – Michal Apr 10 '18 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.