Re: Huge Performance Difference on Similar Query in Pg7.2

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> Using the numbers quoted yields use of the primary key. I am indeed using
> something other than int4, int2 in fact. So this is something to do with the
> using integer literals which are presumably first interpreted as int4 and then
> are converted in some long winded fashion, or something, to int2 for each and
> every test or row, whereas specifying them as text causes the backend to
> convert to the correct int2 only at the start?

No, they're not converted at all: if you write, say,
    int2var = 42
then the constant is immediately taken as int4, and "=" is resolved as
the int2-equals-int4 operator, which works fine but is not one of the
set of operators that the system knows how to use with an int2 index.

If you write
    int2var = '42'
then the '42' is initially treated as an unknown-type literal, and there
are resolution rules that will preferentially choose int2 to match
what's on the other side of the operator, whereupon "=" gets resolved
as the int2-equals-int2 operator, which is indexable.

To fix this and related problems we need to twiddle the ambiguity
resolution rules so that numeric constants can be given something other
than their "natural" datatype ... without breaking a lot of other cases
that work conveniently today.  See (many) past discussions on the
pghackers list.

            regards, tom lane