CODEWITHSUNDEEP
ruby-on-railspostgresql
TheGuy
TheGuy

Reputation: 349

How to decrease query execution time on a db with 20 million records | Rails, Postgres

I have a Rails app with Postgres db. It has 20 million records. Most of the queries use ILIKE. I have created a triagram index on one of the columns.

Before adding the triagram index, the query execution time was ~200s to ~300s (seconds not ms)

After creating the triagram index, the query execution time came down to ~30s.

How can I reduce the execution time to milliseconds?

Also are there any good practices/suggestions when dealing with a database this huge?

Thanks in advance :)

Ref : Faster PostgreSQL Searches with Trigrams

Edit: 'Explain Analyze' on one of the queries

EXPLAIN ANALYZE SELECT COUNT(*) FROM "listings" WHERE (categories ilike '%store%');

                        QUERY PLAN        
--------------------------------------------------------------------------
 Aggregate  (cost=716850.70..716850.71 rows=1 width=0) (actual time=199354.861..199354.861 rows=1 loops=1)
 ->  Bitmap Heap Scan on listings  (cost=3795.12..715827.76 rows=409177 width=0) (actual time=378.374..199005.008 rows=691941 loops=1)
     Recheck Cond: ((categories)::text ~~* '%store%'::text)
     Rows Removed by Index Recheck: 7302878
     Heap Blocks: exact=33686 lossy=448936
     ->  Bitmap Index Scan on listings_on_categories_idx  (cost=0.00..3692.82 rows=409177 width=0) (actual time=367.931..367.931 rows=692449 loops=1)
         Index Cond: ((categories)::text ~~* '%store%'::text)
 Planning time: 1.345 ms
 Execution time: 199355.260 ms
 (9 rows)

Upvotes: 0

Views: 1341

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

The index scan itself is fast (0.3 seconds), but the trigram index finds more than half a million potential matches. All of these rows have to be checked if they actually match the pattern, which is where the time is spent.

For longer strings or strings with less common letters the performance should be considerably better. Is it a solution for you to impose a lower bound on the length of the search string?

Other than that, maybe the only solution is to use an external text search software.

Upvotes: 1

Related Questions