Re: [SQL] Optimizing Query

[Justin Long]

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... would there be any benefit to doing a vacuum analyze nightly?

Justin Long

At 11:10 PM 3/5/2001 -0500, you wrote:

Justin Long <[EMAIL PROTECTED]> writes:
> Ok, now I have another question... it doesn't seem to be accessing the index.

> explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
> WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and
> w1.wordid=85369))

> NOTICE:  QUERY PLAN:

> Merge Join  (cost=32339.30..35496.97 rows=19262538 width=24)
>    ->  Merge Join  (cost=16530.24..16668.77 rows=233274 width=20)
>          ->  Sort  (cost=15809.06..15809.06 rows=8257 width=4)
>                ->  Seq Scan on kbwords w1  (cost=0.00..15271.85 rows=8257
> width=4)
>          ->  Sort  (cost=721.18..721.18 rows=2825 width=16)
>                ->  Seq Scan on knowledge k  (cost=0.00..559.25 rows=2825
> width=16)
>    ->  Sort  (cost=15809.06..15809.06 rows=8257 width=4)
>          ->  Seq Scan on kbwords w0  (cost=0.00..15271.85 rows=8257 width=4)

> Note the sequential scans... there is a wordindex where w0.wordid=42743...
> why isn't it doing an indexscan? wouldn't that be more efficient?

It probably thinks not, because the estimated number of hits (8257) is
so high.  That estimate is currently driven by the frequency of the most
common value in the column (mainly because that's the only stat we have
:-().  I am guessing that you have a few very common words, which are
skewing the stats for kbwords and causing it not to pick an indexscan.

Does your setup have a notion of "stop words" that shouldn't be indexed,
like "a", "an", "the", etc?  Perhaps you need to add such a feature, or
throw in a few more stopwords if you already have 'em.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

____________________________________________________________________
Justin Long                                     Network for Strategic Missions
[EMAIL PROTECTED] 1732 South Park Court
http://www.strategicnetwork.org Chesapeake, VA 23320, USA
Reality Check e-zine: [EMAIL PROTECTED]
____________________________________________________________________
Law: Never retreat. Never surrender. Never cut a deal with a dragon.
Corollary: No armor? Unclean life? Then do not mess in the affairs
of dragons, for you are crunchy and taste good with ketchup.