I have a simple table with Trigram index, 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? -- Best Regards, Aaron Lewis - PGP: 0x13714D33 - http://pgp.mit.edu/ Finger Print: 9F67 391B B770 8FF6 99DC D92D 87F6 2602 1371 4D33 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general