Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B05C39FB20A for ; Mon, 25 Sep 2006 17:57:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 57550-01 for ; Mon, 25 Sep 2006 17:57:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.238]) by postgresql.org (Postfix) with ESMTP id ABD849FA2AD for ; Mon, 25 Sep 2006 17:57:51 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id t13so2039691wxc for ; Mon, 25 Sep 2006 13:57:50 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=VjKUIEs7ncyehItNh9HFEUNSLauTWThcUTKEefXHJohwiz5ioPCwVsgc8VrL13SFfhN6NWiSi35PfDgd3gOdxHj43uQSpBiGMDrJicNCABQRfjMco44NHuxvln4mYYdZscQF2IFZXRWxOW3+7EmFxihfWQma4IXnfqwGcKhcNjE= Received: by 10.70.111.2 with SMTP id j2mr18748wxc; Mon, 25 Sep 2006 13:57:50 -0700 (PDT) Received: by 10.70.75.5 with HTTP; Mon, 25 Sep 2006 13:57:50 -0700 (PDT) Message-ID: <1d219a6f0609251357y33e74c56u4d283af07cd45360@mail.gmail.com> Date: Mon, 25 Sep 2006 16:57:50 -0400 From: "Chris Hoover" To: "pgsql-admin@postgresql.org" Subject: best way to choose index to cluster on? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_36746_7156849.1159217870188" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.01 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_00_10, HTML_MESSAGE, SPF_PASS X-Spam-Level: * X-Archive-Number: 200609/274 X-Sequence-Number: 23188 ------=_Part_36746_7156849.1159217870188 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_36746_7156849.1159217870188 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_36746_7156849.1159217870188--