Re: Lack of use of indexes

On Fri, 22 Nov 2002, Don Isgitt wrote:

> 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)
>
> EXPLAIN
>
> gds2=# explain select * from lg where section=14;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on lg  (cost=0.00..5885.77 rows=3836 width=73)

My guess would be that if you turned off seq_scan (enable_seqscan=off)
and explained, you'd get a larger estimate for the cost of the index
scan.  Assuming those row estimates are correct and the width is around
73 and that the data isn't very clustered, it's probably guessing that
it's going to be reading most of the datafile anyway and so the sequence
scan is faster. If it gives a higher estimate, but a lower real time with
enable_seqscan=off your data might be more clustered than it seems to be
expecting or maybe the default cost for random page reads is higher than
necessary on your machine (there are some settings in postgresql.conf that
you can play with)