The following bug has been logged online:

Bug reference: 2006
Logged by: Atanas Hristov
Email address: atanashristov@hotmail.com
PostgreSQL version: 8.1 Beta 3
Operating system: FreeBSD 5
Description: queryoptimizer and comparing a primary key of biginteger
and a literal
Details:

foo=# CREATE TABLE foo (id BIGINT, CONSTRAINT foo_pk PRIMARY KEY(id) );



foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = CAST(0 AS BIGINT);
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------
Index Scan using foo_pk on foo (cost=0.00..4.82 rows=2 width=0) (actual
time=0.113..0.113 rows=0 loops=1)
Index Cond: (id = 0::bigint)



foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = 0; -- WHY POSTGRES
DOES NOT IMPLICITLY DO CASTING OF THE LITERAL "0" ??????
QUERY PLAN
----------------------------------------------------------------------------
------------------
Seq Scan on foo (cost=0.00..22.50 rows=2 width=0) (actual
time=0.003..0.003 rows=0 loops=1)
Filter: (id = 0)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Atanas Hristov" <atanashristov@hotmail.com> writes:
> foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = 0; -- WHY POSTGRES
> DOES NOT IMPLICITLY DO CASTING OF THE LITERAL "0" ??????

It works for me:

regression=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Index Scan using foo_pk on foo (cost=0.00..4.82 rows=1 width=0) (actual time=0.241..0.241 rows=0 loops=1)
Index Cond: (id = 0)
Total runtime: 0.456 ms
(3 rows)

I don't think you're really testing 8.1. Try "select version()"

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Atanas Hristov wrote:
> Bug reference: 2006
> Logged by: Atanas Hristov
> Email address: atanashristov@hotmail.com
> PostgreSQL version: 8.1 Beta 3
> Operating system: FreeBSD 5
> Description: queryoptimizer and comparing a primary key of biginteger
> and a literal
> Details:
>
> foo=# CREATE TABLE foo (id BIGINT, CONSTRAINT foo_pk PRIMARY KEY(id) );

> foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = CAST(0 AS BIGINT);
> --------------------------------
> Index Scan using foo_pk on foo (cost=0.00..4.82 rows=2 width=0) (actual
> time=0.113..0.113 rows=0 loops=1)
> Index Cond: (id = 0::bigint)

> foo=# EXPLAIN ANALYZE SELECT 'foo' FROM foo WHERE id = 0; -- WHY POSTGRES
> DOES NOT IMPLICITLY DO CASTING OF THE LITERAL "0" ??????

See the mailing list archives for extensive discussion of this. Since
you are using 8.1 it IS casting against the index.

> ------------------
> Seq Scan on foo (cost=0.00..22.50 rows=2 width=0) (actual
> time=0.003..0.003 rows=0 loops=1)
> Filter: (id = 0)

You don't have any data in the table - what's the point of PG using an
index?

=> CREATE TABLE foo (id BIGINT, CONSTRAINT foo_pk PRIMARY KEY(id) );
=> INSERT INTO foo SELECT * FROM generate_series(1,10000);
=> VACUUM ANALYSE foo;
=> EXPLAIN ANALYSE SELECT * FROM foo WHERE id=1;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using foo_pk on foo (cost=0.00..3.01 rows=1 width=8) (actual
time=43.832..43.839 rows=1 loops=1)
Index Cond: (id = 1)

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster