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

Dominique,

If you cast your constants to the datatype of your columns the optimizer
should then choose to use the index:

select ciname from city where cix=400::smallint and ciy=500::smallint;

(or you could change the datatype of the columns to be integer)

The way you have things currently the optimizer sees the column as being
smallint and the constant as being an integer and because of the
datatype differences, it therefore thinks it can't use the index.

thanks,
--Barry

Dominique Dumortier wrote:

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