Re: Query tuning help

In article <7fc67646a961f5ebef90def7aeb95fd0(at)drivefaster(dot)net>,
Dan Harris <fbsd(at)drivefaster(dot)net> writes:

> On May 8, 2005, at 8:06 PM, Josh Berkus wrote:
>>
>>> If I were to use tsearch2 for full-text indexing, would I need to
>>> create another table that merges all of my recordtext rows into a
>>> single 'text' field type?
>>
>> No. Read the OpenFTS docs, they are fairly clear on how to set up
>> a simple
>> FTS index. (TSearch2 ~~ OpenFTS)
>>
>>> If so, this is where I run into problems, as
>>> my logic also needs to match multiple words in their original order.

> I have been reading the Tsearch2 docs and either I don't understand
> something or I'm not communicating my situation clearly enough. It
> seems that Tsearch2 has a concept of "document". And, in everything I
> am reading, they expect your "document" to be all contained in a
> single row. Since my words can be spread across multiple rows, I
> don't see that Tsearch2 will combine all 'recordtext' row values with
> the same "incidentid" into a single vector. Am I overlooking
> something in the docs?

AFAICS no, but you could create a separate table containing just the
distinct incidentids and the tsearch2 vectors of all recordtexts
matching that incidentid. This table would get updated solely by
triggers on the original table and would provide a fast way to get all
incidentids for RED and CORVETTE. The question is: would this reduce
the number of rows to check more than filtering on date?