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 | ||
| + | |||