X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 62EFFD1C4E1 for ; Mon, 4 Aug 2003 21:14:50 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 49469-07 for ; Mon, 4 Aug 2003 18:13:35 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 3345BD1C518 for ; Mon, 4 Aug 2003 18:14:39 -0300 (ADT) Received: (qmail 14556 invoked by uid 500); 4 Aug 2003 21:19:51 -0000 Date: Mon, 4 Aug 2003 16:19:51 -0500 From: Bruno Wolff III To: "Valsecchi, Patrick" Cc: pgsql-performance@postgresql.org Subject: Re: Indexes not used for "min()" Message-ID: <20030804211951.GA14522@wolff.to> Mail-Followup-To: "Valsecchi, Patrick" , pgsql-performance@postgresql.org References: <4E2CE3FB9E1B744482F6669A9AE2F72E68F0E8@NSTAREXCH1.windows.nagrastar.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4E2CE3FB9E1B744482F6669A9AE2F72E68F0E8@NSTAREXCH1.windows.nagrastar.com> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/44 X-Sequence-Number: 2861 On Mon, Aug 04, 2003 at 15:05:08 -0600, "Valsecchi, Patrick" 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.