От: Harald Fuchs
Тема: Re: Query tuning help
Дата: ,
Msg-id: pufywwvdal.fsf@srv.protecting.net
(см: обсуждение, исходный текст)
Ответ на: Query tuning help  (Dan Harris)
Список: pgsql-performance

Скрыть дерево обсуждения

Query tuning help  (Dan Harris, )
 Re: Query tuning help  (Josh Berkus, )
 Re: Query tuning help  (Russell Smith, )
  Re: Query tuning help  (Tom Lane, )
  Re: Query tuning help  (Dan Harris, )
   Re: Query tuning help  (Josh Berkus, )
    Re: Query tuning help  (Dan Harris, )
     Re: Query tuning help  (Klint Gore, )
   Re: Query tuning help  (Tom Lane, )
   Re: Query tuning help  (Russell Smith, )
    Re: Query tuning help  (Dan Harris, )
    Re: Query tuning help  (Mischa Sandberg, )
 Re: Query tuning help  (Harald Fuchs, )
 Re: Query tuning help  (Ulrich Wisser, )

In article <>,
Dan Harris <> 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?


В списке pgsql-performance по дате сообщения:

От: Mischa Sandberg
Дата:
Сообщение: Re: Query tuning help
От: Derek Buttineau|Compu-SOLVE
Дата:
Сообщение: Re: ORDER BY Optimization