Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CBFC82E012F for ; Wed, 13 Feb 2008 19:15:50 -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 25182-10 for ; Wed, 13 Feb 2008 19:15:40 -0400 (AST) Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.227]) by postgresql.org (Postfix) with ESMTP id 46B752E010D for ; Wed, 13 Feb 2008 19:15:40 -0400 (AST) Received: by qb-out-0506.google.com with SMTP id o12so9907218qba.17 for ; Wed, 13 Feb 2008 15:15:38 -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:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; bh=90DyDCJ/nUc1HULWUKEkYAgBAdbzYdiS3ScCR0YPYxY=; b=eFRzyXe5tJCEmcQVHxvUwY5HUAAj5SRfV2jRHAWZs0ardSPSzD5daG9liqxXyU76UDVPQPx3fcGYv998MzWWgn+ta9MvvHiOJ+sJ2YyKHYPZRVYt8ITffkv/uxFHCLXZpI5mTORJG9FvpPz3+BkeFuNBXrgnbQWVh89cmGkeJgo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=kBRFL8x2Jc0QAEduE7uC988Lz3D7jzMhTE6ZcyoGjky8zlIs6vmQP5N6k4zgtplhJfOtdNH6EF77fs551DC8VNwMMaD44yKqaE6HHo+zTP7X+e5vpYAUnqYRJbK6PCyHQwYODQuLdMXyljeNDC70BA8VCmh/56O2JqIsrVT1DrA= Received: by 10.141.1.2 with SMTP id d2mr408062rvi.129.1202944537193; Wed, 13 Feb 2008 15:15:37 -0800 (PST) Received: by 10.141.195.16 with HTTP; Wed, 13 Feb 2008 15:15:37 -0800 (PST) Message-ID: Date: Thu, 14 Feb 2008 00:15:37 +0100 From: "Peter Kovacs" To: "Jeff Frost" Subject: Re: How much space do database objects take up in data files Cc: "Carol Walter" , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <96FC91B8-A568-4781-80D5-0319AA27E8A3@indiana.edu> X-Google-Sender-Auth: 9feee890403f577b X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/168 X-Sequence-Number: 28242 Thanks, appears to work great!! Peter On Feb 13, 2008 9:46 PM, Jeff Frost wrote: > On Wed, 13 Feb 2008, Carol Walter wrote: > > > I've struggled with this one too. You can get the size of databases with > > SELECT pg_database_size('database name'); You can get the size of tables > > with SELECT pg_relation_size('table name'); > > > > Carol > > On Feb 13, 2008, at 1:43 PM, Peter Kovacs wrote: > > > >> Hi, > >> > >> How can I find out how much space is taken up by database objects in data > >> files? > > Give this query a try. It's a variation of one posted on this list some time > ago. Unfortunately, I'm not sure who to credit for the original post. This > one takes into account index and toast size and sorts descending by totalsize. > > SELECT nspname, relname, > pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize > FROM > (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize, > COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint > FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize, > CASE WHEN reltoastrelid=0 THEN 0 > ELSE pg_relation_size(reltoastrelid) > END AS toastsize, > CASE WHEN reltoastrelid=0 THEN 0 > ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct > WHERE ct.oid = cl.reltoastrelid)) > END AS toastindexsize > FROM pg_class cl, pg_namespace ns > WHERE cl.relnamespace = ns.oid > AND ns.nspname NOT IN ('pg_catalog', 'information_schema') > AND cl.relname IN > (SELECT table_name FROM information_schema.tables > WHERE table_type = 'BASE TABLE')) ss > ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC; > > > > -- > Jeff Frost, Owner > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >