From PostgreSQL wiki

Revision as of 14:36, 27 October 2009 by Intgr (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Performance Snippets

Table Storage Parameters

Works with PostgreSQL


Written in


Depends on


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

Personal tools