Bug #580: Optimizer uses seq scan only

Janko Richter (j(dot)richter(at)wallstreet-develop(dot)de) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Optimizer uses seq scan only

Long Description
Dear Postgresql team,

I've found an optimizer bug as follow:

At first I installed PG 7.2 from source followed by
initdb and create database. No changes in postgresql.conf are made.

After CREATE DATABASE I created a simple table :
CREATE TABLE test (
testid int8 not null default 0,
name varchar(255) ,
primary key (testid)
);
Now I inserted 500.000 records and I did VACUUM ANALYSE.

And now a simple query :
janko=# EXPLAIN SELECT * FROM test WHERE testid = 12345;
NOTICE: QUERY PLAN:

Seq Scan on stocks (cost=100000000.00..100009926.99 rows=1 width=23)

Why POSTGRESQL doesn't use index scan (500000 records!)?

And now as opposition :
CREATE TABLE test2 ( testid int8 primary key);
INSERT INTO test2 VALUES (12345);
VACUUM ANALYSE test2;
EXPLAIN SELECT * FROM test t1 INNER JOIN test2 t2 ON t1.testid=t2.testid;

The result:
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..7.70 rows=1 width=31)
-> Index Scan using test2_pkey on test2 t2 (cost=0.00..4.68 rows=1 width=8)
-> Index Scan using test_pkey on test t1 (cost=0.00..3.01 rows=1 width=23)

The "same" query but much faster !!

Bye : Janko

Sample Code

No file was uploaded with this report