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
applicationto the greatest extent possible. What you're suggesting is that every application that queries against a
Postgresdatabase should know the exact range of every numeric data type of every indexed column in the schema, simply
becausePostgres 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
rangefor 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'snot 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