Re: Index unused with OR? - Mailing list pgsql-interfaces

From Olaf Mittelstaedt
Subject Re: Index unused with OR?
Date
Msg-id 199805141359.PAA04711@gate.va.fh-ulm.de
Whole thread Raw
In response to Re: [INTERFACES] Index unused with OR?  (Teodorescu Constantin <teo@flex.flex.ro>)
Responses Re: [INTERFACES] Re: Index unused with OR?
Re: [INTERFACES] Re: Index unused with OR?
List pgsql-interfaces
> 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.


pgsql-interfaces by date:

Previous
From: Teodorescu Constantin
Date:
Subject: Re: [INTERFACES] Index unused with OR?
Next
From: Manuel Reiter
Date:
Subject: JDBC-driver for postgresql