Thread: PostGres is not using indices in select, I would like it to because it is too slow !
PostGres is not using indices in select, I would like it to because it is too slow !
From
Dominique Dumortier
Date:
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
On Mon, 24 Sep 2001, Dominique Dumortier wrote: > 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; Ah, you're probably running into the integer constants being int4 problem. I'd suggest making the smallints into regular ints, or explicitly casting the constants to smallint.
Re: PostGres is not using indices in select, I would like it to because it is too slow !
From
Martijn van Oosterhout
Date:
On Mon, Sep 24, 2001 at 09:39:19PM +0200, Dominique Dumortier wrote: > Dear all, > > "cix" smallint, > "ciy" smallint, > 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; Try putting quotes around the numbers. There are similar issues with bigint where it can't see that smallint promotes to int4 without loss so it doesn't realise that it can use an index.. By putting quotes around it, the database will see them as smallints and (hopefully) us the index. HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
Re: PostGres is not using indices in select, I would like it to because it is too slow !
From
Barry Lind
Date:
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, > > >