I'm not sure why you are adding toast to table size, since pg_relation_size already does that.
http://www.postgresql.org/docs/9.3/interactive/functions-admin.html This query might work better and faster for you. SELECT n.nspname as schema, c.relname as table, a.rolname as owner, c.relfilenode as filename, c.reltuples::integer, pg_size_pretty(pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as size, pg_size_pretty(pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) )) as total_size, pg_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as size_bytes, pg_total_relation_size( quote_ident( n.nspname ) || '.' || quote_ident( c.relname ) ) as total_size_bytes, CASE WHEN c.reltablespace = 0 THEN 'pg_default' ELSE (SELECT t.spcname FROM pg_tablespace t WHERE (t.oid = c.reltablespace) ) END as tablespace FROM pg_class c JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_authid a ON ( a.oid = c.relowner ) WHERE c.relname = 'sensor' AND n.nspname = 'devtest'; On Thu, Jun 4, 2015 at 9:50 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Hans Guijt <h...@terma.com> writes: > > I have a Postgres 9.3.7 database, freshly created on Ubuntu 14 LTS 64 > bit, and at this time almost completely empty. I'm attempting to find the > size of a table, using the following code: > > SELECT > > pg_relation_size (stat.relid), > > CASE WHEN cl.reltoastrelid = 0 THEN > > 0 > > ELSE > > pg_relation_size (cl.reltoastrelid) + COALESCE (( > > SELECT SUM (pg_relation_size (indexrelid)) FROM pg_index WHERE > indrelid=cl.reltoastrelid > > ), 0)::int8 > > END, > > COALESCE ((SELECT SUM (pg_relation_size (indexrelid)) FROM pg_index > WHERE indrelid=stat.relid), 0)::int8 > > FROM pg_stat_all_tables stat > > JOIN pg_class cl ON cl.oid=stat.relid > > JOIN pg_namespace ns ON cl.relnamespace=ns.oid > > WHERE UPPER (cl.relname) = UPPER ('sensor') > > AND UPPER (ns.nspname) = UPPER ('devtest') > > Getting rid of the useless join to pg_stat_all_tables would probably help; > there's a lot of computation in that view. > > regards, tom lane > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
