Skip to content

Requetes systèmes

DBPostgreSQL

Quelques requêtes utiles :

 Taille des tables et index avec proportion:


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 non compris


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;

 

Tables avec des lignes mortes dans la base

 

select relname,n_dead_tup,n_live_tup from pg_stat_all_tables order by n_dead_tup 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;

Rétroliens

Pas de rétroliens

Commentaires

Afficher les commentaires en Vue non groupée | Vue groupée

Pas de commentaires

Ajouter un commentaire

Marquer un texte en gras: *mot*, souligner un texte: _mot_.
Les smilies standard comme :-) et ;-) sont convertis en images.
Les adresses Email ne sont pas affichées, et sont seulement utilisées pour la communication.
Syntaxe BBCode autorisée
Form options