Обсуждение: Bug #580: Optimizer uses seq scan only

Janko Richter (j.richter@wallstreet-develop.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

On Mon, 11 Feb 2002 pgsql-bugs@postgresql.org wrote:

> Janko Richter (j.richter@wallstreet-develop.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

You'll need to cast the value you're comparing with to int8
(12345::int8 or CAST(12345 as int8) should work).

pgsql-bugs@postgresql.org writes:
> CREATE TABLE test (
>   testid  int8 not null default 0,

> janko=# EXPLAIN SELECT * FROM test WHERE testid = 12345;

Try 12345::int8.  Yes, we know it's a pain in the neck.

            regards, tom lane