On Sun, Nov 13, 2016 at 6:05 PM, Aaron Lewis <the.warl0ck.1989@xxxxxxxxx> wrote:
Sigh, didn't notice that. Thanks for the heads up.
It takes 500ms with 10m rows, could it be faster?
sure. Recheck with function call is pretty expensive, so I'd not recommend to create functional index, just create separate column of type tsvector (materialize to_tsvector) and create gin index on it. You should surprise.
I've increased work_mem to 256MB
test=# explain analyze select * from mytable where
to_tsvector('english', title) @@ 'x264'::tsquery limit 1000 offset 10;
QUERY PLAN
------------------------------------------------------------ ------------------------------ ------------------------------ ---------------
Limit (cost=684.06..2949.42 rows=1000 width=83) (actual
time=348.506..536.483 rows=1000 loops=1)
-> Bitmap Heap Scan on mytable (cost=661.41..158917.22 rows=69859
width=83) (actual time=345.354..536.199 rows=1010 loops=1)
Recheck Cond: (to_tsvector('english'::regconfig, title) @@
'''x264'''::tsquery)
Rows Removed by Index Recheck: 12242
Heap Blocks: exact=20 lossy=186
-> Bitmap Index Scan on name_fts (cost=0.00..643.95
rows=69859 width=0) (actual time=333.703..333.703 rows=1044673
loops=1)
Index Cond: (to_tsvector('english'::regconfig, title)
@@ '''x264'''::tsquery)
Planning time: 0.144 ms
Execution time: 537.212 ms
(9 rows)
On Sun, Nov 13, 2016 at 10:33 PM, Julien Rouhaud
<julien.rouhaud@xxxxxxxxxx> wrote:
> On 13/11/2016 15:26, Aaron Lewis wrote:
>> Hi Oleg,
>>
>> Can you elaborate on the title column? I don't get it.
>>
>
>>>> create table mytable(hash char(40), title varchar(500));
>>>> create index name_fts on mytable using gin(to_tsvector('english',
>>>> 'title'));
>
> You created an index on the text 'title', not on the title column, so
> the index is useless.
>
> Drop the existing index and create this one instead:
>
> create index name_fts on mytable using gin(to_tsvector('english', title));
>
>> On Sun, Nov 13, 2016 at 10:10 PM, Oleg Bartunov <obartunov@xxxxxxxxx> wrote:
>>>
>>>
>>> On Sun, Nov 13, 2016 at 2:50 PM, Aaron Lewis <the.warl0ck.1989@xxxxxxxxx>
>>> wrote:
>>>>
>>>> I have a simple table, and a gin index,
>>>>
>>>> create table mytable(hash char(40), title varchar(500));
>>>> create index name_fts on mytable using gin(to_tsvector('english',
>>>> 'title'));
>>>
>>>
>>>
>>> ^^^^^
>>>
>>>>
>>>> create unique index md5_uniq_idx on mytable(hash);
>>>>
>>>> When I execute a query with tsquery, the GIN index was not in use:
>>>>
>>>> test=# explain analyze select * from mytable where
>>>> to_tsvector('english', title) @@ 'abc | def'::tsquery limit 10;
>>>> QUERY PLAN
>>>>
>>>> ------------------------------------------------------------ ------------------------------ --------------------------
>>>> Limit (cost=0.00..277.35 rows=10 width=83) (actual
>>>> time=0.111..75.549 rows=10 loops=1)
>>>> -> Seq Scan on mytable (cost=0.00..381187.45 rows=13744 width=83)
>>>> (actual time=0.110..75.546 rows=10 loops=1)
>>>> Filter: (to_tsvector('english'::regconfig, (title)::text) @@
>>>> '''abc'' | ''def'''::tsquery)
>>>> Rows Removed by Filter: 10221
>>>> Planning time: 0.176 ms
>>>> Execution time: 75.564 ms
>>>> (6 rows)
>>>>
>>>> Any ideas?
>>>>
>
> --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org
--
Best Regards,
Aaron Lewis - PGP: 0x13714D33 - http://pgp.mit.edu/
Finger Print: 9F67 391B B770 8FF6 99DC D92D 87F6 2602 1371 4D33
