From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Long <justinlong@strategicnetwork.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Optimizing Query
Date: Mon, 05 Mar 2001 23:10:55 -0500
Message-ID: <4315.983851855@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, justinlong@strategicnetwork.org
Subject: Re: Optimizing Query
In-Reply-To: <4315.983851855@sss.pgh.pa.us>
References: <20010305224029.A22983@ilse.nl> <5.0.2.1.0.20010305165854.03114e48@mail.strategicnetwork.org> <4315.983851855@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox