Re: index use again and again

On Tue, 12 Feb 2002, Holger Marzen wrote:

> I read the Postgres boog, browsed the mailing list archives and have
> still no clue.
>
> I have a table with about 150.000 rows and put some indexes (not unique)
> on it. If I use "=" in the where clause tha index is used, if I use ">"
> or "between" then it is not used. It happens both with 7.1.3 and the new
> 7.2:

Previous discussion on this topic give me the idea that index use is
determined by the optimizer and it will use and index or sequential
depending on some percentage of the records it thinks will be returned.
I.e. is it more economical to just go through them all or spend time
jumping around getting the right records. Of course one of the hackers
will be able to give you a much better and probably correct explanation.

Since the columns are not unique I'd guess there are duplicate values in
them. An interesting thought - if there are only a few unique values
per column - is, would a clustering of the column/index get the
optimizer to use the index.
I'm sure this has been discussed in previous threads also but my
brain is full so I have to keep only the current stuff around.

Regards,
Rod
--
Let Accuracy Triumph Over Victory

Zetetic Institute
"David's Sling"
Marc Stiegler