Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 401A99FB28D for ; Mon, 25 Sep 2006 19:31:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 57177-10 for ; Mon, 25 Sep 2006 22:31:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 1AB699FB1DE for ; Mon, 25 Sep 2006 19:31:45 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Mon, 25 Sep 2006 22:31:43 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 25 Sep 2006 17:31:44 -0500 Subject: Re: best way to choose index to cluster on? From: Scott Marlowe To: adey Cc: Chris Hoover , "pgsql-admin@postgresql.org" In-Reply-To: <1c66bda80609251527i4e8bad89jb6c1420cd44a8a33@mail.gmail.com> References: <1d219a6f0609251357y33e74c56u4d283af07cd45360@mail.gmail.com> <1c66bda80609251527i4e8bad89jb6c1420cd44a8a33@mail.gmail.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1159223503.26848.46.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 25 Sep 2006 17:31:44 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200609/277 X-Sequence-Number: 23191 Note that PostgreSQL doesn't auto-update clustered indexes. i.e. they deteriorate over time, and require being re-clustered after a while. On Mon, 2006-09-25 at 17:27, adey wrote: > A good guide for a clustered index is:- > Will the index be unique, static and narrow. > If not, avoid clustered indexing as they require extra work on disk to > maintain their sequence. > > > On 9/26/06, Chris Hoover wrote: > I am looking to squeeze a bit more speed out of my database by > clustering most of my tables. However, on tables with > multiple indexes, how is the best way to choose the index. I > am thinking I want to use the index with the largest > pg_stat_user_indexes.idx_tup_read. Would this be correct? If > not, what column(s) and views should I be looking at to find > the most popular index? > > Secondly, I have some partial indexes on a very active table, > and it tends to have the highest idx_tup_read for that table. > Is is possible to cluster on a partial index? > > Thanks, > > Chris >