Re: why isn't index used?

On Mon, Oct 07, 2002 at 09:46:11PM +0900, Thomas O'Dowd wrote:
> Hi all,
>
> Just spent last day scratching my head over why the following simple
> query wasn't using the index...
>
> select b,c from testing where a=99999;

Put quotes around the number. That's the most reliable.

> nooper=# explain select b,c from testing where a=99999;
> NOTICE:  QUERY PLAN:
> Seq Scan on testing  (cost=0.00..1987.20 rows=1 width=14)
> EXPLAIN
>
> nooper=# explain select b,c from testing where a=99999::int8;
> NOTICE:  QUERY PLAN:
> Index Scan using testing_a_key on testing  (cost=0.00..3.01 rows=1
> width=14)
> EXPLAIN

You picked it. The parser reads your number as an int4 and the planner
assumes that you can't use int4's on an int8 index. It sounds brain-dead. It
is brain-dead, except that it's also not easy to fix. Putting quotes around
the number means the planner will treat it as unknown and it works.

> I'm using 7.2.1 currently. Maybe its different in upcoming 7.3?

There has been some work towards this, yes. I don't know how much though.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.