A l'honneur

Top 10

PostgreSQL : Taille des tables, index...

Détails
Clics : 7677

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 bases avec la taille des fichiers temporaires :
SELECT d.datname AS database_name,
       pg_size_pretty(pg_database_size(d.datname)) AS persistent_size,
       s.temp_files AS temp_files_count,
       pg_size_pretty(s.temp_bytes) AS temp_size,
       pg_size_pretty(pg_database_size(d.datname) + COALESCE(s.temp_bytes, 0)) AS total_approx
FROM pg_database d
LEFT JOIN pg_stat_database s ON d.oid = s.datid
WHERE d.datistemplate = false
ORDER BY (pg_database_size(d.datname) + COALESCE(s.temp_bytes, 0)) DESC;
  • 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;

Menu