Thread

1. PostgreSQL BugTool Submission

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-08-23T21:53:13Z

   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
   
   
   

2. Re: PostgreSQL BugTool Submission

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-24T03:52:15Z

   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