Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5E5A52E2DCC; Mon, 11 Feb 2008 22:42:31 -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 01803-07; Mon, 11 Feb 2008 22:42:28 -0400 (AST) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id 75C882E2DC2; Mon, 11 Feb 2008 22:42:28 -0400 (AST) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id m1C2gKjg066892 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Mon, 11 Feb 2008 19:42:23 -0700 (MST) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.14.2/8.14.2) with ESMTP id m1C2gKeD042868; Mon, 11 Feb 2008 19:42:20 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.14.2/8.14.2/Submit) id m1C2gJx3042867; Mon, 11 Feb 2008 19:42:19 -0700 (MST) (envelope-from mfuhr) Date: Mon, 11 Feb 2008 19:42:19 -0700 From: Michael Fuhr To: Scott Marlowe Cc: salman , pgsql-novice@postgresql.org, pgsql-admin@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [PERFORM] Question about CLUSTER Message-ID: <20080212024219.GA42737@winnie.fuhr.org> References: <47B0AA1F.8040101@quietcaresystems.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/146 X-Sequence-Number: 28220 On Mon, Feb 11, 2008 at 03:33:37PM -0600, Scott Marlowe wrote: > On Feb 11, 2008 2:03 PM, salman wrote: > > I'm planning to cluster a few large tables in our database but I'm > > unable to find any recommendations/documentation on best practices -- > > Mainly, whether it's better to use an index which has a higher idx_scan > > value, a higher idx_tup_read value, or the higest idx_tup_fetch value. > > > > I'm assuming that idx_tup_read would probably be the best choice, but > > want to get other opinions before proceeding. > > If you've got two indexes that are both being hit a lot, it might be > worth looking into their correlation, and if they get used a lot > together, look at creating an index on both. > > But I'd guess that idx_tup_read would be a good bet. You might also consider the ratio idx_tup_read::float8 / idx_scan to see which indexes access a lot of rows per scan. -- Michael Fuhr