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

Typecast bug?

From: Craig James <craig_james(at)emolecules(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Typecast bug?
Date: 2008-06-26 04:17:50
Message-ID: 4863186E.1000306@emolecules.com (view raw or flat)
Thread:
Lists: pgsql-performance
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)





In response to

Responses

pgsql-performance by date

Next:From: Peter T. BreuerDate: 2008-06-26 05:03:38
Subject: Re: Hardware vs Software Raid
Previous:From: Craig JamesDate: 2008-06-26 01:52:59
Subject: Re: RAID 10 Benchmark with different I/O schedulers

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