X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 003F4D1B4BE for ; Mon, 2 Feb 2004 19:17:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 47644-04 for ; Mon, 2 Feb 2004 15:17:39 -0400 (AST) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 79B2AD1D299 for ; Mon, 2 Feb 2004 15:17:38 -0400 (AST) Received: from [134.22.69.227] (dyn-69-227.tor.dsl.tht.net [134.22.69.227]) by tht.net (Postfix) with ESMTP id 4459D76B0B; Mon, 2 Feb 2004 14:17:42 -0500 (EST) Subject: Re: "Overlaping" indexes From: Rod Taylor To: Tomasz Myrta Cc: Scott Marlowe , Rigmor Ukuhe , Postgresql Performance In-Reply-To: <401E9A37.1070909@klaster.net> References: <401E9A37.1070909@klaster.net> Content-Type: text/plain; charset=iso-8859-2 Message-Id: <1075749471.308.105.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Mon, 02 Feb 2004 14:17:52 -0500 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/9 X-Sequence-Number: 5533 On Mon, 2004-02-02 at 13:43, Tomasz Myrta wrote: > Dnia 2004-02-02 19:30, Użytkownik scott.marlowe napisał: > > Not entirely, since it only has to sort two columns, it will be smaller, > > and will therefore be somewhat faster. > > Can you say something more about it? Will it be enough faster to keep > them both? Did anyone make such tests? You can actually come up with test cases where both indexes are useful. The three column index will have more data to sift through. That said, having both indexes used means there is less ram available for cache. The biggest mistake I see is people doing everything they can to optimize a single query, then they optimize the next query, etc. When you consider the entire set of queries, those two indexes are very likely to slow select throughput down due to increased memory requirements and the system hitting disk a little more often. It's similar to the mistake of benchmarking a set of 1000 row tables and optimizing memory settings for that, then using that configuration on the 10M row tables in production.