Answer the question
In order to leave comments, you need to log in
Why is trigram search so slow?
There is a table with addresses. I use GIN index for inaccurate search by addresses. But the response is incredibly slow at 300k records, searching for an address can take from 3-6 seconds.
The base is spinning on ssd.
Request example:
EXPLAIN ANALYSE SELECT *, similarity(title, 'г. Санкт-Петербург') FROM addresses
WHERE title % 'г. Санкт-Петербург' ORDER BY similarity DESC limit 5;
Limit (cost=3114.58..3114.59 rows=5 width=189) (actual time=2952.433..2952.434 rows=5 loops=1)
-> Sort (cost=3114.58..3115.54 rows=383 width=189) (actual time=2952.432..2952.432 rows=5 loops=1)
" Sort Key: (similarity(title, 'г. Санкт-Петербург'::text)) DESC"
Sort Method: top-N heapsort Memory: 26kB
-> Bitmap Heap Scan on addresses (cost=1810.97..3108.22 rows=383 width=189) (actual time=73.699..2911.092 rows=132305 loops=1)
Recheck Cond: (title % 'г. Санкт-Петербург'::text)
Rows Removed by Index Recheck: 5930
Heap Blocks: exact=4599
-> Bitmap Index Scan on title_trgm_idx (cost=0.00..1810.87 rows=383 width=0) (actual time=73.013..73.013 rows=138235 loops=1)
Index Cond: (title % 'г. Санкт-Петербург'::text)
Planning time: 0.105 ms
Execution time: 2952.466 ms
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question