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

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:

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