Re: Bugs in bigint indexes

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