Thanks, this is most helpful. I originally found that query somewhere on the internet and used it as-is.
If I drop the restriction on table name I get a list that also includes indexes, constraints, etc. Is there a way to restrict the returned set to tables only? Hans Guijt From: Melvin Davidson [mailto:melvin6...@gmail.com] Sent: 04 June 2015 16:08 To: Tom Lane Cc: Hans Guijt; pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_relation_size performance issue 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<mailto: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<mailto: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<mailto:t...@sss.pgh.pa.us>> wrote: Hans Guijt <h...@terma.com<mailto: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<mailto: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. [Image removed by sender.] -- Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. [Image removed by sender.] -- Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. [Image removed by sender.]
