Dammit pg_total_relation_size includes toast data. Thumb problems and to quick to hit send. :(
On Thu, Jun 4, 2015 at 10:07 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > 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. > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
