Is indexing broken for bigint columns?

Dann Corbit wrote:

http://www.phpbuilder.com/columns/smith20010821.php3?page=3

bigint indexes work fine. The queries probably referenced 32-bit
integer constants that were neither quoted nor CAST. I always start
bigint sequences at 5 billion. This ensures that client applications
aren't assuming 32-bit quantities that will break once ~4.2 billion
is reached and I get index scans without quoting or casting free.
But IIRC there's a change in the development tree to jettison the
requirement for quoting/casting...

Mike Mascari

-----Original Message-----
From: Mike Mascari [mailto:mascarm@mascari.com]
Sent: Tuesday, February 24, 2004 3:27 PM
To: Dann Corbit
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Is indexing broken for bigint columns?

Dann Corbit wrote:

http://www.phpbuilder.com/columns/smith20010821.php3?page=3

bigint indexes work fine. The queries probably referenced 32-bit
integer constants that were neither quoted nor CAST. I always start
bigint sequences at 5 billion. This ensures that client applications
aren't assuming 32-bit quantities that will break once ~4.2 billion
is reached and I get index scans without quoting or casting free.
But IIRC there's a change in the development tree to jettison the
requirement for quoting/casting...

I think it would be awfully nice for "conversions that make sense" to
happen implicitly.
Including for function calls.
Including comparison operators.
Etc.

I have had to write a ton of work-around stuff and I still keep finding
holes in it.

-----Original Message-----
From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
Sent: Tuesday, February 24, 2004 3:33 PM
To: Dann Corbit
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Is indexing broken for bigint columns?

Dann,

Did you mean to forward this to -hackers?

Yes. Was my posting inappropriate?

Is there a way to reply on that site? (My lynx didn't show me
a link that looked likely in the neighbourhood of that glib comment).

Scroll to the bottom, and there is a response form.

Dann Corbit wrote:

http://www.phpbuilder.com/columns/smith20010821.php3?page=3

http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-INT

-----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?

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

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.

-----Original Message-----
From: Mike Mascari [mailto:mascarm@mascari.com]
Sent: Tuesday, February 24, 2004 4:37 PM
To: Dann Corbit
Cc: Peter Eisentraut; PostgreSQL-development
Subject: Re: [HACKERS] Is indexing broken for bigint columns?

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.

And yet they do. For example SQL*Server:

SET SHOWPLAN_ALL ON
go

SET SHOWPLAN_TEXT ON
go

select * from foo where bar = 1
go

|--Clustered Index Seek(OBJECT:([model].[dbo].[foo].[foobar]),
SEEK:([foo].[bar]=Convert([@1])) ORDERED FORWARD)

And MySQL:
mysql> explain select * from foo where bar = 1;
+-------+-------+---------------+--------+---------+-------+------+-----
--+
| table | type  | possible_keys | key    | key_len | ref   | rows |
Extra |
+-------+-------+---------------+--------+---------+-------+------+-----
--+
| foo   | const | foobar        | foobar |       9 | const |    1 |
|
+-------+-------+---------------+--------+---------+-------+------+-----
--+
1 row in set (0.03 sec)

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=2983
2.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%2Bpost
gresql

Happy days.