Re: Is indexing broken for bigint columns?

> -----Original Message-----
> From: Mike Mascari [mailto:mascarm(at)mascari(dot)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([(at)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.