From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Thomas O'Dowd <tom(at)nooper(dot)com> |
Cc: | Postgresql General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: why isn't index used? |
Date: | 2002-10-07 15:31:51 |
Message-ID: | 20021007153151.GF24034@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)svana(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Zeugswetter Andreas SB SD | 2002-10-07 15:42:12 | Re: [HACKERS] Large databases, performance |
Previous Message | Manfred Koizar | 2002-10-07 15:22:41 | Re: Large databases, performance |