Thread: BUG: bigint and indexes ?

Is it a known bug if optimizer, that Postgres can't use indexes created on =
a int8 (bigint) ?
This problem exists in 7.1.3 and even in 7.2.


test=3D# CREATE TABLE parent_child (
test(#         parent_id int8 NOT NULL,
test(#         parent_type int4 NOT NULL,
test(#         child_id int8 NOT NULL PRIMARY KEY,
test(#         child_type int4 NOT NULL,
test(#         account_id int8 NOT NULL,
test(#         p_begin date,
test(#         suspended int4);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'parent_chil=
d_pkey' for table 'parent_child'
CREATE
test=3D# explain select * from parent_child where child_id=3D1;
NOTICE:  QUERY PLAN:

Seq Scan on parent_child  (cost=3D0.00..22.50 rows=3D1 width=3D40)

EXPLAIN
test=3D# create index parent_child_childtype on parent_child(child_type);
CREATE
test=3D# explain select * from parent_child where child_type=3D1;
NOTICE:  QUERY PLAN:

Index Scan using parent_child_childtype on parent_child  (cost=3D0.00..17.0=
7 rows=3D5 width=3D40)

EXPLAIN
test=3D#

On Fri, 1 Mar 2002, Victor Krasinsky wrote:

> Is it a known bug if optimizer, that Postgres can't use indexes created on a int8 (bigint) ?
> This problem exists in 7.1.3 and even in 7.2.

You need to explicitly cast the constant that you're comparing to
bigint (1::bigint or CAST(1 as bigint)).