Обсуждение: 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
====================================================

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