Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6B6D264FE19 for ; Wed, 12 Nov 2008 18:28:36 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 28371-08 for ; Wed, 12 Nov 2008 18:28:20 -0400 (AST) X-Greylist: delayed 05:25:37.289603 by SQLgrey-1.7.6 Received: from smtp20.orange.fr (smtp20.orange.fr [193.252.22.29]) by mail.postgresql.org (Postfix) with ESMTP id 169F064FD2A for ; Wed, 12 Nov 2008 18:28:16 -0400 (AST) Received: from smtp20.orange.fr (mwinf2013 [172.22.130.41]) by mwinf2015.orange.fr (SMTP Server) with ESMTP id 032A31C03239 for ; Wed, 12 Nov 2008 18:02:41 +0100 (CET) Received: from me-wanadoo.net (localhost [127.0.0.1]) by mwinf2013.orange.fr (SMTP Server) with ESMTP id 1816C1C0008B for ; Wed, 12 Nov 2008 18:02:34 +0100 (CET) Received: from axege.com (LNeuilly-152-22-82-133.w193-251.abo.wanadoo.fr [193.251.23.133]) by mwinf2013.orange.fr (SMTP Server) with ESMTP id EF2951C0008E for ; Wed, 12 Nov 2008 18:02:33 +0100 (CET) X-ME-UUID: 20081112170233979.EF2951C0008E@mwinf2013.orange.fr Received: from localhost (localhost [127.0.0.1]) by axege.com (Postfix) with ESMTP id 9E91C5013B for ; Wed, 12 Nov 2008 18:11:37 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at myhostname="axege.com." Received: from axege.com ([127.0.0.1]) by localhost (axege.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id jgerYZwjd+qX for ; Wed, 12 Nov 2008 18:11:37 +0100 (CET) Received: from [10.0.0.69] (unknown [192.168.1.1]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by axege.com (Postfix) with ESMTPSA id 03E455012E for ; Wed, 12 Nov 2008 18:11:37 +0100 (CET) Subject: Disk usage question From: Franck Routier To: pgsql-performance@postgresql.org Content-Type: text/plain Organization: =?ISO-8859-1?Q?Ax=E8ge?= Sarl Date: Wed, 12 Nov 2008 18:02:31 +0100 Message-Id: <1226509351.21212.20.camel@franck-laptop> Mime-Version: 1.0 X-Mailer: Evolution 2.24.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/111 X-Sequence-Number: 31842 Hi, I have to manage a database that is getting way too big for us. Currently db size is 304 GB. One table is accounting for a third of this space. The table itself has 68.800.000 tuples, taking 28GB. There are 39 indices on the table, and many of them use multiple columns. A lot of these indices share the same column(s). The indices are taking 95GB. So, here are my questions: - do these figures seem normal or is there likely a bigger problem ? - when indices share a column, is it worth creating several multi-column indices (as we do now), or would we get the same result (from a performance point of view) by creating several single column indices (one for each column) ? - does the order in which a multi-column index is created matter ? That is, if I have a column A with less discriminating values and a column B with more discriminating values, does it matter if I: 'CREATE INDEX myindex ON mytable USING (A,B) ' or 'CREATE INDEX myindex ON mytable USING (A,B) ' Is the second solution likely to behave faster ? Or is it simply better to: CREATE INDEX myindexa ON mytable USING (A); CREATE INDEX myindexb ON mytable USING (B); - as we do many insert and very few update/delete, I thought REINDEX was going to be superfluous. But REINDEXing is often needed to keep the size of the db _relatively_ reasonable. Does it sound normal ? Thanks for any tip, Franck