X
X
Xaip2020-06-15 12:52:32
PostgreSQL
Xaip, 2020-06-15 12:52:32

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;

Answer
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

Tell me how to optimize the query.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question