To: pgsql-performance@postgresql.org
From: Harald Fuchs <use_reply_to@protecting.net>
Subject: Re: Query tuning help
Date: 09 May 2005 13:39:30 +0200
Organization: Linux Private Site
Lines: 31
Message-ID: <pufywwvdal.fsf@srv.protecting.net>
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
	<200505091051.14689.mr-russ@pws.com.au>
	<6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
	<200505081906.47731.josh@agliodbs.com>
	<7fc67646a961f5ebef90def7aeb95fd0@drivefaster.net>
Reply-To: hf1122x@protecting.net
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7

In article <7fc67646a961f5ebef90def7aeb95fd0@drivefaster.net>,
Dan Harris <fbsd@drivefaster.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?