Re: Full text search on a complex schema - a classic problem?

On 05/23/10 18:03, Andy Colson wrote:
> On 05/22/2010 09:40 PM, Ivan Voras wrote:
>> Hello,
>>
>> I have a schema which tracks various pieces of information which would
>> need to be globally searchable. One approach I came up with to make all
>> of the data searchable is to create a view made of UNION ALL queries
>> that would integrate different tables into a common structure which
>> could be uniformly queried by using tsearch2 functions. This would work,
>> up to the point where it would be practically unavoidable (for
>> performance reasons) to create indexes on this view, which cannot be
>> done. I would like to avoid using a "hand-made" materialized view (via
>> triggers, etc.) because of administrative overhead and because it would
>> duplicate data, of which there is potentially a lot.
>>
>> I think this looks like a fairly common problem with full text searches
>> on a large-ish schemas, so I'm wondering what are the best practices
>> here, specifically with using tsearch2?
>>
>
> I have something like this, but with PostGIS layers. When a person
> clicks I search all the different layers (each a table) for
> information. I use a stored proc. Each table has its own index so each
> table is fast. It also lets me abstract out differences between the
> layers (I can search each a little differently).
>
> If each of your tables had its own full text fields and indexes, then
> write a stored proc to search them all individually, it should be pretty
> quick.

This looks like an interesting solution. And it could be done
generically in our case by having a separate table describing which
tables need to be searched and by what fields.