Re: [BUGS] BUG #2006: queryoptimizer and comparing a primary key

#1

Atanas Hristov wrote:

Quote:

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.

Quote:

------------------
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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #2006: queryoptimizer and comparing a primary key

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #2006: queryoptimizer and comparing a primary key - 10-28-2005 , 11:34 AM