mardi 6 mai 2008
Obtenir le liste détaillée de l'occupation disque pour chaque table (avec index et toast)
Par david techer, mardi 6 mai 2008 à 15:08 :: PostGIS et PostgreSQL
Pas de race, on fait tout simplement
SELECT stat.relname AS "Table",pg_size_pretty(pg_total_relation_size(stat.relid)) AS "Occupation totale", pg_size_pretty(pg_relation_size(stat.relid)) AS "Taille de la table", CASE WHEN cl.reltoastrelid = 0 THEN 'aucun' ELSE pg_size_pretty(pg_relation_size(cl.reltoastrelid)+ COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)) END AS "Taille de la table TOAST", pg_size_pretty(COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=stat.relid)::int8, 0)) AS "Taille des index" FROM pg_stat_all_tables stat JOIN pg_statio_all_tables statio ON stat.relid = statio.relid JOIN pg_class cl ON cl.oid=stat.relid AND stat.schemaname='public' order by stat.relname;
Ce qui donnera par exemple
Table | Occupation totale | Taille de la table | Taille de la table TOAST | Taille des index ----------------------+-------------------+--------------------+--------------------------+------------------ table | 72 kB | 40 kB | 8192 bytes | 24 kB terrain_golf | 152 kB | 104 kB | 8192 bytes | 40 kB theatre | 272 kB | 168 kB | 8192 bytes | 96 kB ua_arrondissement | 152 kB | 48 kB | 80 kB | 24 kB ua_chef-lieu_departe | 40 kB | 8192 bytes | 8192 bytes | 24 kB ua_chef-lieu_region | 40 kB | 8192 bytes | 8192 bytes | 24 kB ua_commune | 74 MB | 43 MB | 28 MB | 2320 kB ua_departement | 4608 kB | 16 kB | 4568 kB | 24 kBPetit rappel: (surtout pour moi

BLCKSZ
soit
psql -At -U postgres -d postgres -c "show block_size" --> 8192, les 8192 bytes correspondent à  une page de 8Ko.