Re: BUG #2006: queryoptimizer and comparing a primary key

Atanas Hristov wrote:
> Bug reference: 2006
> Logged by: Atanas Hristov
> Email address: atanashristov(at)hotmail(dot)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