Thread: PostgreSQL BugTool Submission

Stu Coates (stu@stucoates.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Not performing index scan for 64bit primary

Long Description
When performing a query against a table that has a 64bit (int8) primary key a sequential scan always takes place.
Changingthe key to a 32bit (int4) one will result in the _pkey index being used (after a vacuum -analyze is ran). This
resultsin a huge performance hit when using 64bit foreign keys and referential integrity checks when the data volumes
arelarge (>1M rows). 

PostgreSQL version: 7.0.2 on powerpc-unknown-linux-gnu, compiled by gcc 2.95.2

Sample Code
Example code at: http://www.filesys.demon.co.uk/postgresBug.html


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> When performing a query against a table that has a 64bit (int8)
> primary key a sequential scan always takes place.

Possibly a casting issue.  Observe:

regression=# create table foo1 (f1 int8 primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo1_pkey' for table 'foo1'
CREATE
regression=# explain select * from foo1 where f1 = 42;
NOTICE:  QUERY PLAN:

Seq Scan on foo1  (cost=0.00..22.50 rows=10 width=8)

EXPLAIN
regression=# explain select * from foo1 where f1 = 42::int8;
NOTICE:  QUERY PLAN:

Index Scan using foo1_pkey on foo1  (cost=0.00..8.14 rows=10 width=8)

EXPLAIN

The planner is not currently very smart about figuring out whether a
cross-data-type operator (int8-vs-int4-equal, here) can be munged into
the single-data-type operator that's associated with an index.  An
explicit cast will prod it in the right direction.

We do plan to fix this, but there's still some debate about how...

            regards, tom lane