Обсуждение: select actual data size for a relation?

Поиск
Список
Период
Сортировка

select actual data size for a relation?

От
Kevin Kempter
Дата:
Hi all;

Anyone know how to select / calculate the actual data size for a table or
index?

NOT the disk usage as in:
 pg_class.relpages -- shows disk usage including bloat
pg_relation_size() -- same as pg_class.relpages but in bytes
or pg_total_relation_size()  -- total disk usage inclusive of bloat and
indexes

I want only the data size. I tried grabbing the sum of all the avg_width cols
from pg_stats and multiplying times the pg_class.reltuples but this seems way
too big.

Thoughts?



Thanks in advance

Re: select actual data size for a relation?

От
"Joshua D. Drake"
Дата:
On Thu, 2009-10-22 at 17:41 -0600, Kevin Kempter wrote:
> Hi all;
>
> Anyone know how to select / calculate the actual data size for a table or
> index?

select pg_total_relation_size('relation')

>
> NOT the disk usage as in:
>  pg_class.relpages -- shows disk usage including bloat
> pg_relation_size() -- same as pg_class.relpages but in bytes
> or pg_total_relation_size()  -- total disk usage inclusive of bloat and
> indexes
>
> I want only the data size. I tried grabbing the sum of all the avg_width cols
> from pg_stats and multiplying times the pg_class.reltuples but this seems way
> too big.
>
> Thoughts?
>
>
>
> Thanks in advance
>


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

Re: select actual data size for a relation?

От
"Joshua D. Drake"
Дата:
On Thu, 2009-10-22 at 17:41 -0600, Kevin Kempter wrote:
> Hi all;
>
> Anyone know how to select / calculate the actual data size for a table or
> index?

select pg_total_relation_size('relation')

>
> NOT the disk usage as in:
>  pg_class.relpages -- shows disk usage including bloat
> pg_relation_size() -- same as pg_class.relpages but in bytes
> or pg_total_relation_size()  -- total disk usage inclusive of bloat and
> indexes
>
> I want only the data size. I tried grabbing the sum of all the avg_width cols
> from pg_stats and multiplying times the pg_class.reltuples but this seems way
> too big.
>
> Thoughts?
>
>
>
> Thanks in advance
>


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander