From: | Aaron Lewis <the(dot)warl0ck(dot)1989(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Trigram is slow when 10m rows |
Date: | 2016-11-13 11:54:36 |
Message-ID: | CAJZVxRkcg=MAqryAFaR7wgei4sVAtkEz3fSL8zh5p1OxK0=XHA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | aws backup | 2016-11-13 13:51:38 | Re: pg_dumpall: could not connect to database "template1": FATAL: |
Previous Message | Aaron Lewis | 2016-11-13 11:50:18 | Why is this query not using GIN index? |