Re: Trigram is slow when 10m rows

On Sun, Nov 13, 2016 at 3:54 AM, Aaron Lewis <the(dot)warl0ck(dot)1989(at)gmail(dot)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