Received: from strategicnetwork.org (saharawi.strategicnetwork.org.207.181.216.in-addr.arpa [216.181.207.166] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f26EMtx90018 for ; Tue, 6 Mar 2001 09:22:55 -0500 (EST) (envelope-from justinlong@strategicnetwork.org) Received: (qmail 5991 invoked from network); 6 Mar 2001 14:32:37 -0000 Received: from unknown (HELO JUSTIN.strategicnetwork.org) (192.168.0.39) by ns.strategicnetwork.org with SMTP; 6 Mar 2001 14:32:37 -0000 Message-Id: <5.0.2.1.0.20010306091755.03723ea0@mail.strategicnetwork.org> X-Sender: justinlong@strategicnetwork.org@mail.strategicnetwork.org X-Mailer: QUALCOMM Windows Eudora Version 5.0.2 Date: Tue, 06 Mar 2001 09:18:41 -0500 To: Tom Lane From: Justin Long Subject: Re: Optimizing Query Cc: pgsql-sql@postgresql.org In-Reply-To: <4315.983851855@sss.pgh.pa.us> References: <5.0.2.1.0.20010305165854.03114e48@mail.strategicnetwork.org> <20010305224029.A22983@ilse.nl> <5.0.2.1.0.20010305165854.03114e48@mail.strategicnetwork.org> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_1018330511==_.ALT" X-Archive-Number: 200103/93 X-Sequence-Number: 1741 --=====================_1018330511==_.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed 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 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 majordomo@postgresql.org ____________________________________________________________________ Justin Long Network for Strategic Missions justinlong@strategicnetwork.org 1732 South Park Court http://www.strategicnetwork.org Chesapeake, VA 23320, USA Reality Check e-zine: reality-check-subscribe@yahoogroups.com ____________________________________________________________________ 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. --=====================_1018330511==_.ALT Content-Type: text/html; charset="us-ascii" 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 <justinlong@strategicnetwork.org> 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 majordomo@postgresql.org


____________________________________________________________________
Justin Long                                     Network for Strategic Missions
justinlong@strategicnetwork.org 1732 South Park Court
http://www.strategicnetwork.org Chesapeake, VA 23320, USA
Reality Check e-zine: reality-check-subscribe@yahoogroups.com
____________________________________________________________________
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.
--=====================_1018330511==_.ALT--