Thread: Index doesn't appear to be working.

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?  Thanks, I appreciate the help!

John Oakes

> 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?

Only if most of the students are failing ;)

Since most entries in this table are likely to be 'P', the optimizer
correctly concludes that it would be faster to sequentially scan the
table for rows. This is likely to be the optimizer choice for any
two-value column.

If the table has only a few rows (as might be true for a single class or
for a test dataset) then the optimizer will never choose an index scan.

If the table has more than a few hundred rows then you might get an
index scan if you search for students who are failing (again, assuming
that most are not). Make sure that you "vacuum analyze" after populating
the table to get correct statistics.

                      - Thomas