Обсуждение: Index unused with OR?
Hello all, playing around with the latest ODBC driver I noticed very slow answers when using OR with an indexed column in the WHERE part of a SELECT. To verify this I tried the following directly with psql on the database server: postgres=> create database test7; CREATEDB postgres=> \c test7 connecting to new database: test7 test7=> create table t7(i1 int4,i2 int4); CREATE test7=> create unique index i_t7 on t7(i1); CREATE test7=> insert into t7 values(1,2); INSERT 53741 1 test7=> insert into t7 values(2,3); INSERT 53742 1 test7=> insert into t7 values(3,4); INSERT 53743 1 test7=> explain select * from t7 where i1=1; NOTICE: QUERY PLAN: Index Scan on t7 (cost=0.00 size=0 width=8) EXPLAIN test7=> explain select * from t7 where (i1=1) or (i1=2); NOTICE: QUERY PLAN: Seq Scan on t7 (cost=0.00 size=0 width=8) EXPLAIN Obviously the index isn't used. Both PostgreSQL 6.3.1 and 6.3.2 are suffering from this problem. Any help or hints? Regards, Olaf -- Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm Tel.: +49 (0)731-502-8220 Fax: -8270 Nemo me impune lacessit.
The index is used only if scanning the index is faster than scanning the table itself. Trying the index only with a coupleof records isn't good. Try inserting some hundred records inside and VACUUM the database after. When you vacuum the database you are updating the statistics table used by the query optimizer. The query optimizer willchoose to use an index when it found that it worths. Not using the index in OR queries was some-time ago a bug in PostgreSQL 6.1 if my memory is good. Think that has been solvedin new releases, am I wrong ? Constantin Teodorescu FLEX Consulting Braila, ROMANIA
> The index is used only if scanning the index is faster than > scanning the table itself. Trying the index only with a couple of > records isn't good. > Try inserting some hundred records inside and VACUUM the database > after. Actually, I discovered the problem using a table containg more than 8000 rows, using the latest release 6.3.2. > When you vacuum the database you are updating the statistics table > used by the query optimizer. The query optimizer will choose to > use an index when it found that it worths. > Not using the index in OR queries was some-time ago a bug in > PostgreSQL 6.1 if my memory is good. Think that has been solved in > new releases, am I wrong ? This is the real database: w=> select count(*) from p; count ----- 8331 (1 row) w=> vacuum; VACUUM w=> explain select * from p where m = 29000; NOTICE: QUERY PLAN: Index Scan on p (cost=0.00 size=0 width=10) EXPLAIN w=> explain select * from p where (m=29000) or (m=30000); NOTICE: QUERY PLAN: Seq Scan on p (cost=0.00 size=0 width=10) EXPLAIN Regards, Olaf -- Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm Tel.: +49 (0)731-502-8220 Fax: -8270 Ash nazg durbatulûk, ash nazg gimbatul, ash nazg thrakatulûk agh burzum-ishi krimpatul.
> > Not using the index in OR queries was some-time ago a bug in > > PostgreSQL 6.1 if my memory is good. Think that has been solved in > > new releases, am I wrong ? w=> explain select * from p where (m=29000) or (m=30000); NOTICE: QUERY PLAN: Seq Scan on p (cost=0.00 size=0 width=10) It seems that it was not solved :-( Let's hear also some other answers from the developers ... Please make another try ! I have discovered once a strange thing : that if I am vacuuming the database as another user thanroot, the statistics table isn't properly updated. I think that it was a problem with my database, I have reported butnoone has reported the same thing again , so ... the problem it has been dropped. Make another try logging as postgres user, issue "psql yourdb" command and then vacuum. Repeat the EXPLAIN query and checkif it's the same result! Constantin Teodorescu FLEX Consulting Braila, ROMANIA
Olaf Mittelstaedt wrote:
This is probably the wrong list, but ...
> Actually, I discovered the problem using a table containg more than
> 8000 rows, using the latest release 6.3.2.
>
> > Not using the index in OR queries was some-time ago a bug in
> > PostgreSQL 6.1 if my memory is good. Think that has been solved in
> > new releases, am I wrong ?
seems _not_ to be fixed :(
> VACUUM
> w=> explain select * from p where m = 29000;
> NOTICE: QUERY PLAN:
>
> Index Scan on p (cost=0.00 size=0 width=10)
>
> EXPLAIN
> w=> explain select * from p where (m=29000) or (m=30000);
> NOTICE: QUERY PLAN:
>
> Seq Scan on p (cost=0.00 size=0 width=10)
>
As a work-around try
select * from p where (m=29000) union select * from p where (m=30000);
it should be much faster.
Btw, EXPLAIN thinks this to cost nothing ;)
Unique (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Append (cost=0.00 size=0 width=0)
Hannu
> This is probably the wrong list, but ... Sorry for using the wrong list, I discovered the problem using ODBC. There are now too many PostgreSQL lists to subscribe to all... :( > select * from p where (m=29000) union select * from p where (m=30000); Thanks for your hint, the query above works much better. Regards, Olaf -- Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm Tel.: +49 (0)731-502-8220 Fax: -8270 Beati pauperes spiritu.
At 17:59 +0300 on 14/5/98, Olaf Mittelstaedt wrote: > > w=> vacuum; > VACUUM > w=> explain select * from p where m = 29000; > NOTICE: QUERY PLAN: > > Index Scan on p (cost=0.00 size=0 width=10) Unrelated to the question in hand - you complained about VACUUM not properly updating the stats. It seems to me you should use VACUUM ANALYZE for that to happen. Just vacuuming means elimination of old rows no longer used. Herouth