Re: optimizer ignoring primary key and doing sequence scan

Scott Marlowe ha scritto:
> On Mon, Jul 14, 2008 at 1:54 PM, Chris Hoy <chris(dot)hoy(at)hoyc(dot)fsnet(dot)co(dot)uk> wrote:
>> Hi
>>
>> I have a number of tables in my database where the queries appear to
>> ignoring the primary key and doing a seq scan instead, however other tables
>> appear to be fine. I can see any difference between them.
>>
>> Is their any way of determination why the otimizer isn't picking up the
>> primary key?
>>
>> Version 8.3.3 windows
>>
>> An example of a non working table is:
>>
>> select * from industries where industryid = 1;
>> "Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual
>> time=0.011..0.013 rows=1 loops=1)"
>
> According to this there's only one row in the table. why WOULD
> postgresql use an index when it can just scan the one row table in a
> split second.
>
I agree with you that it can depend on the size of the table but where
you can read that the table contains only one row?

I try with my table (39910 rows, no index on column note)
explain analyze select * from table where note='single example';

Seq Scan on table (cost=0.00..2458.88 rows=13 width=327) (actual
time=10.901..481.896 rows=1 loops=1)

On the postgres manual I can find "Estimated number of rows output by
this plan node (Again, only if executed to completion.)" regarding the
third parameter of the explain

Where is my error?

Edoardo