Comment trouver le nombre de lignes pour toutes vos tables dans Postgres ?

Question

Je cherche un moyen de trouver le nombre de lignes pour toutes mes tables dans Postgres. Je sais que je peux le faire une table à la fois avec :

SELECT count(*) FROM table_name;

mais j'aimerais voir le nombre de lignes de toutes les tables, puis les ordonner pour avoir une idée de la taille de mes tables.

Answer 1

Il y a trois façons d'obtenir ce genre de compte, chacune ayant ses propres inconvénients.

Si vous voulez un compte réel, vous devez exécuter l'instruction SELECT comme celle que vous avez utilisée pour chaque table. Ceci est dû au fait que PostgreSQL conserve les informations de visibilité de la ligne dans la ligne elle-même, pas ailleurs, donc tout comptage précis ne peut être que relatif à une transaction. Vous obtenez un compte de ce que cette transaction voit au moment où elle s'exécute. Vous pourriez automatiser cette opération pour l'exécuter sur chaque table de la base de données, mais vous n'avez probablement pas besoin de ce niveau de précision ou ne voulez pas attendre aussi longtemps.

La deuxième approche consiste à noter que le collecteur de statistiques suit à peu près le nombre de lignes qui sont "vivantes" (non supprimées ou rendues obsolètes par des mises à jour ultérieures) à tout moment. Cette valeur peut être légèrement erronée en cas d'activité intense, mais elle constitue généralement une bonne estimation :

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
  ORDER BY n_live_tup DESC;

Cela peut également vous montrer combien de lignes sont mortes, ce qui est en soi un chiffre intéressant à surveiller.

La troisième façon est de noter que la commande système ANALYZE, qui est exécutée par le processus autovacuum régulièrement à partir de PostgreSQL 8.3 pour mettre à jour les statistiques de la table, calcule également une estimation de rangée. Vous pouvez l'attraper comme ceci :

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

Il est difficile de dire laquelle de ces requêtes est la meilleure à utiliser. Normalement, je prends cette décision en fonction des informations plus utiles que je souhaite utiliser dans pg_class ou dans pg_stat_user_tables. Pour des comptages de base, juste pour voir la taille des choses en général, l'un ou l'autre devrait être assez précis.

Answer 2

Si les données potentiellement périmées ne vous dérangent pas, vous pouvez accéder aux mêmes statistiques que celles utilisées par l'optimiseur de requêtes .

Quelque chose comme :

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;

Answer 3

Je ne sais pas si une réponse dans bash est acceptable pour vous, mais pour info...

PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
            SELECT   table_name
            FROM     information_schema.tables
            WHERE    table_type='BASE TABLE'
            AND      table_schema='public'
            \""
TABLENAMES=$(export PGPASSWORD=test; eval "$PGCOMMAND")

for TABLENAME in $TABLENAMES; do
    PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
                SELECT   '$TABLENAME',
                         count(*) 
                FROM     $TABLENAME
                \""
    eval "$PGCOMMAND"
done

Answer 4

En général, je ne me fie pas aux statistiques, surtout dans PostgreSQL.

SELECT table_name, dsql2('select count(*) from '||table_name) as rownum
FROM information_schema.tables
WHERE table_type='BASE TABLE'
    AND table_schema='livescreen'
ORDER BY 2 DESC;

CREATE OR REPLACE FUNCTION dsql2(i_text text)
  RETURNS int AS
$BODY$
Declare
  v_val int;
BEGIN
  execute i_text into v_val;
  return v_val;
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Answer 5

Je ne me souviens pas de l'URL d'où j'ai récupéré cela. Mais j'espère que cela pourra vous aider :

CREATE TYPE table_count AS (table_name TEXT, num_rows INTEGER); 

CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD; 
    t_name RECORD; 
    r table_count%ROWTYPE; 

BEGIN
    FOR t_name IN 
        SELECT 
            c.relname
        FROM
            pg_catalog.pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE 
            c.relkind = ''r''
            AND n.nspname = ''public'' 
        ORDER BY 1 
        LOOP
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.relname 
            LOOP 
            END LOOP; 

            r.table_name := t_name.relname; 
            r.num_rows := the_count.count; 
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql; 

Exécuter select count_em_all(); devrait vous donner le nombre de lignes de toutes vos tables.