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

BUG #4313: Strange optimizer behaviour

From: "Daniel Podlejski" <daniel(dot)podlejski(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4313: Strange optimizer behaviour
Date: 2008-07-17 14:37:53
Message-ID: 200807171437.m6HEbrOJ013322@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4313
Logged by:          Daniel Podlejski
Email address:      daniel(dot)podlejski(at)gmail(dot)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.

Responses

pgsql-bugs by date

Next:From: Gregory StarkDate: 2008-07-17 15:31:18
Subject: Re: BUG #4313: Strange optimizer behaviour
Previous:From: ITAGAKI TakahiroDate: 2008-07-17 10:09:16
Subject: Re: MAX関数に関する報告

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