Skip site navigation (1) Skip section navigation (2)

Re: Is indexing broken for bigint columns?

From: "Dann Corbit" <DCorbit(at)connx(dot)com>
To: "Mike Mascari" <mascarm(at)mascari(dot)com>
Cc: "Peter Eisentraut" <peter_e(at)gmx(dot)net>,"PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Is indexing broken for bigint columns?
Date: 2004-02-25 00:50:23
Message-ID: D90A5A6C612A39408103E6ECDD77B829408D10@voyager.corporate.connx.com (view raw or flat)
Thread:
Lists: pgsql-hackers

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

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-02-25 03:35:04
Subject: Re: select statement against pg_stats returns inconsistent data
Previous:From: Joe ConwayDate: 2004-02-25 00:38:31
Subject: Re: select statement against pg_stats returns inconsistent

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group