Thread

1. Re: Is indexing broken for bigint columns?

   Dann Corbit <dcorbit@connx.com> — 2004-02-25T00:20:34Z

   > -----Original Message-----
   > From: Peter Eisentraut [mailto:peter_e@gmx.net] 
   > Sent: Tuesday, February 24, 2004 3:38 PM
   > To: Dann Corbit; PostgreSQL-development
   > Subject: Re: [HACKERS] Is indexing broken for bigint columns?
   > 
   > 
   > Dann Corbit wrote:
   > > http://www.phpbuilder.com/columns/smith20010821.php3?page=3
   > 
   http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-INT
   
   PostgreSQL is the only database that requires casts to do an index
   lookup.
   
   This is SQL*Server syntax:
   ==============================================================
   drop table foo
   go
   
   create table foo (bar bigint)
   go
   
   insert into foo (bar) values (1)
   go
   
   insert into foo (bar) values (-9223372036854775808)
   go
   
   insert into foo (bar) values (9223372036854775807)
   go
   
   create unique clustered index foobar on foo(bar)
   go
   
   select * from foo where bar = 1
   Go
   -- Correctly returns a value of 1.
   ==============================================================
   This is Oracle syntax:
   ==============================================================
   SQL> drop table foo;
   
   Table dropped.
   
   SQL> 
   SQL> create table foo (bar number(19));
   
   Table created.
   
   SQL> 
   SQL> insert into foo (bar) values (1);
   
   1 row created.
   
   SQL> 
   SQL> insert into foo (bar) values (-9223372036854775808);
   
   1 row created.
   
   SQL> 
   SQL> insert into foo (bar) values (9223372036854775807);
   
   1 row created.
   
   SQL> 
   SQL> create unique index foobar on foo(bar);
   
   Index created.
   
   SQL> 
   SQL> select * from foo where bar = 1;
   
         BAR
   ---------
           1
   
   SQL> 
   SQL> 
   ==============================================================
   
   DB/2 uses bigint like SQL*Server and PostgreSQL and necessary
   conversions are implicit.
   Sybase and Rdb also use bigint types.
   
   And now, here is the unkindest cut of all:
   
   mysql> create table foo (bar bigint);
   Query OK, 0 rows affected (0.01 sec)
   
   mysql>
   mysql> insert into foo (bar) values (1);
   Query OK, 1 row affected (0.00 sec)
   
   mysql>
   mysql> insert into foo (bar) values (-9223372036854775808);
   Query OK, 1 row affected (0.00 sec)
   
   mysql>
   mysql> insert into foo (bar) values (9223372036854775807);
   Query OK, 1 row affected (0.02 sec)
   
   mysql>
   mysql> create unique index foobar on foo(bar);
   Query OK, 3 rows affected (0.02 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   
   mysql>
   mysql> select * from foo where bar = 1;
   +------+
   | bar  |
   +------+
   |    1 |
   +------+
   1 row in set (0.00 sec)
   
   And (prattling on) if this is necessary for PostgreSQL:
   select * from foo where bar = 1::bigint;
   
   Why wouldn't this be necessary:
   select * from foo where bar = 1::integer;
   For an integer column?
   
   

2. Re: Is indexing broken for bigint columns?

   Mike Mascari <mascarm@mascari.com> — 2004-02-25T00:37:14Z

   Dann Corbit wrote:
   
   > PostgreSQL is the only database that requires casts to do an index
   > lookup.
   
   Possibly (quite probably) true, but you don't show any evidence that 
   SQL*Server, Oracle, or MySQL uses indexes either. Like I said 
   before, Tom (of course) already has a fix is already in the 
   development branch:
   
   http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=29832.1068682253%40sss.pgh.pa.us&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26scoring%3Dd%26q%3Dbigint%2Bindex%2Bhackers%2Bpostgresql
   
   > 
   > This is SQL*Server syntax:
   > ==============================================================
   ...
   > select * from foo where bar = 1
   ...
   > This is Oracle syntax:
   > ==============================================================
   > SQL> select * from foo where bar = 1;
   ...
   > mysql> select * from foo where bar = 1;
   
   Mike Mascari
   
   
   
   
   

3. Re: Is indexing broken for bigint columns?

   Peter Eisentraut <peter_e@gmx.net> — 2004-02-25T00:38:31Z

   Dann Corbit wrote:
   > > Dann Corbit wrote:
   > > > http://www.phpbuilder.com/columns/smith20010821.php3?page=3
   >
   > http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-INT
   >
   > PostgreSQL is the only database that requires casts to do an index
   > lookup.
   
   This issue has been discussed on these mailing lists literally dozens of 
   times.  If you're interested in the details, please see the archives.  
   Further discussion will hopefully not be necessary, because 7.5 will 
   fix it.