Indexes not working (bug in 7.0.2?) - Mailing list pgsql-general

Hi!

This is my first post (not counting those failed because I posted them
from the other email address) so please be gentle.

I have recently started playing with PostgreSQL and found what I think
is a bug in postgres. I'm using 7.0.2 from Debian Linux package
(woody) if its of any difference.

I have a table like this:

imenik=# \d subscriber
         Table "subscriber"
 Attribute |     Type     | Modifier
-----------+--------------+----------
 id        | integer      | not null
 prefix    | char(2)      | not null
 name      | varchar(128) | not null
 number    | varchar(8)   | not null
 title     | integer      |
 street    | integer      | not null
 num       | varchar(8)   | not null
 city      | integer      | not null
Index: idx_number

First I populated the table, then I created index on the number field
with "CREATE INDEX idx_number on subscriber(number);"

The trouble is when I do SELECT specifying a number, postgres reads a
whole table ie. it is not using the index. EXPLAIN reveals it is
indeed doing a sequential scan.

test=# EXPLAIN SELECT * from subscriber where number = '123456';
NOTICE:  QUERY PLAN:

Seq Scan on subscriber  (cost=0.00..38677.28 rows=15564 width=64)

EXPLAIN

What am I doing wrong???

Second example:

Today I stumbled upon a similar problem with completely different set
of data, but even more confusing.

Consider two tables 'filenew' and 'fileold' that have same fields and
indices:

filedb=# \d fileold
           Table "fileold"
 Attribute |     Type      | Modifier
-----------+---------------+----------
 file      | varchar(1024) | not null
 mode      | integer       | not null
 uid       | integer       | not null
 gid       | integer       | not null
 size      | bigint        | not null
 mtime     | integer       | not null
 ctime     | integer       | not null
Index: fileold_file_key

s/fileold/filenew and you know the schema for filenew. Idea is to
populate fileold once with filesystem information and then later, when
things on FS change, populate filenew with a new data and search for
differences.

As you see, tables are almost the same, but...

*** Looking for new files:

filedb=# explain select file from filenew where not exists (select 1 from fileold where filenew.file = fileold.file);
NOTICE:  QUERY PLAN:

Seq Scan on filenew  (cost=0.00..0.00 rows=1 width=12)
  SubPlan
    ->  Index Scan using fileold_file_key on fileold  (cost=0.00..2935.96 rows=1329 width=4)

EXPLAIN

*** Looking for deleted files:

filedb=# explain select file from fileold where not exists (select 1 from filenew where fileold.file = filenew.file);
NOTICE:  QUERY PLAN:

Seq Scan on fileold  (cost=0.00..3155.26 rows=1 width=12)
  SubPlan
    ->  Seq Scan on filenew  (cost=0.00..0.00 rows=1 width=4)

EXPLAIN

It is now NOT using the index, and I don't understand why? Queries are
practically the same, tables are practically the same, why is postgres
using indexes in the first case and not in the second?

TIA,
--
Zlatko