From: Josh Berkus <josh@agliodbs.com>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
Date: Sun, 8 May 2005 19:06:47 -0700
Message-ID: <200505081906.47731.josh@agliodbs.com> (raw)
In-Reply-To: <6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
<200505091051.14689.mr-russ@pws.com.au>
<6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
Dan,
> While I believe you, I'm confused by this line in my original EXPLAIN
>
> ANALYZE:
> >> -> Index Scan using ea1 on ea (cost=0.00..2736.43 rows=42 width=47)
> >> (actual time=2.085..2.309 rows=2 loops=473)
> >> Index Cond:
> >> ((ea.incidentid)::text = ("outer".incidentid)::text)
> >> Filter: (((recordtext)::text
> >> ~~ '%RED%'::text) OR ((recordtext)::text ~~ '%CORVETTE%'::text))
The index named is matching based on incidentid -- the join condition. The
"filter" is applied against the table rows, i.e. a scan.
> 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.
You do that by doubling up ... that is, use the FTS index to pick all rows
that contain "RED" and "CORVETTE", and then check the order. I'll also note
that your current query is not checking word order.
Example:
WHERE recordtext_fti @@ to_tsquery ('default', 'RED && CORVETTE')
AND recordtext LIKE '%RED%CORVETTE%'
I'm doing something fairly similar on one of my projects and it works very
well.
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.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, fbsd@drivefaster.net
Subject: Re: Query tuning help
In-Reply-To: <200505081906.47731.josh@agliodbs.com>
* 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