From: Greg Stark <gsstark@mit.edu>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Michael Nachbaur <mike@nachbaur.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Forcing query to use an index
Date: 03 Mar 2003 20:26:59 -0500
Message-ID: <877kbfj50c.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <20030303140649.H42776-100000@megazone23.bigpanda.com>
References: <20030303140649.H42776-100000@megazone23.bigpanda.com>
> -> Merge Join (cost=6106.42..6335.30 rows=2679 width=265)
(actual time=859.77..948.06 rows=1 loops=1)
Actually another problem, notice the big discrepancy between the estimated row
and the actual rows. That's because you have the big OR clause so postgres
figures there's a good chance one of the clauses will be true so it estimates
a lot of rows will match. In fact of course they're all very selective and
you'll usually probably only get a few records.
If you're stuck with the unanchored text search it will always do a full table
scan so it will never be lightening fast. But it would probably be a bit
faster if you put a limit clause (on a subquery) on the table that's doing the
full table scan.
That will convince postgres that there won't be thousands of resulting
records, which might convince it to do a nested loop.
Also, as a beneficial side effect will also limit the damage if one your users
does a search for "e"...
This only really helps if you can get rid of the OR CMS.package clause...
otherwise it actually needs all the records in case they match a summary
record with a kate package.
--
greg
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-sql@postgresql.org
Cc: gsstark@mit.edu, sszabo@megazone23.bigpanda.com, mike@nachbaur.com
Subject: Re: Forcing query to use an index
In-Reply-To: <877kbfj50c.fsf@stark.dyndns.tv>
* 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