Sorry, I left out AND relkind = 'r'
will which restrict to just tables. On Thu, Jun 4, 2015 at 10:24 AM, Hans Guijt <h...@terma.com> wrote: > 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> > 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. [image: 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: 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: 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.
