Postgres Pro
Downloads
Home   >   mailing lists

Thread: why isn't index used?

why isn't index used?

From
Thomas O'Dowd
Date:
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;

The table was...

CREATE TABLE testing
(
   a  int8 NOT NULL,
   b  text NOT NULL,
   c  text NOT NULL
);

I generated 100,000 rows using a small perl program.

    print "copy testing from stdin;\n";
    for (1..100000) {
       print "$_   one   two\n";
    }
    print "\\.\n";

and created an index using...

    create index testing_a_key on testing (a);

and then ran Analyze command.

    analyze;

Then tried the following...

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

In the first case you'll note that I didn't explicitly cast to bigint
and the index is not used even if I turn off enable_seqscan. Only when I
explicitly cast to bigint does it get used. This seems a little brain
dead to me, no? Is this the expected behaviour?

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

Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs

Re: why isn't index used?

From
Martijn van Oosterhout
Date:
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.

Re: why isn't index used?

From
Thomas O'Dowd
Date:
Martijn,

> 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.

Thanks for the reply. It would be great to get this added to the DOCS so
that the next person doesn't spend a day trying to figure it out too.
Also, which is faster: using '3' or 3::int8? I presume shorter is
quicker to parse, but is there an advantage over treating it as an
unknown and an explicit cast?

Tom.
--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs