Thread: Fulltext - multiple single column indexes

Fulltext - multiple single column indexes

From

esemba

Date:

20 March 2009, 00:07:45

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.

Re: Fulltext - multiple single column indexes

From

Richard Huxton

Date:

20 March 2009, 09:37:44

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');
>
> This query works, but EXPLAIN has shown me, that postgres doesn't use the
> indexes
[snip]

You're right in concluding this isn't really going to work. You could
have separate indexes for each column and check them all:

  SELECT ... WHERE col1 @@ ... OR col2 @@ ...

Where it thinks it is sensible, PG should use a bitmap and combine the
different index scans. If you already have single-column indexes this
makes a lot of sense.

Alternatively, you could add a fulltext_blocks table with a "source"
column and keep it up to date via triggers. That way you could search
something like:

SELECT some_id FROM fulltext_blocks WHERE words @@ ... AND source IN
('col11', 'col2');

This is more effort, but has the advantage that you can add scores to
each column if you require. It also lets you be really clever and say to
users "you searched for 'foo' on columns 1,2,3 - no matches. There are
matches on other columns - show you these?"

HTH
--
  Richard Huxton
  Archonet Ltd

Re: Fulltext - multiple single column indexes

From

Oleg Bartunov

Date:

20 March 2009, 10:01:39

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

Re: Fulltext - multiple single column indexes

From

esemba

Date:

20 March 2009, 13:45:16

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

Re: Fulltext - multiple single column indexes

From

esemba

Date:

20 March 2009, 13:45:40

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

Re: Fulltext - multiple single column indexes

From

esemba

Date:

20 March 2009, 13:46:11

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

Re: Fulltext - multiple single column indexes

From

Oleg Bartunov

Date:

20 March 2009, 17:44:14

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

Re: Fulltext - multiple single column indexes

From

esemba

Date:

20 March 2009, 19:25:20

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.

Re: Fulltext - multiple single column indexes

From

Oleg Bartunov

Date:

21 March 2009, 07:42:46

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

Re: Fulltext - multiple single column indexes

From

esemba

Date:

21 March 2009, 08:40:48

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.