Thread: Index unused with OR?

Index unused with OR?

From
"Olaf Mittelstaedt"
Date:
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.


Re: [INTERFACES] Index unused with OR?

From
Teodorescu Constantin
Date:
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


Re: Index unused with OR?

From
"Olaf Mittelstaedt"
Date:
> 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.


Re: [INTERFACES] Re: Index unused with OR?

From
Teodorescu Constantin
Date:
> > 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


Re: [INTERFACES] Re: Index unused with OR?

From
Hannu Krosing
Date:
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

Re: [INTERFACES] Re: Index unused with OR?

From
"Olaf Mittelstaedt"
Date:
> 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.


Re: [INTERFACES] Re: Index unused with OR?

From
Herouth Maoz
Date:
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