Correction, pg_relation_size includes toast data. On Thu, Jun 4, 2015 at 10:03 AM, Melvin Davidson <melvin6...@gmail.com> wrote:
> 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. > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
