¿Cómo encuentras el recuento de filas para todas tus mesas en Postgres?

Estoy buscando una manera de encontrar el conteo de filas para todas mis mesas en Postgres. Sé que puedo hacer esta tabla por vez con:

SELECT count(*) FROM table_name; 

pero me gustaría ver el recuento de filas para todas las tablas y luego ordenarlo para tener una idea de cuán grandes son todas mis tablas.

    Hay tres formas de obtener este tipo de conteo, cada uno con sus propias compensaciones.

    Si desea un recuento real, debe ejecutar la instrucción SELECT como la que utilizó contra cada tabla. Esto se debe a que PostgreSQL mantiene la información de visibilidad de fila en la fila misma, no en ningún otro lado, por lo que cualquier recuento exacto solo puede ser relativo a alguna transacción. Obtendrá un recuento de lo que ve esa transacción en el momento en que se ejecuta. Puede automatizar esto para que se ejecute en todas las tablas de la base de datos, pero probablemente no necesite ese nivel de precisión o desee esperar tanto tiempo.

    El segundo enfoque señala que el recostackdor de estadísticas rastrea aproximadamente cuántas filas están “activas” (no eliminadas o obsoletas por actualizaciones posteriores) en cualquier momento. Este valor puede desaparecer un poco bajo una actividad intensa, pero generalmente es una buena estimación:

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

    Eso también puede mostrarle cuántas filas están muertas, lo cual es en sí mismo un número interesante para monitorear.

    La tercera forma es observar que el comando ANALIZAR del sistema, que se ejecuta por el proceso de autovacuum regularmente a partir de PostgreSQL 8.3 para actualizar las estadísticas de la tabla, también calcula una estimación de fila. Puedes agarrar uno como este:

     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; 

    Cuál de estas consultas es mejor usar es difícil de decir. Normalmente tomo esa decisión en función de si hay más información útil que también quiero usar dentro de pg_class o dentro de pg_stat_user_tables. Para propósitos básicos de conteo solo para ver qué tan grandes son las cosas en general, cualquiera de las dos debería ser lo suficientemente precisa.

    Para obtener estimaciones, vea la respuesta de Greg Smith .

    Para obtener recuentos exactos, las otras respuestas hasta ahora están plagadas de algunos problemas, algunos de ellos serios (ver más abajo). Aquí hay una versión que con suerte es mejor:

     CREATE FUNCTION rowcount_all(schema_name text default 'public') RETURNS table(table_name text, cnt bigint) as $$ declare table_name text; begin for table_name in SELECT c.relname FROM pg_class c JOIN pg_namespace s ON (c.relnamespace=s.oid) WHERE c.relkind = 'r' AND s.nspname=schema_name LOOP RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I', table_name, schema_name, table_name); END LOOP; end $$ language plpgsql; 

    Toma un nombre de esquema como parámetro, o public si no se proporciona ningún parámetro.

    Para trabajar con una lista específica de esquemas o una lista proveniente de una consulta sin modificar la función, se puede invocar desde una consulta como esta:

     WITH rc(schema_name,tbl) AS ( select sn,rowcount_all(sn) from (values ('schema1'),('schema2')) as s(n) ) SELECT schema_name,(tbl).* FROM rc; 

    Esto produce una salida de 3 columnas con el esquema, la tabla y el recuento de filas.

    Ahora aquí hay algunos problemas en las otras respuestas que esta función evita:

    • Los nombres de tablas y esquemas no se deben insertar en SQL ejecutable sin ser citados, ya sea con quote_ident o con la función de format() más moderna format() con su cadena %I format. De lo contrario, una persona malintencionada puede nombrar su tabla tablename;DROP TABLE other_table que es perfectamente válida como nombre de tabla.

    • Incluso sin la inyección de SQL y los problemas de caracteres divertidos, el nombre de la tabla puede existir en variantes que difieren según el caso. Si una tabla se llama ABCD y otra abcd , el SELECT count(*) FROM... debe usar un nombre entre comillas, de lo contrario saltará ABCD y contará abcd dos veces. El %I de formato lo hace automáticamente.

    • information_schema.tables enumera los tipos compuestos personalizados además de las tablas, incluso cuando table_type es 'BASE TABLE' (!). Como consecuencia, no podemos iterar en information_schema.tables , de lo contrario nos arriesgamos a select count(*) from name_of_composite_type y eso fallaría. OTOH pg_class where relkind='r' siempre debería funcionar bien.

    • El tipo de COUNT () es bigint , no int . Las tablas con más de 2,15 mil millones de filas pueden existir (sin embargo, ejecutar una cuenta (*) en ellas es una mala idea).

    • No es necesario crear un tipo permanente para que una función devuelva un conjunto de resultados con varias columnas. RETURNS TABLE(definition...) es una mejor alternativa.

    Aquí hay una solución que no requiere funciones para obtener un recuento preciso de cada tabla:

     select table_schema, table_name, (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count from ( select table_name, table_schema, query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count from information_schema.tables where table_schema = 'public' --<< change here for the schema you want ) t 

    query_to_xml ejecutará la consulta SQL pasada y devolverá un XML con el resultado (el recuento de filas para esa tabla). El xpath() externo xpath() extraerá la información de recuento de ese xml y lo convertirá en un número

    La tabla derivada no es realmente necesaria, pero hace que el xpath() un poco más fácil de entender, de lo contrario, la query_to_xml() debería pasarse a la función xpath() .

    Si no le molestan los datos obsoletos, puede acceder a las mismas estadísticas utilizadas por el optimizador de consultas .

    Algo como:

     SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables; 

    No estoy seguro si una respuesta en bash es aceptable para usted, pero FWIW …

     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 

    La respuesta hacky, práctica para las personas que intentan evaluar qué plan Heroku necesitan y no pueden esperar a que el contador de filas lento de heroku se actualice:

    Básicamente, si desea ejecutar \dt en psql , copie los resultados en su editor de texto favorito (se verá así:

      public | auth_group | table | axrsosvelhutvw public | auth_group_permissions | table | axrsosvelhutvw public | auth_permission | table | axrsosvelhutvw public | auth_user | table | axrsosvelhutvw public | auth_user_groups | table | axrsosvelhutvw public | auth_user_user_permissions | table | axrsosvelhutvw public | background_task | table | axrsosvelhutvw public | django_admin_log | table | axrsosvelhutvw public | django_content_type | table | axrsosvelhutvw public | django_migrations | table | axrsosvelhutvw public | django_session | table | axrsosvelhutvw public | exercises_assignment | table | axrsosvelhutvw 

    ), luego ejecuta una búsqueda de expresiones regulares y reemplaza así:

     ^[^|]*\|\s+([^|]*?)\s+\| table \|.*$ 

    a:

     select '\1', count(*) from \1 union/g 

    que le dará algo muy similar a esto:

     select 'auth_group', count(*) from auth_group union select 'auth_group_permissions', count(*) from auth_group_permissions union select 'auth_permission', count(*) from auth_permission union select 'auth_user', count(*) from auth_user union select 'auth_user_groups', count(*) from auth_user_groups union select 'auth_user_user_permissions', count(*) from auth_user_user_permissions union select 'background_task', count(*) from background_task union select 'django_admin_log', count(*) from django_admin_log union select 'django_content_type', count(*) from django_content_type union select 'django_migrations', count(*) from django_migrations union select 'django_session', count(*) from django_session ; 

    (Deberá eliminar la union y agregar el punto y coma al final manualmente)

    Ejecútelo en psql y listo.

      ?column? | count --------------------------------+------- auth_group_permissions | 0 auth_user_user_permissions | 0 django_session | 1306 django_content_type | 17 auth_user_groups | 162 django_admin_log | 9106 django_migrations | 19 [..] 

    Por lo general, no confío en las estadísticas, especialmente en 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; 

    Hice una pequeña variación para incluir todas las tablas, también para las tablas no públicas.

     CREATE TYPE table_count AS (table_schema TEXT,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 table_schema,table_name FROM information_schema.tables where table_schema !=''pg_catalog'' and table_schema !=''information_schema'' ORDER BY 1,2 LOOP FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.table_schema||''.''||t_name.table_name LOOP END LOOP; r.table_schema := t_name.table_schema; r.table_name := t_name.table_name; r.num_rows := the_count.count; RETURN NEXT r; END LOOP; RETURN; END; ' LANGUAGE plpgsql; 

    use select count_em_all(); para llamarlo

    Espero que encuentres esto útil. Pablo

    No recuerdo la URL desde donde recogí esto. Pero espero que esto te ayude:

     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; 

    Ejecutando select count_em_all(); debería hacer que cuentes todas tus mesas.

    Me gusta la respuesta de Daniel Vérité. Pero cuando no puedes usar una statement CREATE, puedes usar una solución bash o, si eres un usuario de Windows, una poderosa:

     # You don't need this if you have pgpass.conf $env:PGPASSWORD = "userpass" # Get table list $tables = & 'C:\Program Files\PostgreSQL\9.4\bin\psql.exe' -U user -w -d dbname -At -c "select table_name from information_schema.tables where table_type='BASE TABLE' AND table_schema='schema1'" foreach ($table in $tables) { & 'C:\path_to_postresql\bin\psql.exe' -U root -w -d dbname -At -c "select '$table', count(*) from $table" } 

    Simple dos pasos:
    (Nota: no es necesario cambiar nada solo copie y pegue)
    1. crear función

     create function cnt_rows(schema text, tablename text) returns integer as $body$ declare result integer; query varchar; begin query := 'SELECT count(1) FROM ' || schema || '.' || tablename; execute query into result; return result; end; $body$ language plpgsql; 

    2. Ejecute esta consulta para que las filas cuenten para todas las tablas

     select sum(cnt_rows) as total_no_of_rows from (select cnt_rows(table_schema, table_name) from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema') and table_type='BASE TABLE') as subq; 

    o
    Para que las filas cuenten en la tabla

     select table_schema, table_name, cnt_rows(table_schema, table_name) from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema') and table_type='BASE TABLE' order by 3 desc