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