Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9EAC72E0064 for ; Wed, 13 Feb 2008 17:06:01 -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 94552-01 for ; Wed, 13 Feb 2008 17:05:57 -0400 (AST) Received: from po-out-1718.google.com (po-out-1718.google.com [72.14.252.152]) by postgresql.org (Postfix) with ESMTP id 955F32E003A for ; Wed, 13 Feb 2008 17:05:57 -0400 (AST) Received: by po-out-1718.google.com with SMTP id c31so4398729poi.3 for ; Wed, 13 Feb 2008 13:05:56 -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=6r8LqJyv8V3ir1qrPDL9DWSf4hWS5qskbhJzCS7wT/0=; b=eoT0hPsartcUJfJfPROEeUkvmggStgnJAW8ZiWd5WFILn8POLwVxE29olxeothdVS/tFwgiQlx78fU4r0ya0iBVmxcfPRGNQ+cvVBaYDUsZedp/lKGu3ws8lkHMxxc1JhLboVa08IPSorUBuwaw9+xXEmbxu1y9kOH2pcllhHWw= 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=xiQB9/OZ1XKzgQUK2vOWlFEua7KNZQ76ZTG3zNS8qWkCONh8NLBydE3a/4IV4zlNIO5lgaokfTCOR+ZXvCMiKkeYNf46jJkb/c0AYDVKhg03tWI6SkZj/nAy/gSIO7xa7zJ+jRWPIOQTGDlrj0kOLTavqi2JQfQ4nTlRiVDST5g= Received: by 10.140.172.6 with SMTP id u6mr288561rve.257.1202936755655; Wed, 13 Feb 2008 13:05:55 -0800 (PST) Received: by 10.141.195.16 with HTTP; Wed, 13 Feb 2008 13:05:55 -0800 (PST) Message-ID: Date: Wed, 13 Feb 2008 22:05:55 +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: 6fe76494e0b934d1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/179 X-Sequence-Number: 28253 Howooow!!! Great!!! Thanks a lot. 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 >