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

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: 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-tp22611952p22627255.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.