create table mytable(hash char(40), title text); create index title_trgm_idx on mytable using gin(title gin_trgm_ops);
When I run a query with 10m rows, it uses the Trigram index, but takes 3s to execute, very slow. (I have 80m rows, but only inserted 10m for testing purpose)
test=# select count(*) from mytable; count ---------- 13971887 (1 row)
test=# explain analyze select * from mytable where title ilike 'x264'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on mytable (cost=462.69..5639.67 rows=1380 width=83) (actual time=2937.308..2937.308 rows=0 loops=1) Recheck Cond: (title ~~* 'x264'::text) Rows Removed by Index Recheck: 11402855 Heap Blocks: exact=39557 lossy=158010 -> Bitmap Index Scan on title_trgm_idx (cost=0.00..462.35 rows=1380 width=0) (actual time=342.440..342.440 rows=1220793 loops=1) Index Cond: (title ~~* 'x264'::text) Planning time: 0.611 ms Execution time: 2937.729 ms (8 rows)
Any ideas to speed things up?
What version of postgresql and pg_trgm are you using? It might work better under 9.6/1.3
Since your query doesn't use wildcards, it is probably more well suited to a regular btree index, perhaps with citext.
