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. Changing the key to a 32bit (int4) one will result in the _pkey index being used (after a vacuum -analyze is ran). This results in a huge performance hit when using 64bit foreign keys and referential integrity checks when the data volumes are large (>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