Re: Fulltext - multiple single column indexes

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