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

2008/12/26 Gerhard Wiesinger <lists(at)wiesinger(dot)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(at)wiesinger(dot)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(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>