On Sun, Nov 13, 2016 at 2:54 PM, 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? > Rows Removed by Index Recheck: 11402855 Heap Blocks: exact=39557 lossy=158010 You need to increase work_mem > > -- > 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 >
