Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C48CD2E005B for ; Wed, 27 Feb 2008 14:45:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 67806-02-3 for ; Wed, 27 Feb 2008 14:45:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from hunley.homeip.net (hunley.homeip.net [69.12.222.146]) by postgresql.org (Postfix) with ESMTP id BB59E2E0050 for ; Wed, 27 Feb 2008 14:45:29 -0400 (AST) Received: from douglas.hunley.homeip.net (d60-34-189.col.wideopenwest.com [65.60.189.34]) (authenticated bits=0) by hunley.homeip.net (8.13.1/8.13.1) with ESMTP id m1RIjIgo001693 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Wed, 27 Feb 2008 13:45:25 -0500 From: Douglas J Hunley To: Bill Moran Subject: Re: questions about CLUSTER Date: Wed, 27 Feb 2008 13:45:11 -0500 User-Agent: KMail/1.9.7 Cc: pgsql-performance@postgresql.org References: <200802271101.57169.doug@hunley.homeip.net> <20080227124057.9b3937ce.wmoran@collaborativefusion.com> <200802271335.16397.doug@hunley.homeip.net> In-Reply-To: <200802271335.16397.doug@hunley.homeip.net> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200802271345.11897.doug@hunley.homeip.net> X-Greylist: Sender succeeded SMTP AUTH authentication, not delayed by milter-greylist-3.0 (hunley.homeip.net [69.12.222.146]); Wed, 27 Feb 2008 13:45:25 -0500 (EST) X-Virus-Scanned: ClamAV version 0.92.1, clamav-milter version 0.92.1 on hunley.homeip.net X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL=0.004 X-Spam-Level: X-Archive-Number: 200802/376 X-Sequence-Number: 28918 On Wednesday 27 February 2008 13:35:16 Douglas J Hunley wrote: > > > 2) is there any internal data in the db that would allow me to > > > programmatically determine which tables would benefit from being > > > clustered? 3) for that matter, is there info to allow me to determine > > > which index it should be clustered on in cases where the table has more > > > than one index? > > > > The pg_stat_user_indexes table keeps stats on how often the index is > > used. Indexes that are used frequently are candidates for clustering. > > I had just started looking at this actually. ok, so for a follow-on, should I be more concerned with idx_scan, idx_tup_read, or idx_tup_fetch when determining which indexes are 'good' candidates? again, tia. i feel like such a noob around here :) -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net I don't get paid. I just get a tip as the money passes through my hands on its way from my employer to my debtors.