Re: Using index from sub-query

pdaly <petedaly(at)ix(dot)netcom(dot)com> writes:
> As has been talked about recently, I have a int8 field, which when
> searched for as 'value' will use the index, but as value, with the
> quotes will not.

Right now, you have exactly two choices: quote the literal, or
explicitly cast it to int8. Thus
select distinct id from namelookup where name = '$tname'
select distinct id from namelookup where name = $tname::int8
select distinct id from namelookup where name = int8($tname)
will work; anything else will not use the index.

The problem is basically that an unadorned integer-looking literal
will be taken to be int4 not int8, so the parser ends up considering
the '=' operator to be int8-vs-int4 equality, which is not an operator
that can be used with an int8 index. You can find past discussions
of this issue in the archives, though I think previous complainers have
been more interested in int2 indexes which have a similar problem.

Fixing this without making the system do the wrong thing in other cases
turns out to be trickier than you might think. It's on the todo list
but isn't likely to be fixed for 7.1 ...

regards, tom lane