Re: Fulltext - multiple single column indexes - Mailing list pgsql-general

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: oleg@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: oleg@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: oleg@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.