From: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
To: pgsql-interfaces@postgreSQL.org
Subject: Re: Index unused with OR?
Date: Thu, 14 May 1998 15:59:04 +0100
Message-ID: <199805141359.PAA04711@gate.va.fh-ulm.de> (raw)
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=> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: mstaedt@va-sigi.va.fh-ulm.de
Subject: Re: Index unused with OR?
In-Reply-To: <199805141359.PAA04711@gate.va.fh-ulm.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox