From: Harald Fuchs <use_reply_to@protecting.net>
To: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
Date: 09 May 2005 13:39:30 +0200
Message-ID: <pufywwvdal.fsf@srv.protecting.net> (raw)
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
<200505091051.14689.mr-russ@pws.com.au>
<6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
<200505081906.47731.josh@agliodbs.com>
<7fc67646a961f5ebef90def7aeb95fd0@drivefaster.net>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: use_reply_to@protecting.net
Subject: Re: Query tuning help
In-Reply-To: <pufywwvdal.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox