Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 0D6D5475425 for ; Sun, 3 Mar 2002 02:50:05 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 029AFD600; Sat, 2 Mar 2002 23:50:03 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id E65FC5BFB; Sat, 2 Mar 2002 23:50:03 -0800 (PST) Date: Sat, 2 Mar 2002 23:50:03 -0800 (PST) From: Stephan Szabo To: John Oakes Cc: Subject: Re: Index doesn't appear to be working. In-Reply-To: <001801c1c24a$56369270$cb842341@john> Message-ID: <20020302234538.Y58150-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200203/24 X-Sequence-Number: 6906 On Sat, 2 Mar 2002, John Oakes wrote: > I have a table with a column titled 'passfail' that only contains either a P > for pass or an F for fail. The table name is 'one'. I created the index on > the table with: > > CREATE INDEX one_passfail_idx ON one USING btree (passfail); > > I then do: > > VACUUM ANALYZE one; > > Then I do an explain on this query: > > SELECT * FROM one where passfail = 'P'; > > and it tells me: > > Seq Scan on one (cost=0.00..263.02 rows=5613 width=56) > > Shouldn't it tell me > > Index Scan using one_passfail_idx on one? > > Why isn't it using the index? This query returns about 5,600 of 10,000 > records. It is faster to just do a seq scan because it returns such a high > percentage or records? Thanks, I appreciate the help! Usually the seq scan will be faster in this kind of situation. Because the system needs to load the matching rows anyway, it's likely to result in reading all the blocks of the table and paying a penalty for seeking around the file.