Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0E3C365007C for ; Wed, 12 Nov 2008 19:44:20 -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 42285-07 for ; Wed, 12 Nov 2008 19:44:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.237]) by mail.postgresql.org (Postfix) with ESMTP id 8498164FD2A for ; Wed, 12 Nov 2008 19:44:09 -0400 (AST) Received: by qb-out-0506.google.com with SMTP id d8so577713qbc.23 for ; Wed, 12 Nov 2008 15:44:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=PdKYCTERVBwTL/U+PlJVE8Fwcm78zcP0JkZXdcRclUM=; b=xCO9E6IgO7HiTxbAKjFAfMg44OAnBbzUndfErdxln+gxoGMLssHTNSNVgs1hax3h0q XBj7/KUi1BcI/Rz0zJxD0iaMYDx/N0uk/tOrd+/GvwciqCzFn1ZYQSJZ+to0Qs6TBLcP K1rF2ySs/3qvdlSQFkIIdoiDNBxvEQ6w1tzY4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=m837YAedCOryUJokbhHG2VF6UgRWEDbt+pVK6S505chsmonoMwtNckPc4Z0Vv1gTS3 h/pavszFh7PCjLkkt5bdhi+mzhMe328jGWPJufpygQuEwpgiPJ6+L0eVtJq4duHBpjYL erGXOfa7xFTw1fCA0M6zcLz1Cyv/97X3r6u+s= Received: by 10.114.170.1 with SMTP id s1mr6432929wae.57.1226533447625; Wed, 12 Nov 2008 15:44:07 -0800 (PST) Received: by 10.114.39.11 with HTTP; Wed, 12 Nov 2008 15:44:07 -0800 (PST) Message-ID: Date: Wed, 12 Nov 2008 16:44:07 -0700 From: "Scott Marlowe" To: "Franck Routier" Subject: Re: Disk usage question Cc: pgsql-performance@postgresql.org In-Reply-To: <1226509351.21212.20.camel@franck-laptop> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1226509351.21212.20.camel@franck-laptop> 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/114 X-Sequence-Number: 31845 On Wed, Nov 12, 2008 at 10:02 AM, Franck Routier wrote: > 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 ? Can't really say. Is this a table with a single integer column? Then it's way too big. If it's got plenty of columns, some of which are text or bytea then probably not. What does vacuum verbose say about your tables / db? > - 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) ? No, single field indexes are not as fast as multi-field indexes when the where clause hits the fields starting from the left of the index. Note that an index on (a,b) will not help a where clause on only b. > - 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) ' Those look the same, I assume you meant USING (B,A) for one. Assuming both fields are used by the query's where clause, the more selective one should be first. I think. testing will tell for sure. > 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); Maybe. If one is very selective then there's no great need for the other anyway. > - 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 ? Yes, if you have a lot of failed inserts. a failed insert = insert + delete.