The following bug has been logged online:
Bug reference: 4313
Logged by: Daniel Podlejski
Email address: daniel.podlejski@gmail.com
PostgreSQL version: 8.3.1, 8.3.3
Operating system: Linux
Description: Strange optimizer behaviour
Details:
cvalley_dev=# \d messages
Table "public.messages"
Column | Type | Modifiers
------------+-----------------------------+---------------------------------
----------------------
id | integer | not null default
nextval('messages_id_seq'::regclass)
sender_id | integer | not null
rcptto_id | integer | not null
subject | text |
body | text |
read | boolean | not null default false
deleted | boolean | not null default false
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
"messages_pkey" PRIMARY KEY, btree (id)
cvalley_dev=# EXPLAIN SELECT * FROM messages WHERE (messages."id" = 111111);
QUERY PLAN
----------------------------------------------------------------------------
---
Index Scan using messages_pkey on messages (cost=0.00..8.35 rows=1
width=51)
Index Cond: (id = 111111)
(2 rows)
cvalley_dev=# EXPLAIN SELECT * FROM messages WHERE (messages."id" =
11111111111111111111111111111111111111111111111111);
QUERY PLAN
----------------------------------------------------------------------------
-------------
Seq Scan on messages (cost=0.00..23400.56 rows=4588 width=51)
Filter: ((id)::numeric =
11111111111111111111111111111111111111111111111111::numeric)
(2 rows)
I think there is no sense to cast too big value to numeric when field type
is integer.
On really big table this "bug" cause unnecessary io load.