Returning schema name with table name

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

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
====================================================

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
====================================================

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.