Re: Lack of use of indexes

Thank you, Tom, for your reply.

As usual (from my observation of this newsgroup), you are quite correct,
as was Stephan. To wit,

gds2=# explain select * from lg where section=14;
NOTICE:  QUERY PLAN:

Seq Scan on lg  (cost=0.00..5885.77 rows=3836 width=73)

EXPLAIN
gds2=# set enable_seqscan=off;
SET VARIABLE
gds2=# explain select * from lg where section=14;
NOTICE:  QUERY PLAN:

Index Scan using lgsec on lg  (cost=0.00..12167.45 rows=3836 width=73)

EXPLAIN

I am surprised at the low cutoff percentage, but it is nice to know for
future reference.

Thank you also to Doug and Stephan for your help. I remain quite
pleasantly amazed at the quality of the software and of the support.

Don


Tom Lane wrote:

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