Thread: int8 indices?

hi
my postgresql is:
depesz=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2devel on i686-pc-linux-gnu, compiled by GCC 3.0
(1 row)

(compiled from cvs 2 days ago).

int8 are working:
depesz=# select 2147483648::int4+1::int4;
ERROR:  pg_atoi: error reading "2147483648": Numerical result out of range
depesz=# select 2147483648::int8+1::int8;
  ?column?
------------
 2147483649

not i have table a:
depesz=# \d a
          Table "a"
 Column |  Type  | Modifiers
--------+--------+-----------
 id     | bigint |
Unique keys: aaa

depesz=# \d aaa
   Index "aaa"
 Column |  Type
--------+--------
 id     | bigint
unique btree

depesz=# select count(*) from a;
 count
-------
 25981
(1 row)

(all of them are not nul:
depesz=# select count(*) from a where id is not null;
 count
-------
 25981
(1 row)
)

of course i vacuum'ed, but when i select anything from the table i get
seqscan:
depesz=# explain select * from a where id = 6970;
NOTICE:  QUERY PLAN:

Seq Scan on a  (cost=0.00..465.76 rows=1 width=8)

EXPLAIN

what could be possible reason?

depesz

--
hubert depesz lubaczewski                          http://www.depesz.pl/
------------------------------------------------------------------------
... and the end of all our exploring  will be to arrive where we started
and know the place for the first time.                    -- T. S. Eliot

From: "hubert depesz lubaczewski" <depesz@depesz.pl>

> depesz=# select version();
>                             version
> ---------------------------------------------------------------
>  PostgreSQL 7.2devel on i686-pc-linux-gnu, compiled by GCC 3.0
> (1 row)
>
> (compiled from cvs 2 days ago).

PG 7.2dev, GCC 3.0 - you're just showing off ;-)

> of course i vacuum'ed, but when i select anything from the table i get
> seqscan:
> depesz=# explain select * from a where id = 6970;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on a  (cost=0.00..465.76 rows=1 width=8)

Try where id=6970::int8 - I seem to remember Tom Lane saying the planner got
upset because the constant got cast to an int4 and of course you don't have
an int4 index...

HTH

- Richard Huxton

I got burned by this recently and found the answer on the net:

When selecting on a column with an INT8 index, alway put
quotes around the constant value:

select * from a where id = '6970';

Otherwise the constant is assumed to be an INT4, which doesn't match the
type in the index, so the index is not used.



> of course i vacuum'ed, but when i select anything from the table i get
> seqscan:
> depesz=# explain select * from a where id = 6970;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on a  (cost=0.00..465.76 rows=1 width=8)
>
> EXPLAIN
>
> what could be possible reason?
>