PostGres is not using indices in select, I would like it to because it is too slow !

Dear all,

We had been using a postgresql version 6.0 since 1997. I have recently
decided to go to the most recent version 7.1.3. I am under Solaris 2.6. The
installation was OK, but after restoring the database. I have been surprised
by the poor performance of the new version.

Going a litte deeper, I have discovered with an explain that the new version
thinks it is better not to use an index while the older version was using
it. Thus my old select was taking a few seconds, when it now takes 3
minutes. I have not been able to figure out how I could convince the new
version that it would go faster with the index.

I have loaded the new database from an dump file, I have recreated the index
from scratch under the new version. I have done vacuum analyse before, after
creating the index. No way !

Could anyone help me ? Or I go back to the old version.

The table is created this way:

CREATE TABLE "city" (
"ciname" character varying(80),
"cix" smallint,
"ciy" smallint,
"cilat" smallint,
"cilong" smallint,
"cialt" smallint,
"cicocode" smallint,
"cimacode" character(1),
"cictcode" character(1)
);

COPY "city" FROM stdin;
Abat 1030 504 4233 1981 1130 8 H T
Abate 1030 504 4233 1981 1130 8 H T
Abati 1030 504 4233 1981 1130 8 H T
The table contains about 750000 lines such as the ones above.

The index is created like that:

CREATE INDEX ci_index on city (cix,ciy)

If I do select ciname from city where cix=400 and ciy=500;

This version of Postgres does not use the index and it takes much more time
than the previous version.

Hint: for the same couple cix, ciy there might be 30 different ciname to
report, not more.

Thanks in advance,

--
Mr Dominique Dumortier
LASH-ENTPE
Rue Maurice Audin
69518 Vaulx-en-Velin Cedex
France
Tel: +33 472047087
Fax: +33 472047041