Re: 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 <melvin6925(at)gmail(dot)com>
wrote:

> Correction, pg_relation_size includes toast data.
>
> On Thu, Jun 4, 2015 at 10:03 AM, Melvin Davidson <melvin6925(at)gmail(dot)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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Hans Guijt <hg(at)terma(dot)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(at)postgresql(dot)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.