Thread: Bugs in bigint indexes

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

MessageTry this

 explain SELECT id FROM it_test WHERE id=3D'123';
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using it_test_pkey on it_test  (cost=3D0.00..4.82 rows=3D1 widt=
h=3D8)
   Index Cond: (id =3D 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 -----=20
  From: Gustavo Scotti=20
  To: pgsql-bugs@postgresql.org=20
  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...=20
  Let's get straight to the point. I'm using a small portion of my actual t=
able, 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=3D123;
  Seq Scan on it_test (cost=3D0.0..22.50 rows=3D2 width=3D8)
     Filter: (id=3D123)
  (2 rows)