Hi Stephan. Thank you for your quick reply.
Pursuant to your suggestions, I tried the following.
gds2-# \set seqscan off
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
VERSION = 'PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96'
DBNAME = 'gds2'
USER = 'djisgitt'
PORT = '5432'
ENCODING = 'SQL_ASCII'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
HISTSIZE = '500'
LASTOID = '0'
seqscan = 'off'
Sequential scan is obviously not off; how do you turn it off? I tried
enable_seqscan=off at the psql prompt, but it did not like that. Is that
a postgresql.conf variable?
Don
Stephan Szabo wrote:
>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)
>
>
>
