Re: Information about Pages, row versions of tables, indices

2008/12/26 Gerhard Wiesinger <lists@wiesinger.com>:
> Hello Pavel,
>
> Works fine.
>
> Any ideas how to optimzize the function calls to one for the output
> parameters (multiple select from pgstattuple where only one part is used)?


postgres=# select schemaname, tablename, table_len, dead_tuple_count
from (select (pgstattuple(quote_ident(schemaname) || '.' ||
quote_ident(tablename))).*, schemaname, tablename from pg_tables where
schemaname = 'public') a;
 schemaname | tablename | table_len | dead_tuple_count
------------+-----------+-----------+------------------
 public     | x         |      8192 |                0
 public     | foo       |         0 |                0
 public     | fooa      |      8192 |                0
(3 rows)

look on fce pg_size_pretty

postgres=# select schemaname, tablename, pg_size_pretty(table_len),
dead_tuple_count from (select (pgstattuple(quote_ident(schemaname) ||
'.' || quote_ident(tablename))).*, schemaname, tablename from
pg_tables) a;
     schemaname     |        tablename        | pg_size_pretty |
dead_tuple_count
--------------------+-------------------------+----------------+------------------
 pg_catalog         | pg_type                 | 48 kB          |
         0
 information_schema | sql_languages           | 8192 bytes     |
         0
 information_schema | sql_packages            | 8192 bytes     |
         0
 information_schema | sql_parts               | 8192 bytes     |
         0
 information_schema | sql_sizing              | 8192 bytes     |
         0
 pg_catalog         | pg_statistic            | 152 kB         |
         0
 information_schema | sql_sizing_profiles     | 0 bytes        |
         0
 pg_catalog         | pg_database             | 8192 bytes     |
         0
 pg_catalog         | pg_authid               | 112 kB         |
         0
 information_schema | sql_features            | 56 kB          |
         0
 information_schema | sql_implementation_info | 8192 bytes     |
         0
 pg_catalog         | pg_ts_config_map        | 16 kB          |
         0
 pg_catalog         | pg_ts_dict              | 8192 bytes     |
         0
 pg_catalog         | pg_ts_parser            | 8192 bytes     |
         0
 pg_catalog         | pg_ts_template          | 8192 bytes     |
         0
 pg_catalo

regards
Pavel Stehule

>
> I've included some selects which might be usefull for others, too.
>
> Thnx.

call

>
> Ciao,
> Gerhard
>
> --
> http://www.wiesinger.com/
>
> -------------------------------------------------------------------------------------------
> -- Table info
> -------------------------------------------------------------------------------------------
>
> SELECT schemaname,
>       tablename,
>       pg_relpages(schemaname || '.' || tablename) AS rel_pages,
>       (SELECT ROUND(table_len/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || tablename)) AS table_len_MB,
>       (SELECT tuple_count FROM pgstattuple(schemaname || '.' || tablename))
> AS tuple_count,
>       (SELECT ROUND(tuple_len/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || tablename)) AS tuple_len_MB,
>       (SELECT tuple_percent FROM pgstattuple(schemaname || '.' ||
> tablename)) AS tuple_percent,
>       (SELECT dead_tuple_count FROM pgstattuple(schemaname || '.' ||
> tablename)) AS dead_tuple_count,
>       (SELECT ROUND(dead_tuple_len/1024.0/1024.0,3) FROM
> pgstattuple(schemaname || '.' || tablename)) AS dead_tuple_len_MB,
>       (SELECT dead_tuple_percent FROM pgstattuple(schemaname || '.' ||
> tablename)) AS dead_tuple_percent,
>       (SELECT ROUND(free_space/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || tablename)) AS free_space_MB,
>       (SELECT free_percent FROM pgstattuple(schemaname || '.' || tablename))
> AS free_percent
> FROM
> (SELECT  cl.oid AS oid,
>         cl.relkind AS relkind,
>         relowner AS relowner,
>         n.nspname AS schemaname,
>         relname AS relname,
>         CASE
>              WHEN cl.relkind = 'r' THEN relname
>              WHEN cl.relkind = 'i' THEN (SELECT relname FROM pg_index pi,
> pg_class cx WHERE cl.oid=pi.indexrelid and pi.indrelid=cx.oid)
>              WHEN cl.relkind = 't' THEN relname
>              ELSE null
>         END AS tablename,
>         reltoastrelid as reltoastrelid,
>         reltoastidxid as reltoastidxid,
>         reltype AS reltype,
>         reltablespace AS reltablespace,
>         CASE
>              WHEN cl.relkind = 'i' THEN 0.0
>              ELSE pg_relation_size(cl.oid)
>         END AS tablesize,
>         pg_relation_size(cl.oid),
> --         pg_relation_size(cl.relname) AS tablesize,
>         CASE
>              WHEN cl.relkind = 'r' THEN CAST('TABLE' AS VARCHAR(20))
>              WHEN cl.relkind = 'i' THEN
>                CASE WHEN cl.oid in (SELECT pi.indexrelid FROM pg_index pi,
> pg_class pc, pg_class pt WHERE pi.indexrelid = pt.oid AND pi.indexrelid =
> pc.reltoastidxid ORDER BY pi.indexrelid)
>                      THEN CAST('INDEX OF TOAST TABLE' AS VARCHAR(20))
>                  ELSE CAST('INDEX' AS VARCHAR(20))
>                END
>              WHEN cl.relkind = 'S' THEN CAST('SEQUENCE' AS VARCHAR(20))
>              WHEN cl.relkind = 's' THEN CAST('SPECIAL' AS VARCHAR(20))
>              WHEN cl.relkind = 'v' THEN CAST('VIEW' AS VARCHAR(20))
>              WHEN cl.relkind = 't' THEN CAST('TOAST TABLE' AS VARCHAR(18))
>              ELSE null
>         END AS object_type,
>         CASE
>              WHEN cl.relkind = 'r' THEN
>                 COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
>                   FROM pg_index WHERE cl.oid=indrelid), 0)
>              ELSE pg_relation_size(cl.oid)
>         END AS indexsize,
>         CASE
>              WHEN reltoastrelid=0 THEN 0
>              ELSE pg_relation_size(reltoastrelid)
>         END AS toastsize,
>         CASE
>              WHEN reltoastrelid=0 THEN 0
>              ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
>                                     WHERE cl.reltoastrelid = ct.oid))
>         END AS toastindexsize
> FROM pg_class cl
> LEFT OUTER JOIN pg_namespace n ON n.oid = cl.relnamespace
> ) ss
> WHERE schemaname='public'
>      AND object_type='TABLE'
> ORDER BY
>  schemaname, tablename;
>
> -------------------------------------------------------------------------------------------
> -- Table & Index info
> -------------------------------------------------------------------------------------------
>
> SELECT schemaname,
>       tablename,
>       object_type,
>       relname,
>       pg_relpages(schemaname || '.' || tablename) AS rel_pages,
>       (SELECT ROUND(table_len/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || relname)) AS table_len_MB,
>       (SELECT tuple_count FROM pgstattuple(schemaname || '.' || relname)) AS
> tuple_count,
>       (SELECT ROUND(tuple_len/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || relname)) AS tuple_len_MB,
>       (SELECT tuple_percent FROM pgstattuple(schemaname || '.' || relname))
> AS tuple_percent,
>       (SELECT dead_tuple_count FROM pgstattuple(schemaname || '.' ||
> relname)) AS dead_tuple_count,
>       (SELECT ROUND(dead_tuple_len/1024.0/1024.0,3) FROM
> pgstattuple(schemaname || '.' || relname)) AS dead_tuple_len_MB,
>       (SELECT dead_tuple_percent FROM pgstattuple(schemaname || '.' ||
> relname)) AS dead_tuple_percent,
>       (SELECT ROUND(free_space/1024.0/1024.0,3) FROM pgstattuple(schemaname
> || '.' || relname)) AS free_space_MB,
>       (SELECT free_percent FROM pgstattuple(schemaname || '.' || relname))
> AS free_percent
> FROM
> (SELECT  cl.oid AS oid,
>         cl.relkind AS relkind,
>         relowner AS relowner,
>         n.nspname AS schemaname,
>         relname AS relname,
>         CASE
>              WHEN cl.relkind = 'r' THEN relname
>              WHEN cl.relkind = 'i' THEN (SELECT relname FROM pg_index pi,
> pg_class cx WHERE cl.oid=pi.indexrelid and pi.indrelid=cx.oid)
>              WHEN cl.relkind = 't' THEN relname
>              ELSE null
>         END AS tablename,
>         reltoastrelid as reltoastrelid,
>         reltoastidxid as reltoastidxid,
>         reltype AS reltype,
>         reltablespace AS reltablespace,
>         CASE
>              WHEN cl.relkind = 'i' THEN 0.0
>              ELSE pg_relation_size(cl.oid)
>         END AS tablesize,
>         pg_relation_size(cl.oid),
> --         pg_relation_size(cl.relname) AS tablesize,
>         CASE
>              WHEN cl.relkind = 'r' THEN CAST('TABLE' AS VARCHAR(20))
>              WHEN cl.relkind = 'i' THEN
>                CASE WHEN cl.oid in (SELECT pi.indexrelid FROM pg_index pi,
> pg_class pc, pg_class pt WHERE pi.indexrelid = pt.oid AND pi.indexrelid =
> pc.reltoastidxid ORDER BY pi.indexrelid)
>                      THEN CAST('INDEX OF TOAST TABLE' AS VARCHAR(20))
>                  ELSE CAST('INDEX' AS VARCHAR(20))
>                END
>              WHEN cl.relkind = 'S' THEN CAST('SEQUENCE' AS VARCHAR(20))
>              WHEN cl.relkind = 's' THEN CAST('SPECIAL' AS VARCHAR(20))
>              WHEN cl.relkind = 'v' THEN CAST('VIEW' AS VARCHAR(20))
>              WHEN cl.relkind = 't' THEN CAST('TOAST TABLE' AS VARCHAR(18))
>              ELSE null
>         END AS object_type,
>         CASE
>              WHEN cl.relkind = 'r' THEN
>                 COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
>                   FROM pg_index WHERE cl.oid=indrelid), 0)
>              ELSE pg_relation_size(cl.oid)
>         END AS indexsize,
>         CASE
>              WHEN reltoastrelid=0 THEN 0
>              ELSE pg_relation_size(reltoastrelid)
>         END AS toastsize,
>         CASE
>              WHEN reltoastrelid=0 THEN 0
>              ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
>                                     WHERE cl.reltoastrelid = ct.oid))
>         END AS toastindexsize
> FROM pg_class cl
> LEFT OUTER JOIN pg_namespace n ON n.oid = cl.relnamespace
> ) ss
> WHERE schemaname='public'
>      AND (object_type='INDEX' OR object_type='TABLE')
> ORDER BY
>  schemaname, tablename, object_type DESC, relname;
>
> -------------------------------------------------------------------------------------------
> -- Index
> -------------------------------------------------------------------------------------------
> SELECT schemaname,
>       tablename,
>       object_type,
>       relname,
>       pg_relpages(schemaname || '.' || tablename) AS rel_pages,
>       (SELECT version FROM pgstatindex(schemaname || '.' || relname)) AS
> version,
>       (SELECT tree_level FROM pgstatindex(schemaname || '.' || relname)) AS
> tree_level,
>       (SELECT index_size FROM pgstatindex(schemaname || '.' || relname)) AS
> index_size,
>       (SELECT root_block_no FROM pgstatindex(schemaname || '.' || relname))
> AS root_block_no,
>       (SELECT internal_pages FROM pgstatindex(schemaname || '.' || relname))
> AS internal_pages,
>       (SELECT leaf_pages FROM pgstatindex(schemaname || '.' || relname)) AS
> leaf_pages,
>       (SELECT empty_pages FROM pgstatindex(schemaname || '.' || relname)) AS
> empty_pages,
>       (SELECT deleted_pages FROM pgstatindex(schemaname || '.' || relname))
> AS deleted_pages,
>       (SELECT avg_leaf_density FROM pgstatindex(schemaname || '.' ||
> relname)) AS avg_leaf_density,
>       (SELECT leaf_fragmentation FROM pgstatindex(schemaname || '.' ||
> relname)) AS leaf_fragmentation
> FROM
> (SELECT  cl.oid AS oid,
>         cl.relkind AS relkind,
>         relowner AS relowner,
>         n.nspname AS schemaname,
>         relname AS relname,
>         CASE
>              WHEN cl.relkind = 'r' THEN relname
>              WHEN cl.relkind = 'i' THEN (SELECT relname FROM pg_index pi,
> pg_class cx WHERE cl.oid=pi.indexrelid and pi.indrelid=cx.oid)
>              WHEN cl.relkind = 't' THEN relname
>              ELSE null
>         END AS tablename,
>         reltoastrelid as reltoastrelid,
>         reltoastidxid as reltoastidxid,
>         reltype AS reltype,
>         reltablespace AS reltablespace,
>         CASE
>              WHEN cl.relkind = 'i' THEN 0.0
>              ELSE pg_relation_size(cl.oid)
>         END AS tablesize,
>         pg_relation_size(cl.oid),
> --         pg_relation_size(cl.relname) AS tablesize,
>         CASE
>              WHEN cl.relkind = 'r' THEN CAST('TABLE' AS VARCHAR(20))
>              WHEN cl.relkind = 'i' THEN
>                CASE WHEN cl.oid in (SELECT pi.indexrelid FROM pg_index pi,
> pg_class pc, pg_class pt WHERE pi.indexrelid = pt.oid AND pi.indexrelid =
> pc.reltoastidxid ORDER BY pi.indexrelid)
>                      THEN CAST('INDEX OF TOAST TABLE' AS VARCHAR(20))
>                  ELSE CAST('INDEX' AS VARCHAR(20))
>                END
>              WHEN cl.relkind = 'S' THEN CAST('SEQUENCE' AS VARCHAR(20))
>              WHEN cl.relkind = 's' THEN CAST('SPECIAL' AS VARCHAR(20))
>              WHEN cl.relkind = 'v' THEN CAST('VIEW' AS VARCHAR(20))
>              WHEN cl.relkind = 't' THEN CAST('TOAST TABLE' AS VARCHAR(18))
>              ELSE null
>         END AS object_type,
>         CASE
>              WHEN cl.relkind = 'r' THEN
>                 COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
>                   FROM pg_index WHERE cl.oid=indrelid), 0)
>              ELSE pg_relation_size(cl.oid)
>         END AS indexsize,
>         CASE
>              WHEN reltoastrelid=0 THEN 0
>              ELSE pg_relation_size(reltoastrelid)
>         END AS toastsize,
>         CASE
>              WHEN reltoastrelid=0 THEN 0
>              ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
>                                     WHERE cl.reltoastrelid = ct.oid))
>         END AS toastindexsize
> FROM pg_class cl
> LEFT OUTER JOIN pg_namespace n ON n.oid = cl.relnamespace
> ) ss
> WHERE schemaname='public'
>      AND object_type='INDEX'
> ORDER BY
>  schemaname, tablename, object_type DESC, relname;
>
> On Thu, 25 Dec 2008, Pavel Stehule wrote:
>
>> Hello
>>
>> look on contrib module pg_stat_tuple
>> http://www.postgresql.org/docs/8.3/interactive/pgstattuple.html
>>
>> regards
>> Pavel Stehule
>>
>> 2008/12/25 Gerhard Wiesinger <lists@wiesinger.com>:
>>>
>>> Hello!
>>>
>>> Is there some information in meta tables available about the number of
>>> pages
>>> currently unused, row versions of tables and indices which are unused?
>>>
>>> I'm asking because I want to measure how efficient HOT is working and
>>> whether vacuum should be run or not saving diskspace (I know this is done
>>> automatically).
>>>
>>> Thanx.
>>>
>>> Ciao,
>>> Gerhard
>>>
>>> --
>>> http://www.wiesinger.com/
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>