Re: Optimizing Query - Mailing list pgsql-sql

Wow. I can't believe the difference. It didn't take too long. I'll set up a script in my etc/cron.weekly to run it...
wouldthere be any benefit to doing a vacuum analyze nightly?<br /><br /> Justin Long<br /><br /> At 11:10 PM 3/5/2001
-0500,you wrote:<br /><blockquote cite="cite" class="cite" type="cite">Justin Long
<justinlong@strategicnetwork.org>writes:<br /> > Ok, now I have another question... it doesn't seem to be
accessingthe index.<br /><br /> > explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1 <br />
>WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and <br /> > w1.wordid=85369))<br /><br /> >
NOTICE: QUERY PLAN:<br /><br /> > Merge Join  (cost=32339.30..35496.97 rows=19262538 width=24)<br /> >    -> 
MergeJoin  (cost=16530.24..16668.77 rows=233274 width=20)<br /> >          ->  Sort  (cost=15809.06..15809.06
rows=8257width=4)<br /> >                ->  Seq Scan on kbwords w1  (cost=0.00..15271.85 rows=8257 <br /> >
width=4)<br/> >          ->  Sort  (cost=721.18..721.18 rows=2825 width=16)<br /> >                ->  Seq
Scanon knowledge k  (cost=0.00..559.25 rows=2825 <br /> > width=16)<br /> >    ->  Sort 
(cost=15809.06..15809.06rows=8257 width=4)<br /> >          ->  Seq Scan on kbwords w0  (cost=0.00..15271.85
rows=8257width=4)<br /><br /> > Note the sequential scans... there is a wordindex where w0.wordid=42743... <br />
>why isn't it doing an indexscan? wouldn't that be more efficient?<br /><br /> It probably thinks not, because the
estimatednumber of hits (8257) is<br /> so high.  That estimate is currently driven by the frequency of the most<br />
commonvalue in the column (mainly because that's the only stat we have<br /> :-().  I am guessing that you have a few
verycommon words, which are<br /> skewing the stats for kbwords and causing it not to pick an indexscan.<br /><br />
Doesyour setup have a notion of "stop words" that shouldn't be indexed,<br /> like "a", "an", "the", etc?  Perhaps you
needto add such a feature, or<br /> throw in a few more stopwords if you already have 'em.<br /><br />
                        regards,tom lane<br /><br /> ---------------------------(end of
broadcast)---------------------------<br/> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org</blockquote><p><fontface="Courier New, Courier"><br />
____________________________________________________________________<br/> Justin
Long                                     Networkfor Strategic Missions<br /> justinlong@strategicnetwork.org 1732 South
ParkCourt<br /> http://www.strategicnetwork.org Chesapeake, VA 23320, USA<br /> Reality Check e-zine:
reality-check-subscribe@yahoogroups.com<br/> ____________________________________________________________________<br />
Law:Never retreat. Never surrender. Never cut a deal with a dragon.<br /> Corollary: No armor? Unclean life? Then do
notmess in the affairs <br /> of dragons, for you are crunchy and taste good with ketchup.<br /></font>