BIGINT datatype and Indexes Failure

Started by Denis Gasparinover 24 years ago3 messagesgeneral
Jump to latest
#1Denis Gasparin
denis@edinet.it

Hi to all!
Is it possible to define indexes on a column with BIGINT datatype? See
this example:

testdb=# create table a (col1 integer not null primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for
table 'a'
CREATE
testdb=# create table b (col1 bigint not null primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'b_pkey' for
table 'b'
CREATE
testdb=# explain select * from a where col1=123;
NOTICE: QUERY PLAN:

Index Scan using a_pkey on a (cost=0.00..8.14 rows=10 width=4)

EXPLAIN
testdb=# explain select * from b where col1=123;
NOTICE: QUERY PLAN:

Seq Scan on b (cost=0.00..22.50 rows=10 width=8)

On table a (INTEGER datatype) the search is done using the index.
Instead on table b (BIGINT datatype) the search is always done using the
seq scan.

Is it a bug?

I use Postgresql 7.1.2 on RedHat 7.1. All the regress tests has been
completed correctely during installation.

Please, let me know as soon as possible...

Regards,

Eng. Denis Gasparin: denis@edistar.com
---------------------------
Programmer & System Administrator - Edistar srl

#2Denis Gasparin
denis@edinet.it
In reply to: Denis Gasparin (#1)
Re: BIGINT datatype and Indexes Failure

I search in the archive and i have found that i have to specify the type of
the column at the end of the query... so the new query is :

explain select * from b where col1=123::int8;

In this way, the explain output is correct.
The e-mail i found in the archive says that the problem will be solved in
some future release....

At this point, is it safe to use BIGINT datatype and indexes on those fields?

Thank for your answers,

Regards,

Eng. Denis Gasparin: denis@edistar.com
---------------------------
Programmer & System Administrator - Edistar srl

At 14.57 24/08/01, Denis Gasparin wrote:

Show quoted text

Hi to all!
Is it possible to define indexes on a column with BIGINT
datatype? See this example:

testdb=# create table a (col1 integer not null primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for
table 'a'
CREATE
testdb=# create table b (col1 bigint not null primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'b_pkey' for
table 'b'
CREATE
testdb=# explain select * from a where col1=123;
NOTICE: QUERY PLAN:

Index Scan using a_pkey on a (cost=0.00..8.14 rows=10 width=4)

EXPLAIN
testdb=# explain select * from b where col1=123;
NOTICE: QUERY PLAN:

Seq Scan on b (cost=0.00..22.50 rows=10 width=8)

On table a (INTEGER datatype) the search is done using the index.
Instead on table b (BIGINT datatype) the search is always done using the
seq scan.

Is it a bug?

I use Postgresql 7.1.2 on RedHat 7.1. All the regress tests has been
completed correctely during installation.

Please, let me know as soon as possible...

Regards,

Eng. Denis Gasparin: denis@edistar.com
---------------------------
Programmer & System Administrator - Edistar srl

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Denis Gasparin (#1)
Re: BIGINT datatype and Indexes Failure

Denis Gasparin <denis@edinet.it> writes:

On table a (INTEGER datatype) the search is done using the index.
Instead on table b (BIGINT datatype) the search is always done using the
seq scan.

Try
select * from b where col1 = 123::int8;

The query planner is not presently very smart about cross-datatype
comparisons (int8 vs int4). We have a TODO item to fix this...

regards, tom lane