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)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
