[GENERAL] Fulltext - multiple single column indexes
Hi,
I have table with several columns and need to perform fulltext search over
volatile number of columns.
I can't use multicolumn gist index or gin index over concatenated columns,
so I've created several single column indexes (one for each column I want to
search) and now I need to query them like this:
to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
coalesce(resume, '')) || ...
@@ to_tsquery('cs', 'Query text');
This query works, but EXPLAIN has shown me, that postgres doesn't use the
indexes, so the query over a table with several thousands of records last
very long time. I've figured out, that indexes probably cannot be used this
way. What is a recommendation for this scenario?
Indexes over static number of columns work fine, but I can't use them,
because in my application logic I want to let user choose which columns to
search.
Thank you for your reply.
--
View this message in context:
http://www.nabble.com/Fulltext---multiple-single-column-indexes-tp22611952p22611952.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fulltext - multiple single column indexes
Well, thank you both for response, but I'm not sure, I understand Oleg's
solution. This would work, but where is the variability of searched columns?
In your example, I create new indexed column with concatenated vectors of 2
columns. But I sometimes new to search only annotation, sometimes resume,
sometomes both.
Oleg Bartunov wrote:
>
> On Thu, 19 Mar 2009, esemba wrote:
>
>>
>> Hi,
>> I have table with several columns and need to perform fulltext search
>> over
>> volatile number of columns.
>> I can't use multicolumn gist index or gin index over concatenated
>> columns,
>> so I've created several single column indexes (one for each column I want
>> to
>> search) and now I need to query them like this:
>>
>> to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
>> coalesce(resume, '')) || ...
>> @@ to_tsquery('cs', 'Query text');
>
> alter table YOURTABLE add columnt fts tsvector;
> update YOURTABLE set fts=
> to_tsvector('cs', coalesce(annotation, '')) ||
> to_tsvector('cs', coalesce(resume, '')) || ...
> create index fts_idx on YOURTABLE using gin(fts);
> vacuum analyze YOURTABLE;
> select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;
>
>
>>
>> This query works, but EXPLAIN has shown me, that postgres doesn't use the
>> indexes, so the query over a table with several thousands of records last
>> very long time. I've figured out, that indexes probably cannot be used
>> this
>> way. What is a recommendation for this scenario?
>> Indexes over static number of columns work fine, but I can't use them,
>> because in my application logic I want to let user choose which columns
>> to
>> search.
>>
>> Thank you for your reply.
>>
>
> Regards,
> Oleg
> _
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
--
View this message in context:
http://www.nabble.com/Fulltext---multiple-single-column-indexes-tp22611952p22617663.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fulltext - multiple single column indexes
Thanks, this works quite fine, but I've ran into some problems so far:
- It's not possible to specify more than 4 labels (just ABCD)
- In query I have to specify searched vectors for each lexem. I think It
would be better to specify searched vectors per-query.
Oleg Bartunov wrote:
>
> On Fri, 20 Mar 2009, esemba wrote:
>
>>
>> Well, thank you both for response, but I'm not sure, I understand Oleg's
>> solution. This would work, but where is the variability of searched
>> columns?
>> In your example, I create new indexed column with concatenated vectors of
>> 2
>> columns. But I sometimes new to search only annotation, sometimes resume,
>> sometomes both.
>
>
> if you assign different labels to the concatenated columns, you can
> specify in query which columns you're interested in. Also, you
> can explicitly specify weight=0 for columns you're not interested.
>
>>
>>
>> Oleg Bartunov wrote:
>>>
>>> On Thu, 19 Mar 2009, esemba wrote:
>>>
>>>>
>>>> Hi,
>>>> I have table with several columns and need to perform fulltext search
>>>> over
>>>> volatile number of columns.
>>>> I can't use multicolumn gist index or gin index over concatenated
>>>> columns,
>>>> so I've created several single column indexes (one for each column I
>>>> want
>>>> to
>>>> search) and now I need to query them like this:
>>>>
>>>> to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
>>>> coalesce(resume, '')) || ...
>>>> @@ to_tsquery('cs', 'Query text');
>>>
>>> alter table YOURTABLE add columnt fts tsvector;
>>> update YOURTABLE set fts=
>>> to_tsvector('cs', coalesce(annotation, '')) ||
>>> to_tsvector('cs', coalesce(resume, '')) || ...
>>> create index fts_idx on YOURTABLE using gin(fts);
>>> vacuum analyze YOURTABLE;
>>> select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;
>>>
>>>
>>>>
>>>> This query works, but EXPLAIN has shown me, that postgres doesn't use
>>>> the
>>>> indexes, so the query over a table with several thousands of records
>>>> last
>>>> very long time. I've figured out, that indexes probably cannot be used
>>>> this
>>>> way. What is a recommendation for this scenario?
>>>> Indexes over static number of columns work fine, but I can't use them,
>>>> because in my application logic I want to let user choose which columns
>>>> to
>>>> search.
>>>>
>>>> Thank you for your reply.
>>>>
>>>
>>> Regards,
>>> Oleg
>>> _
>>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
>>> Sternberg Astronomical Institute, Moscow University, Russia
>>> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
>>> phone: +007(495)939-16-83, +007(495)939-23-83
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>>
>>
>
> Regards,
> Oleg
> _
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
--
View this message in context:
http://www.nabble.com/Fulltext---multiple-single-column-indexes-tp22611952p22627255.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fulltext - multiple single column indexes
Ok, so what solution for the 4 column limitation would you suggest? I'll
probably create two four-column indexes and OR search over them.
Oleg Bartunov wrote:
>
> On Fri, 20 Mar 2009, esemba wrote:
>
>>
>> Thanks, this works quite fine, but I've ran into some problems so far:
>> - It's not possible to specify more than 4 labels (just ABCD)
>
> this is well known limitation and we are certainly should think about it
>
>> - In query I have to specify searched vectors for each lexem. I think It
>> would be better to specify searched vectors per-query.
>
> we provide low level interface, it's up to you to write your very own
> query processing.
>
>>
>>
>> Oleg Bartunov wrote:
>>>
>>> On Fri, 20 Mar 2009, esemba wrote:
>>>
>>>>
>>>> Well, thank you both for response, but I'm not sure, I understand
>>>> Oleg's
>>>> solution. This would work, but where is the variability of searched
>>>> columns?
>>>> In your example, I create new indexed column with concatenated vectors
>>>> of
>>>> 2
>>>> columns. But I sometimes new to search only annotation, sometimes
>>>> resume,
>>>> sometomes both.
>>>
>>>
>>> if you assign different labels to the concatenated columns, you can
>>> specify in query which columns you're interested in. Also, you
>>> can explicitly specify weight=0 for columns you're not interested.
>>>
>>>>
>>>>
>>>> Oleg Bartunov wrote:
>>>>>
>>>>> On Thu, 19 Mar 2009, esemba wrote:
>>>>>
>>>>>>
>>>>>> Hi,
>>>>>> I have table with several columns and need to perform fulltext search
>>>>>> over
>>>>>> volatile number of columns.
>>>>>> I can't use multicolumn gist index or gin index over concatenated
>>>>>> columns,
>>>>>> so I've created several single column indexes (one for each column I
>>>>>> want
>>>>>> to
>>>>>> search) and now I need to query them like this:
>>>>>>
>>>>>> to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
>>>>>> coalesce(resume, '')) || ...
>>>>>> @@ to_tsquery('cs', 'Query text');
>>>>>
>>>>> alter table YOURTABLE add columnt fts tsvector;
>>>>> update YOURTABLE set fts=
>>>>> to_tsvector('cs', coalesce(annotation, '')) ||
>>>>> to_tsvector('cs', coalesce(resume, '')) || ...
>>>>> create index fts_idx on YOURTABLE using gin(fts);
>>>>> vacuum analyze YOURTABLE;
>>>>> select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;
>>>>>
>>>>>
>>>>>>
>>>>>> This query works, but EXPLAIN has shown me, that postgres doesn't use
>>>>>> the
>>>>>> indexes, so the query over a table with several thousands of records
>>>>>> last
>>>>>> very long time. I've figured out, that indexes probably cannot be
>>>>>> used
>>>>>> this
>>>>>> way. What is a recommendation for this scenario?
>>>>>> Indexes over static number of columns work fine, but I can't use
>>>>>> them,
>>>>>> because in my application logic I want to let user choose which
>>>>>> columns
>>>>>> to
>>>>>> search.
>>>>>>
>>>>>> Thank you for your reply.
>>>>>>
>>>>>
>>>>> Regards,
>>>>> Oleg
>>>>> _
>>>>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
>>>>> Sternberg Astronomical Institute, Moscow University, Russia
>>>>> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
>>>>> phone: +007(495)939-16-83, +007(495)939-23-83
>>>>>
>>>>> --
>>>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>>>
>>>>>
>>>>
>>>>
>>>
>>> Regards,
>>> Oleg
>>> _
>>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
>>> Sternberg Astronomical Institute, Moscow University, Russia
>>> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
>>> phone: +007(495)939-16-83, +007(495)939-23-83
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>>
>>
>
> Regards,
> Oleg
> _
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
--
View this message in context:
http://www.nabble.com/Fulltext---multiple-single-column-indexes-tp22611952p22633855.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [fulltext] GIN indexes - OR search
Is it possible to have 2 gin indexes and OR the search of them? Problem is, I need to search 5 columns and there is limitation of only 4 weights, so I want to have one index of 4 vectors and second index of 1 vector and need to OR the search. When I try to search them separately, or use AND, it works, but for OR EXPLAIN ANALYZE shows me, it does seq scan. Thank you. -- View this message in context: http://www.nabble.com/-fulltext--GIN-indexes---OR-search-tp22781900p22781900.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [fulltext]Gin index full scan
I've a table with tsvector column named meta_vector. Over this column there
is a gin index. When I execute query like:
select id from theses where meta_vector @@@ to_tsquery('cs', 'foo | (!bar)')
I get an errror message: query requires full scan, which is not supported by
GIN indexes.
The weird thing is, that when I drop the index, or disable index scans, it
works. Why can't the planner just use full scans instead of index scans on
such the queries? Thanks for help.
Lukas
--
View this message in context:
http://www.nabble.com/-fulltext-Gin-index-full-scan-tp23482754p23482754.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
