pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTypecast bug?
5+ messages / 4 participants
[nested] [flat]
* Typecast bug?
@ 2008-06-26 04:17 Craig James <craig_james@emolecules.com>
0 siblings, 2 replies; 5+ messages in thread
From: Craig James @ 2008-06-26 04:17 UTC (permalink / raw)
To: 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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Typecast bug?
@ 2008-06-26 05:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2008-06-26 05:33 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> This seems like a bug to me, but it shows up as a performance problem.
> emol_warehouse_1=> explain analyze select version_id, parent_id from version where version_id = 999999999999999999999999999;
If you actually *need* so many 9's here as to force it out of the range
of bigint, then why is your id column not declared numeric?
This seems to me to be about on par with complaining that "intcol = 4.2e1"
won't be indexed. We have a numeric data type hierarchy, learn to
work with it ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Typecast bug?
@ 2008-06-26 06:22 Craig James <craig_james@emolecules.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Craig James @ 2008-06-26 06:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Tom Lane wrote:
> Craig James <craig_james@emolecules.com> writes:
>> This seems like a bug to me, but it shows up as a performance problem.
>
>> emol_warehouse_1=> explain analyze select version_id, parent_id from version where version_id = 999999999999999999999999999;
>
> If you actually *need* so many 9's here as to force it out of the range
> of bigint, then why is your id column not declared numeric?
>
> This seems to me to be about on par with complaining that "intcol = 4.2e1"
> won't be indexed. We have a numeric data type hierarchy, learn to
> work with it ...
Your suggestion of "learn to work with it" doesn't fly. A good design separates the database schema details from the application to the greatest extent possible. What you're suggesting is that every application that queries against a Postgres database should know the exact range of every numeric data type of every indexed column in the schema, simply because Postgres can't recognize an out-of-range numeric value.
In this case, the optimizer could have instantly returned zero results with no further work, since the query was out of range for that column.
This seems like a pretty simple optimization to me, and it seems like a helpful suggestion to make to this forum.
BTW, this query came from throwing lots of junk at a web app in an effort to uncover exactly this sort of problem. It's not a real query, but then, hackers don't use real queries. The app checks that its input is a well-formed integer expression, but then assumes Postgres can deal with it from there.
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Typecast bug?
@ 2008-06-26 07:02 Frank Joerdens <frank@joerdens.de>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 1 reply; 5+ messages in thread
From: Frank Joerdens @ 2008-06-26 07:02 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On 6/26/08, Craig James <craig_james@emolecules.com> wrote:
> 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.
Which version are you using? 8.3 removes a lot of implicit casts (all?
not sure), so this may already be your fix.
Cheers,
Frank
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Typecast bug?
@ 2008-06-26 07:12 Guillaume Smet <guillaume.smet@gmail.com>
parent: Frank Joerdens <frank@joerdens.de>
0 siblings, 0 replies; 5+ messages in thread
From: Guillaume Smet @ 2008-06-26 07:12 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
On Thu, Jun 26, 2008 at 9:02 AM, Frank Joerdens <frank@joerdens.de> wrote:
> Which version are you using? 8.3 removes a lot of implicit casts (all?
> not sure), so this may already be your fix.
8.3 only removed implicit casts from non text types to text (date ->
text, int -> text, interval -> text...) to avoid unexpected
behaviours.
--
Guillaume
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-06-26 07:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-26 04:17 Typecast bug? Craig James <craig_james@emolecules.com>
2008-06-26 05:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-26 06:22 ` Craig James <craig_james@emolecules.com>
2008-06-26 07:02 ` Frank Joerdens <frank@joerdens.de>
2008-06-26 07:12 ` Guillaume Smet <guillaume.smet@gmail.com>
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