PostgreSQL :- Average Row Length for a table

Background

Wanted to put together a short SQL for calculating Average Row Length for each table.

Code

Outline

To get the Average Row Length, we do the following :-

  1. Get Table Size
    • pg_relation_size against table object pg_class.oid
    • or pg_class.relpages * pg_catalog.pg_settings.value ( name = ‘page_size’)
  2. Get Number of records
    • pg_class.rel_tuples

 

SQL


with cteSetting
(
      name
    , setting
)

as
(
    select
              name
            , setting::integer

    from   pg_catalog.pg_settings

    where  name = 'block_size'
)
select 

         tblNS.nspname as schema

        --, tblC.oid::regclass::text as objectName
        , tblC.relname as table

        , tblC.reltuples
        	as "#ofRecords"

        , tblC.relpages
        	as "#ofPages"

        --, cteS.setting as blockSize

         , pg_size_pretty
            (
                pg_relation_size
                (
                    tblC.oid
                ) ::decimal
            ) as "sizeBORelationSize"

        /*
        , pg_size_pretty
            (
                (tblC.relpages * cteS.setting)
                ::decimal
            )
            as "sizeBORelationPages"
          */

        , (
                pg_relation_size
                (
                    tblC.oid
                )
                /
                nullif
                (
                      tblC.reltuples
                    , 0
                )
          ) :: decimal(10, 2)
            as "avgLenBasedOnRelationSize"

        , (
                (
                    tblC.relpages
                        * cteS.setting
                )       

                /

                nullif
                (
                      tblC.reltuples
                    , 0
                )

          ) :: decimal(10, 2)
            as "avgLenBasedOnRelPages"

from pg_catalog.pg_class tblC

join pg_catalog.pg_namespace tblNS

    on tblC.relnamespace = tblNS.oid

inner join cteSetting cteS
   on true

where tblC.relkind = 'r'

and   tblNS.nspname not in
        (
              'information_schema'
            , 'pg_catalog'
        )

order by

    tblC.relpages desc

Output

Output #1

pg_class.rowLength.20190816.0645PM

Output #2

pg_class.rowLength.02.20190816.0653PM.PNG

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s