Skip site navigation (1) Skip section navigation (2)

Index unused with OR?

From: "Olaf Mittelstaedt" <mstaedt(at)va-sigi(dot)va(dot)fh-ulm(dot)de>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Index unused with OR?
Date: 1998-05-14 14:20:56
Message-ID: 199805141321.PAA04595@gate.va.fh-ulm.de (view raw or flat)
Thread:
Lists: pgsql-interfaces
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(at)fh-ulm(dot)de
Fachhochschule Ulm  Prittwitzstr. 10   89075 Ulm
Tel.: +49 (0)731-502-8220             Fax: -8270

 Nemo me impune lacessit.


Responses

pgsql-interfaces by date

Next:From: Teodorescu ConstantinDate: 1998-05-14 14:28:35
Subject: Re: [INTERFACES] Re: Index unused with OR?
Previous:From: Manuel ReiterDate: 1998-05-14 14:11:35
Subject: JDBC-driver for postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group