Message-Id: <199805141359.PAA04711@gate.va.fh-ulm.de>
From: "Olaf Mittelstaedt" <mstaedt@va-sigi.va.fh-ulm.de>
Organization: Verwaltung FH Ulm
To: pgsql-interfaces@postgreSQL.org
Date: Thu, 14 May 1998 15:59:04 +0100
MIME-Version: 1.0
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: Quoted-printable
Subject: Re: Index unused with OR?
Priority: normal
In-reply-to: <199805141341.QAA02105@flex.flex.ro>
References: <199805141321.PAA04595@gate.va.fh-ulm.de>

> 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=3D> select count(*) from p;
count
-----
 8331
(1 row)

w=3D> vacuum;
VACUUM
w=3D> explain select * from p where m =3D 29000;
NOTICE:  QUERY PLAN:

Index Scan on p  (cost=3D0.00 size=3D0 width=3D10)

EXPLAIN
w=3D> explain select * from p where (m=3D29000) or (m=3D30000);
NOTICE: QUERY PLAN:

Seq Scan on p (cost=3D0.00 size=3D0 width=3D10)

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=FBk, ash nazg gimbatul,
  ash nazg thrakatul=FBk agh burzum-ishi krimpatul.