Re: Lack of use of indexes

Don Isgitt <djisgitt(at)soundenergy(dot)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