| Contrôler l'utilisation de HOT, à partir de la 8.3

Par dim le 26/06/2008 - 14:20

Si comme moi vous avez prévu un fort besoin d'utiliser HOT sur vos tables (UPDATE fréquents sur des colonnes non indexées), vous aurez sûrement réglé un fillfactor largement inférieur à 100. Mais comment contrôler que HOT profite réellement de l'opportunité théorique ? La requête suivante permet de contrôler HOT:

SELECT schemaname, relname,
       n_tup_upd,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;

 schemaname | relname | n_tup_upd | n_tup_hot_upd | hot_ratio
------------+---------+-----------+---------------+-----------
 public     | table1  |         6 |             6 |    100.00
 public     | table2  |   2551200 |       2549474 |     99.93

Et si l'on souhaite ajouter à l'affichage le réglage actuel du fillfactor, c'est possible. Mais il faut se référer au catalogue système, où pg_class référence le schéma sous relnamespace qui est un OID de la table système pg_namespace, laquelle publie le nom du schéma sous la colonne nspname, retrouvée sous le nom schemaname dans les tables de stats...

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

 schemaname | relname |   reloptions    | n_tup_upd | n_tup_hot_upd | hot_ratio
------------+---------+-----------------+-----------+---------------+-----------
 public     | table1  | {fillfactor=50} |   1585920 |       1585246 |     99.96
 public     | table2  | {fillfactor=50} |   2504880 |       2503154 |     99.93

On dirait que les espoirs théoriques sont plutôt bien vérifiés en pratique... on peut donc continuer de suivre la documentation les yeux fermés^Wgrands ouverts ;) Merci PostgreSQL !