Re: Why is this query not using GIN index?

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(at)gmail(dot)com> wrote:
>>
>>
>> On Sun, Nov 13, 2016 at 2:50 PM, Aaron Lewis <the(dot)warl0ck(dot)1989(at)gmail(dot)com>
>> 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