Skip site navigation (1) Skip section navigation (2)

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

From: Richard Huxton <dev(at)archonet(dot)com>
To: Atanas Hristov <atanashristov(at)hotmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2006: queryoptimizer and comparing a primary key
Date: 2005-10-28 16:29:18
Message-ID: 436251DE.5040101@archonet.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Martin PittDate: 2005-10-29 00:36:32
Subject: BUG #2009: Unqouted dashes in manpages
Previous:From: Tom LaneDate: 2005-10-28 16:12:20
Subject: Re: BUG #2006: queryoptimizer and comparing a primary key of biginteger and a literal

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group