Re: Fulltext - multiple single column indexes

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: esemba <esemba(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Fulltext - multiple single column indexes
Date: 2009-03-20 10:01:18
Message-ID: Pine.LNX.4.64.0903201259370.31919@sn.sai.msu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marco Colombo 2009-03-20 12:17:35 Re: Maximum transaction rate
Previous Message Richard Huxton 2009-03-20 09:37:28 Re: Fulltext - multiple single column indexes