To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Michael Nachbaur <mike@nachbaur.com>, <pgsql-sql@postgresql.org>
Subject: Re: Forcing query to use an index
References: <20030303140649.H42776-100000@megazone23.bigpanda.com>
In-Reply-To: <20030303140649.H42776-100000@megazone23.bigpanda.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 03 Mar 2003 20:26:59 -0500
Message-ID: <877kbfj50c.fsf@stark.dyndns.tv>
Lines: 28
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


> ->  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