Information about Pages, row versions of tables, indices

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 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)?

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

Thnx.

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:

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