Différences
Ci-dessous, les différences entre deux révisions de la page.
Prochaine révision | Révision précédente | ||
support:trucs_et_astuces:trouver_facilement_la_taille_d_une_table_de_ses_index_et_son_nombre_de_tuples [2008/10/15 16:54] – créée ioguix | support:trucs_et_astuces:trouver_facilement_la_taille_d_une_table_de_ses_index_et_son_nombre_de_tuples [2011/03/01 15:50] (Version actuelle) – Ajout de fonctions pour avoir la taille des tables, en tenant compte de la taille des toast. zehome | ||
---|---|---|---|
Ligne 40: | Ligne 40: | ||
Jean-Paul ARGUDO\\ | Jean-Paul ARGUDO\\ | ||
http:// | http:// | ||
+ | |||
+ | Merci pour cette vue.\\ | ||
+ | Apres migration en postgres 8.4 j'ai du la modifier pour qu' | ||
+ | En esperant que cela soit utile. | ||
+ | <code sql>drop view vue_stats; | ||
+ | |||
+ | create view vue_stats as | ||
+ | SELECT | ||
+ | c.relname as nom, | ||
+ | c.reltuples:: | ||
+ | pg_total_relation_size(CAST(c.relname AS TEXT)) as volume_total, | ||
+ | pg_relation_size(CAST(c.relname AS TEXT)) as volume_donnees, | ||
+ | pg_total_relation_size(CAST(c.relname AS TEXT))-pg_relation_size(CAST(c.relname AS TEXT)) as volume_index | ||
+ | FROM pg_catalog.pg_class c | ||
+ | JOIN pg_catalog.pg_roles r ON r.oid = c.relowner | ||
+ | LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | ||
+ | WHERE c.relkind = ' | ||
+ | AND n.nspname NOT IN (' | ||
+ | AND pg_catalog.pg_table_is_visible(c.oid) | ||
+ | ORDER BY 1, | ||
+ | -- \\ | ||
+ | Jean-Philippe FARCY\\ | ||
+ | http:// | ||
+ | |||
+ | |||
+ | Les fonctions ci dessus ne tiennent pas compte des tables toast dans leur calculs. | ||
+ | Voici des fonctions qui en tiennent compte: | ||
+ | <code sql> | ||
+ | CREATE OR REPLACE VIEW view_relations_size AS | ||
+ | SELECT | ||
+ | c.relname AS name, | ||
+ | c.reltuples:: | ||
+ | pg_relation_size(c.oid) AS table_size, | ||
+ | pg_total_relation_size(c.oid)-pg_relation_size(c.oid) - (CASE WHEN c.reltoastrelid <> 0 THEN pg_relation_size(c.reltoastrelid) ELSE 0 END) AS index_size, | ||
+ | CASE WHEN c.reltoastrelid <> 0 THEN pg_relation_size(c.reltoastrelid) ELSE 0 END AS toast_size, | ||
+ | pg_total_relation_size(c.oid) AS total_size | ||
+ | FROM | ||
+ | pg_catalog.pg_class c | ||
+ | JOIN | ||
+ | pg_catalog.pg_roles r ON r.oid = c.relowner | ||
+ | LEFT JOIN | ||
+ | pg_catalog.pg_namespace n ON n.oid = c.relnamespace | ||
+ | WHERE | ||
+ | c.relkind = ' | ||
+ | AND n.nspname NOT IN (' | ||
+ | AND pg_catalog.pg_table_is_visible(c.oid) | ||
+ | ORDER BY total_size DESC; | ||
+ | |||
+ | CREATE VIEW view_relations_size_pretty AS | ||
+ | SELECT | ||
+ | name, | ||
+ | tuples, | ||
+ | pg_size_pretty(table_size) AS table_size, | ||
+ | pg_size_pretty(index_size) AS index_size, | ||
+ | pg_size_pretty(toast_size) AS toast_size, | ||
+ | pg_size_pretty(total_size) AS total_size | ||
+ | FROM view_relations_size; | ||
+ | </ | ||
+ | -- \\ | ||
+ | Laurent Coustet | ||
+ |