TableStorageParameters

From PostgreSQL wiki
Jump to navigationJump to search

Performance Snippets

Table Storage Parameters

Works with PostgreSQL

8.3

Written in

SQL

Depends on

Nothing

SELECT t.schemaname, t.relname, c.reloptions, 
       t.n_tup_upd, t.n_tup_hot_upd, 
       case when n_tup_upd > 0 
            then ((n_tup_hot_upd::numeric/n_tup_upd::numeric)*100.0)::numeric(5,2)
            else NULL
        end AS hot_ratio
FROM pg_stat_all_tables t 
      JOIN (pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid) 
        ON n.nspname = t.schemaname AND c.relname = t.relname

Thanks to Dimitri (dim) Fontaine for this handy code. Snippets