Don Isgitt <djisgitt@soundenergy.com> writes:
> gds2=# select count(*) from lg;
> count
> --------
> 138459
> (1 row)
> gds2=# explain select * from lg where state='NM';
> NOTICE: QUERY PLAN:
> Seq Scan on lg (cost=0.00..5885.77 rows=14890 width=73)
An indexscan is usually a poor choice for retrieving 10% of the data in
a table (unless the index order and physical order are highly
correlated, as for instance after a CLUSTER command).
If you don't think the planner is guessing correctly here, you can force
an indexscan (do "set enable_seqscan = off") ... but I'll bet it gets
slower.
For randomly-ordered rows the cutoff point for indexscan effectiveness
is surprisingly low --- typically around 1% of the rows.
regards, tom lane