why isn't index used? - Mailing list pgsql-general

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