Différences
Ci-dessous, les différences entre deux révisions de la page.
| Les deux révisions précédentesRévision précédente | |||
| support:trucs_et_astuces:trouver_facilement_la_taille_d_une_table_de_ses_index_et_son_nombre_de_tuples [2010/04/28 10:50] – jp | 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 63: | Ligne 63: | ||
| Jean-Philippe FARCY\\ | Jean-Philippe FARCY\\ | ||
| http:// | 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 | ||
| + | |||