From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Typecast bug?
Date: Wed, 25 Jun 2008 21:17:50 -0700
Message-ID: <4863186E.1000306@emolecules.com> (raw)
In-Reply-To: <D960CB61B694CF459DCFB4B0128514C202122018@exadv11.host.magwien.gv.at>
References: <D960CB61B694CF459DCFB4B0128514C201FA62C8@exadv11.host.magwien.gv.at>
<C050E63A-2209-4E89-AA99-6A327CCE7380@torgo.978.org>
<Pine.LNX.4.64.0804111736580.20402@aragorn.flymine.org>
<47FFA7FF.10801@postnewspapers.com.au>
<544D07A2-EB3F-4D91-A749-0C4D5D38AB80@gmail.com>
<481F992D.7070509@emolecules.com>
<Pine.GSO.4.64.0805052354120.3752@westnet.com>
<4820A3C2.4000307@emolecules.com>
<Pine.GSO.4.64.0805061605030.11474@westnet.com>
<4820C30A.8010709@emolecules.com>
<D960CB61B694CF459DCFB4B0128514C202122018@exadv11.host.magwien.gv.at>
This seems like a bug to me, but it shows up as a performance problem. Since the column being queried is an integer, the second query (see below) can't possibly match, yet Postgres uses a typecast, forcing a full table scan for a value that can't possibly be in the table.
The application could intercept these bogus queries, but that requires building schema-specific and postgres-specific knowledge into the application (i.e. "What is the maximum legal integer for this column?").
Craig
explain analyze select version_id, parent_id from version where version_id = 99999;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using version_pkey on version (cost=0.00..9.89 rows=1 width=8) (actual time=0.054..0.054 rows=0 loops=1)
Index Cond: (version_id = 99999)
Total runtime: 0.130 ms
(3 rows)
emol_warehouse_1=> explain analyze select version_id, parent_id from version where version_id = 999999999999999999999999999;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on version (cost=0.00..253431.77 rows=48393 width=8) (actual time=3135.530..3135.530 rows=0 loops=1)
Filter: ((version_id)::numeric = 999999999999999999999999999::numeric)
Total runtime: 3135.557 ms
(3 rows)
\d version
Table "emol_warehouse_1.version"
Column | Type | Modifiers
------------+---------+-----------
version_id | integer | not null
parent_id | integer | not null
... more columns
Indexes:
"version_pkey" PRIMARY KEY, btree (version_id)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: Typecast bug?
In-Reply-To: <4863186E.1000306@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox