Received: from serv1.ilse.nl (serv1.ilse.nl [212.189.131.11]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f25N8qx60334 for ; Mon, 5 Mar 2001 18:08:53 -0500 (EST) (envelope-from mathijs@serv0.ilse.nl) Received: from serv0.ilse.nl (serv0.ilse.nl [212.189.131.10]) by serv1.ilse.nl (8.11.0/8.10.2) with ESMTP id f25N8oE37451; Tue, 6 Mar 2001 00:08:50 +0100 (MET) Received: (from mathijs@localhost) by serv0.ilse.nl (8.11.1/8.11.1) id f25N8oo24739; Tue, 6 Mar 2001 00:08:50 +0100 (CET) (envelope-from mathijs) Date: Tue, 6 Mar 2001 00:08:50 +0100 From: Mathijs Brands To: Justin Long Cc: pgsql-sql@postgresql.org Subject: Re: Optimizing Query Message-ID: <20010306000850.I22983@ilse.nl> References: <20010305224029.A22983@ilse.nl> <200103052145.QAA04808@candle.pha.pa.us> <5.0.2.1.0.20010305165854.03114e48@mail.strategicnetwork.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <5.0.2.1.0.20010305165854.03114e48@mail.strategicnetwork.org>; from justinlong@strategicnetwork.org on Mon, Mar 05, 2001 at 04:59:47PM -0500 X-Unexpected-Header: The Spanish Inquisition X-Archive-Number: 200103/77 X-Sequence-Number: 1725 On Mon, Mar 05, 2001 at 04:59:47PM -0500, Justin Long allegedly wrote: > 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? > > Justin Did you run the 'vacuum analyze' command on the tables concerned (or even better, the whole database)? Without the data this analysis provides psql cannot come up with a good execution plan and falls back to full table scans. Do a 'vacuum analyze' one a week to keep performance levels up. Cheers, Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum