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.
Show quoted text
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?