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