Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CAEB32E0045 for ; Wed, 27 Feb 2008 14:35:43 -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 62247-07 for ; Wed, 27 Feb 2008 14:35:39 -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 827622E003E for ; Wed, 27 Feb 2008 14:35:39 -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 m1RIZNTt001407 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Wed, 27 Feb 2008 13:35:33 -0500 From: Douglas J Hunley To: Bill Moran Subject: Re: questions about CLUSTER Date: Wed, 27 Feb 2008 13:35:16 -0500 User-Agent: KMail/1.9.7 Cc: pgsql-performance@postgresql.org References: <200802271101.57169.doug@hunley.homeip.net> <20080227124057.9b3937ce.wmoran@collaborativefusion.com> In-Reply-To: <20080227124057.9b3937ce.wmoran@collaborativefusion.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200802271335.16397.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:35:33 -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/375 X-Sequence-Number: 28917 On Wednesday 27 February 2008 12:40:57 Bill Moran wrote: > In response to Douglas J Hunley : > > After reviewing > > http://www.postgresql.org/docs/8.3/static/sql-cluster.html a couple of > > times, I have some questions: > > 1) it says to run analyze after doing a cluster. i'm assuming autovacuum > > will take care of this? or should i go ahead and do the analyze 'now' > > instead of waiting? > It's always a good idea to analyze after major DB operations. Autovacuum > only runs so often. Also, it won't hurt anything, so why risk not doing > it? being overly-cautious. i was concerned about both autovac and me doing analyzes over each other > > > 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. > > > 4) for tables with >1 indexes, does clustering on one index negatively > > impact queries that use the other indexes? > > Not likely. Clustering only really helps performance if you have an index > that is used to gather ranges of data. For example, if you frequently > do things like SELECT * FROM log WHERE logdate > 'somedate" and < > 'somedate, you might benefit from clustering on logdate. > > But it doesn't really do much if you're only ever pulling one record at a > time. It's the kind of thing that you really need to experiment with to > understand whether it will have a worthwhile performance impact on your > data and your workload. I doubt if there's any pat answer. makes sense. > > > 5) is it better to cluster on a compound index (index on > > lastnamefirstname) or on the underlying index (index on lastname)? > > If cluster helps you at all, it's going to help if you have an index that's > frequently used to fetch ranges of data. Whether that index is compound or > not isn't likely to factor in. understood. i didn't really think it would matter, but its easier to ask than to screw up performance for existing customers :) -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net If a turtle doesn't have a shell, is he homeless or naked?