Returning schema name with table name

[Andrus]

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.

[Pavel Stehule]

Post by Andrus
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

Post by Andrus
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.
--
http://www.postgresql.org/mailpref/pgsql-general

[Thomas Markus]

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

Post by Andrus
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 [phone] | Fax -50 | ***@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
====================================================

[Andrus]

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]

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)

Post by Andrus
Thank you very much.
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 [phone] | Fax -50 | ***@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
====================================================

[Andrus]

Thomas,

Post by Thomas Markus
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.