Обсуждение: 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. > On Tue, 24 Feb 2004, Dann Corbit wrote: > > > http://www.phpbuilder.com/columns/smith20010821.php3?page=3 > > > -- > Nigel Andrews > > >
> -----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: > > 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.
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
> -----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.
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Dann Corbit > Sent: Tuesday, February 24, 2004 4:21 PM > To: Peter Eisentraut; PostgreSQL-development > Subject: Re: [HACKERS] Is indexing broken for bigint columns? > > > > -----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 From the documentaion at the above link: "8.1.1. Integer Types The types smallint, integer, and bigint store whole numbers, that is, numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will result in an error. The type integer is the usual choice, as it offers the best balance between range, storage size, and performance. The smallint type is generally only used if disk space is at a premium. The bigint type should only be used if the integer range is not sufficient, because the latter is definitely faster. The bigint type may not function correctly on all platforms, since it relies on compiler support for eight-byte integers. On a machine without such support, bigint acts the same as integer (but still takes up eight bytes of storage). However, we are not aware of any reasonable platform where this is actually the case. SQL only specifies the integer types integer (or int) and smallint. The type bigint, and the type names int2, int4, and int8 are extensions, which are shared with various other SQL database systems. Note: If you have a column of type smallint or bigint with an index, you may encounter problems getting the system to use that index. For instance, a clause of the form ... WHERE smallint_column = 42 will not use an index, because the system assigns type integer to the constant 42, and PostgreSQL currently cannot use an index when two different data types are involved. A workaround is to single-quote the constant, thus: ... WHERE smallint_column = '42' This will cause the system to delay type resolution and will assign the right type to the constant." ======================================================================== ==================================== DRC Remark: How deliciously ironic that it will correctly convert a character string but not an integral type. Am I the only person who thinks that this is totally bizarre behavior?