Bugs in bigint indexes
<http://www.axur.com.br/images/axur_animado.gif>
_____
Dear developers,
I almost got nuts those two days I'm stuck with this issue...
Let's get straight to the point. I'm using a small portion of my actual
table, but this is enough.
CREATE SEQUENCE it_seq;
CREATE TABLE it_test (
id bigint not null primary key default
nextval('public.it_test_id_seq'::text)
);
explain SELECT id FROM it_test WHERE id=123;
Seq Scan on it_test (cost=0.0..22.50 rows=2 width=8)
Filter: (id=123)
(2 rows)
when the key is bigint, it ignores any kind of index scan, it always use
Seq scan. Why?
Changed the bigint for a regular int (I need a 64bit identificator for
my primary key) and it started working... see:
Index Scan using it_test_pkey on it_test (cost=0.00..4.82 rows=2
width=4)
Index Cond: (id=32)
(2 rows)
That's what I wanted to happen when a bigint fields are used.
I suspected that after some 100k rows the queries where slower, and
slower, and slower.
Please help me.. for the fix I'm using 32bits integer, but I believe the
rows will get over 2 billion registers.
BTW: Tryed postgresql 7.3.4 7.3.5, and 7.4. None worked.
Thank you very much, I would really apreciate a feedback.
Kindly,
Gustavo Scotti
Axur Information Security
Porto Alegre - RS - Brasil
Tel.: +55 (51) 3222 2874
www.axur.com.br <http://www.axur.com.br/>
As informações existentes nessa mensagem e no(s) arquivo(s) anexado(s)
são para uso restrito, sendo seu sigilo protegido por lei. Caso não seja
destinatário, saiba que leitura, divulgação ou cópia são proibidas.
Favor apagar as informações e notificar o remetente. O uso impróprio
será tratado conforme as normas da empresa e a legislação em vigor.
Agradecemos sua colaboração.
The information mentioned in this message and in the archives attached
are of restricted use, and its privacy is protected by law. If you are
not the addressee, be aware that reading, disclosure or copy are
forbidden. Please delete this information and notify the sender.
Inappropriate use will be tracted according to company's rules and valid
laws.
Thank you for your cooperation.
Attachments:
axur_animado.gifimage/gif; name=axur_animado.gifDownload
MessageTry this
explain SELECT id FROM it_test WHERE id='123';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using it_test_pkey on it_test (cost=0.00..4.82 rows=1 width=8)
Index Cond: (id = 123::bigint)
(2 rows)
The point is that 123 is naturally considered by Postgres to be an int4. If you want to specify it is an int8 then use quotes around your value or an explicit cast if the sought value is some kind of int4, int2 attribute.
The same apply for smallints.
----- Original Message -----
From: Gustavo Scotti
To: pgsql-bugs@postgresql.org
Sent: Wednesday, December 17, 2003 8:56 PM
Subject: [BUGS] Bugs in bigint indexes
I almost got nuts those two days I'm stuck with this issue...
Let's get straight to the point. I'm using a small portion of my actual table, but this is enough.
CREATE SEQUENCE it_seq;
CREATE TABLE it_test (
id bigint not null primary key default nextval('public.it_test_id_seq'::text)
);
explain SELECT id FROM it_test WHERE id=123;
Seq Scan on it_test (cost=0.0..22.50 rows=2 width=8)
Filter: (id=123)
(2 rows)
Gustavo Scotti writes:
when the key is bigint, it ignores any kind of index scan, it always use
Seq scan. Why?
RTFM: http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-INT
Please don't post to pgsql-bugs unless you have found a bug.
On Wed, 17 Dec 2003, Gustavo Scotti wrote:
<http://www.axur.com.br/images/axur_animado.gif>
_____Dear developers,
I almost got nuts those two days I'm stuck with this issue...
Let's get straight to the point. I'm using a small portion of my actual
table, but this is enough.CREATE SEQUENCE it_seq;
CREATE TABLE it_test (
id bigint not null primary key default
nextval('public.it_test_id_seq'::text)
);explain SELECT id FROM it_test WHERE id=123;
Seq Scan on it_test (cost=0.0..22.50 rows=2 width=8)
Filter: (id=123)
(2 rows)when the key is bigint, it ignores any kind of index scan, it always use
Seq scan. Why?
Try casting id to bigint, like so:
explain SELECT id from it_test WHERE id = 123::bigint;
(You need to do this for smallints as well).
Why doesn't the query planner notice that an int-type index is present
and perform the cast on its own? Perhaps one of the developers
can explain? I think users would like this property if it could be
implemented without breaking anything.
Nishad
--
"Underneath the concrete, the dream is still alive" -- Talking Heads
Nishad Prakash <prakashn@uci.edu> writes:
Why doesn't the query planner notice that an int-type index is present
and perform the cast on its own? Perhaps one of the developers
can explain? I think users would like this property if it could be
implemented without breaking anything.
It's been done for 7.5. As for why it wasn't done earlier, well, it
was a hard problem. Read the pgsql-hackers archives.
regards, tom lane