On Sun, Nov 13, 2016 at 3:54 AM, Aaron Lewis <the.warl0ck.1...@gmail.com> wrote:
> 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? > 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. Cheers, Jeff
