Thread: Returning schema name with table name
SELECT oid, relname::char(35) as Table_Name,
pg_size_pretty(pg_total_relation_size(oid))::VARCHAR(15) as
Total_Table_Size
FROM pg_class
where pg_total_relation_size(oid)/(1024*1024)>0
ORDER BY pg_total_relation_size(oid) desc
returns table names with size greater than 1 MB
How to modify this so that schema name is also returned?
I have lot of tables with same name and thus this output is difficult to
understand.
pg_class seems not contain schema names.
Andrus.
2008/11/21 Andrus <kobruleht2@hot.ee>: > SELECT oid, relname::char(35) as Table_Name, > pg_size_pretty(pg_total_relation_size(oid))::VARCHAR(15) as > Total_Table_Size > FROM pg_class > where pg_total_relation_size(oid)/(1024*1024)>0 > ORDER BY pg_total_relation_size(oid) desc > add SELECT n.nspname and FROM pg_class JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace ... Regards Pavel Stehule > returns table names with size greater than 1 MB > > How to modify this so that schema name is also returned? > I have lot of tables with same name and thus this output is difficult to > understand. > pg_class seems not contain schema names. > > Andrus. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hi,
my standard query (adapted to 1mb size) is:
select
t.spcname as "tablespace"
, pg_get_userbyid(c.relowner) as "owner"
, n.nspname as "schema"
, relname::text as "name"
, pg_size_pretty(pg_total_relation_size(c.oid))::text as "total size"
, case
when c.relkind='i' then 'index'
when c.relkind='t' then 'toast'
when c.relkind='r' then 'table'
when c.relkind='v' then 'view'
when c.relkind='c' then 'composite type'
when c.relkind='S' then 'sequence'
else c.relkind::text
end as "type"
from
pg_class c
left join pg_namespace n on n.oid = c.relnamespace
left join pg_tablespace t on t.oid = c.reltablespace
where
(pg_total_relation_size(c.oid)>>20)>0 and c.relkind!='t'
order by
c.relkind desc, pg_total_relation_size(c.oid) desc
Andrus schrieb:
> SELECT oid, relname::char(35) as Table_Name,
> pg_size_pretty(pg_total_relation_size(oid))::VARCHAR(15) as
> Total_Table_Size
> FROM pg_class
> where pg_total_relation_size(oid)/(1024*1024)>0
> ORDER BY pg_total_relation_size(oid) desc
>
> returns table names with size greater than 1 MB
>
> How to modify this so that schema name is also returned?
> I have lot of tables with same name and thus this output is difficult
> to understand.
> pg_class seems not contain schema names.
>
> Andrus.
>
>
--
Thomas Markus
====================================================
proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin |
Tel +49 (0)30 2936399-22 | Fax -50 | t.markus@proventis.net
-----------------------------------------------------------------
Geschäftsführer: Norman Frischmuth | Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917
-----------------------------------------------------------------
Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008:
http://www.proventis.net/website/live/blueant/veranstaltungen.html
====================================================
Attachment
> my standard query (adapted to 1mb size) is: Thank you very much. This query shows toast files in a cryptic way: db_owner pg_toast pg_toast_40552_index 1352 kB How to change it so that it shows also relation name whose data pg_toast_40552_index contains? It is not possible to determine from this query output which data is contained in pg_toast_40552_index file. Andrus.
it shows all except toast entries. for included values see http://www.postgresql.org/docs/8.3/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE function |pg_total_relation_size|(oid) Andrus schrieb: >> my standard query (adapted to 1mb size) is: > > Thank you very much. > This query shows toast files in a cryptic way: > > db_owner pg_toast pg_toast_40552_index 1352 kB > > How to change it so that it shows also relation name whose data > pg_toast_40552_index contains? > It is not possible to determine from this query output which data is > contained in pg_toast_40552_index file. > > Andrus. > -- Thomas Markus ==================================================== proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin | Tel +49 (0)30 2936399-22 | Fax -50 | t.markus@proventis.net ----------------------------------------------------------------- Geschäftsführer: Norman Frischmuth | Sitz: Berlin Handelsregister: AG Berlin-Charlottenburg, HR 82917 ----------------------------------------------------------------- Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008: http://www.proventis.net/website/live/blueant/veranstaltungen.html ====================================================
Attachment
Thomas, > it shows all except toast entries. for included values see > http://www.postgresql.org/docs/8.3/static/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE > function |pg_total_relation_size|(oid) I'm sorry I was not clear. For my db your query returns row like db_owner pg_toast pg_toast_40552_index 1352 kB It would be nice if query output allows to find which relation contains 1.3 MB toast data. How to change this query by adding column "parent" which shows parent table name for toast enties? Currently we must find this relation manually from OID (40552) contained in name. Andrus.