Re: Indexes not used for "min()"

On Mon, Aug 04, 2003 at 15:05:08 -0600,
"Valsecchi, Patrick" <patrick(dot)valsecchi(at)nagrastar(dot)com> wrote:
> Sir,
>
> I did a search with the "index" keyword on the mailing list archive and it did come with no result. Sorry if it's a known bug.

It isn't a bug. It is a design trade off. The database has no special
knowledge about the min and max aggregate functions that would allow it
to use indexes.

> But in general, I think the indexes are under used. I have several queries that are taking at least 30 minutes and that would take less than one minute if indexes where used (comes with a comparison I made with Oracle). In particular, I have the feeling that indexes are not used for "IN" statements (within a where clauses).

There are know performance problems with in. These are addressed in 7.4
which will be going into beta any time now. You can usually rewrite IN
queries to use exists instead, which will speed things up.

Also be sure to run analyze (or vacuum analyze) so that the database
server has accurate statistics on which to bases its decisions.

> On the same subject, I'd add that the ability to provide plan "hints" within the queries (like provided in Oracle) would be helpful. I know that the Postgres optimizer is supposed to do a better job than the one from Oracle, but an optimizer cannot be perfect for every cases.

"Hints" aren't going to happen. They cause maintainance problems.
You can disable features for a session (such as sequential scans)
and try to get a plan you like. But generally, rather than adding
this to you application code, you should try to find out why the
planner is making the wrong choice. Adjusting the relative costs
for doing things might allow the planner to do a much better job for
you.

This kind of thing gets discussed on the performance list.