Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f264B6x00854 for ; Mon, 5 Mar 2001 23:11:06 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f264Au904318; Mon, 5 Mar 2001 23:10:56 -0500 (EST) To: Justin Long cc: pgsql-sql@postgresql.org Subject: Re: Optimizing Query In-reply-to: <5.0.2.1.0.20010305165854.03114e48@mail.strategicnetwork.org> References: <20010305224029.A22983@ilse.nl> <5.0.2.1.0.20010305165854.03114e48@mail.strategicnetwork.org> Comments: In-reply-to Justin Long message dated "Mon, 05 Mar 2001 16:59:47 -0500" Date: Mon, 05 Mar 2001 23:10:55 -0500 Message-ID: <4315.983851855@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/85 X-Sequence-Number: 1733 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