Requêtes pour connaitre les tailles :
- Taille des tables et index (global à une table) avec ratio :
SELECT
e1.relname AS table,
pg_size_pretty(pg_relation_size(e1.oid)) AS "table size",
CASE
WHEN e3.oid IS NULL THEN 'O bytes'
WHEN e3.oid IS NOT NULL THEN pg_size_pretty(pg_total_relation_size(e3.oid))
END AS "TOAST size",
pg_size_pretty(sum(pg_relation_size(e2.oid))) AS "Index size",
pg_size_pretty(pg_total_relation_size(e1.oid)) AS "total table size",
round(sum(pg_relation_size(e2.oid))*100/pg_total_relation_size(e1.oid)) AS "ratio"
FROM pg_class e1
JOIN pg_index i ON e1.oid=i.indrelid
JOIN pg_class e2 ON i.indexrelid=e2.oid
LEFT JOIN pg_class e3 ON e1.reltoastrelid=e3.oid
WHERE
(e1.relkind='r' OR e1.relkind='t')
AND pg_relation_size(e1.oid) != 0
GROUP BY e1.relname, e1.oid, e3.oid
ORDER BY sum(pg_relation_size(e2.oid)) DESC;- Taille des tables avec taille des index (index par index) :
SELECT
e1.relname AS table,
e2.relname AS "index name",
pg_size_pretty(pg_relation_size(e1.oid)) AS "table size",
CASE
WHEN e3.oid IS NULL THEN '0 bytes'
WHEN e3.oid IS NOT NULL THEN pg_size_pretty(pg_relation_size(e3.oid))
END AS "TOAST size",
CASE
WHEN e2.oid IS NULL THEN '0 bytes'
WHEN e2.oid IS NOT NULL THEN pg_size_pretty(pg_relation_size(e2.oid))
END AS "Index size",
pg_size_pretty(pg_total_relation_size(e1.oid)) AS "total table size"
FROM pg_class e1
JOIN pg_index i ON e1.oid=i.indrelid
JOIN pg_class e2 ON i.indexrelid=e2.oid
LEFT JOIN pg_class e3 ON e1.reltoastrelid=e3.oid
WHERE
(e1.relkind='r' OR e1.relkind='t')
AND pg_relation_size(e1.oid) != 0
ORDER BY pg_total_relation_size(e1.oid) DESC;- Les 5 plus grosses tables :
SELECT nspname || '.' || relname AS "relation",pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 5;
- Les 50 plus grosses tables triée de la plus grosse à la moins grosse :
select
schema,name, pg_size_pretty(CASE WHEN is_index THEN 0 ELSE s END) AS size,
pg_size_pretty(CASE WHEN is_index THEN s ELSE st - s END) AS index, CASE WHEN st = 0 THEN 0
WHEN is_index THEN 100 ELSE 100 - ((s*100) / st) END || '%' as ratio, pg_size_pretty(st) as total
FROM (SELECT *,st = s AS is_index FROM (SELECT nspname as schema, relname as name, pg_relation_size(nspname || '.' || relname) as s, pg_total_relation_size(nspname || '.' || relname) as st
FROM pg_class JOIN pg_namespace ON (relnamespace = pg_namespace.oid)) AS p) AS pp
ORDER BY st DESC LIMIT 50;- Taille des lignes mortes pour chaque table (postgres superieur à 9) :
CREATE EXTENSION pgstattuple;
SELECT relname as table_name, pg_size_pretty(dead_tuple_len) AS dead_tuple_size
FROM
(SELECT relname, (pgstattuple(oid)).dead_tuple_len AS dead_tuple_len FROM pg_class WHERE relkind='r')
AS stattuple
ORDER BY dead_tuple_len DESC;- Taille des lignes mortes pour la base (postgres superieur à 9) :
SELECT
pg_size_pretty(sum((pgstattuple(oid)).dead_tuple_len)) AS total_dead_tuple_len
FROM pg_class WHERE relkind='r';
- Taille des tables de TOAST :
SELECT a, n, pg_relation_size(t), pg_size_pretty(pg_relation_size(t))
FROM (SELECT c.relname, c.reltoastrelid, d.relname
FROM pg_class c JOIN pg_class d ON c.reltoastrelid = d.oid
) AS x(a, t, n)
WHERE t > 0 AND pg_relation_size(t) > 0
ORDER BY 3 DESC;
- Utilisation d'une vue :
La vue :
CREATE OR REPLACE VIEW public.espace AS
SELECT c1.relname AS nom_table,
c2.relname AS nom_index,
c2.relpages * 8 / 1024 AS taille_mbytes,
c2.relfilenode AS nom_fichier,
0::bigint AS nombre_ligne
FROM pg_class c1,
pg_class c2,
pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid
UNION
SELECT pg_class.relname AS nom_table,
NULL::name AS nom_index,
pg_class.relpages * 8 / 1024 AS taille_mbytes,
pg_class.relfilenode AS nom_fichier,
pg_class.reltuples::bigint AS nombre_ligne
FROM pg_class
WHERE pg_class.relkind = 'r'::"char"
ORDER BY 3 DESC, 1, 2 DESC;exemple d'utilisation :
select nom_table, sum(taille_mbytes) as "taille_totale_index_et_table_en_MB",
(select sum(taille_mbytes) from espace as a2 where nom_index is null and a1.nom_table = a2.nom_table) as "taille_table_en_MB",
(select sum(taille_mbytes) from espace as a2 where nom_index is not null and a1.nom_table = a2.nom_table) as "taille_index_en_MB",
(select nombre_ligne from espace as a2 where nom_index is null and a1.nom_table = a2.nom_table) as nombre_de_ligne from espace as a1 group by nom_table order by 2 desc;