Re: Index doesn't appear to be working.

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.