From: Dan Harris <fbsd@drivefaster.net>
To: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
Date: Sun, 8 May 2005 20:31:38 -0600
Message-ID: <7fc67646a961f5ebef90def7aeb95fd0@drivefaster.net> (raw)
In-Reply-To: <200505081906.47731.josh@agliodbs.com>
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
<200505091051.14689.mr-russ@pws.com.au>
<6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
<200505081906.47731.josh@agliodbs.com>
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?
>
> I'm doing something fairly similar on one of my projects and it works
> very
> well.
>
I'd be curious what similarities they have? Is it the searching across
multiple rows or the order of words?
> The limitations on TSearch2 indexes are:
> 1) they are expensive to update, so your data loads would be noticably
> slower.
> 2) they are only fast when cached in RAM (and when cached, are *very*
> fast).
> So if you have a variety of other processes that tend to fill up RAM
> between
> searches, you may find them less useful.
> 3) You have to create a materialized index column next to recordtext,
> which
> will increase the size of the table.
Duly noted. If this method can search across rows, I'm willing to
accept this overhead for the speed it would add.
In the meantime, is there any way I can reach my goal without Tsearch2
by just restructuring my query to narrow down the results by date
first, then seq scan for the 'likes'?
-Dan
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: fbsd@drivefaster.net
Subject: Re: Query tuning help
In-Reply-To: <7fc67646a961f5ebef90def7aeb95fd0@drivefaster.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