Thread

1. Bugs in bigint indexes

   Gustavo Scotti <gscotti@axur.com.br> — 2003-12-17T19:56:51Z

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

2. Re: Bugs in bigint indexes

   jmm <jmm@cvni.net> — 2003-12-19T17:58:24Z

   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)
   
   

3. Re: Bugs in bigint indexes

   Peter Eisentraut <peter_e@gmx.net> — 2003-12-19T18:00:34Z

   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.
   
   
   

4. Re: Bugs in bigint indexes

   Nishad Prakash <prakashn@uci.edu> — 2003-12-19T18:17:32Z

   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
   
   
   

5. Re: Bugs in bigint indexes

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-12-19T18:31:16Z

   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