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

Resuelto mmrobins asked hace 14 años • 21 respuestas

Estoy buscando una manera de encontrar el recuento de filas de todas mis tablas en Postgres. Sé que puedo hacer esto una tabla a la vez con:

SELECT count(*) FROM table_name;

pero me gustaría ver el recuento de filas de todas las tablas y luego ordenarlas para tener una idea del tamaño de todas mis tablas.

mmrobins avatar Apr 08 '10 06:04 mmrobins
Aceptado

Hay tres formas de realizar este tipo de recuento, cada una con sus propias compensaciones.

Si desea un recuento verdadero, debe ejecutar la instrucción SELECT como la que utilizó en cada tabla. Esto se debe a que PostgreSQL mantiene la información de visibilidad de las filas en la fila misma, no en ningún otro lugar, por lo que cualquier recuento preciso 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 ejecutarlo en todas las tablas de la base de datos, pero probablemente no necesite ese nivel de precisión ni quiera esperar tanto.

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

El segundo enfoque señala que el recopilador de estadísticas rastrea aproximadamente cuántas filas están "activas" (no eliminadas ni obsoletas por actualizaciones posteriores) en cualquier momento. Este valor puede variar un poco en condiciones de actividad intensa, pero en general 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 que en sí mismo es un número interesante de monitorear.

La tercera forma es tener en cuenta que el comando ANALYZE del sistema, que el proceso de autovacuum ejecuta regularmente a partir de PostgreSQL 8.3 para actualizar las estadísticas de la tabla, también calcula una estimación de fila. Puedes agarrarlo así:

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;

Es difícil decir cuál de estas consultas es mejor utilizar. 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 los dos debería ser lo suficientemente preciso.

Greg Smith avatar Apr 10 '2010 00:04 Greg Smith

Aquí hay una solución que no requiere funciones para obtener un recuento preciso para 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_xmlejecutará la consulta SQL pasada y devolverá un XML con el resultado (el recuento de filas para esa tabla). Luego, el externo xpath()extraerá la información del recuento de ese xml y la convertirá en un número.

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

avatar Jul 31 '2016 12:07

Para obtener estimaciones, consulte la respuesta de Greg Smith .

Para obtener recuentos exactos, las otras respuestas hasta ahora están plagadas de algunos problemas, algunos de ellos graves (ver más abajo). Aquí hay una versión que, con suerte, será 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 publicsi 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 llamar desde una consulta como esta:

WITH rc(schema_name,tbl) AS (
  select s.n,rowcount_all(s.n) 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 deben insertarse en SQL ejecutable sin estar entre comillas, ya sea con la función quote_identmás moderna o con su cadena de formato. De lo contrario, alguna persona malintencionada puede nombrar su tabla , lo cual es perfectamente válido como nombre de tabla.format()%Itablename;DROP TABLE other_table

  • Incluso sin la inyección 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 tiene un nombre ABCDy otra abcd, SELECT count(*) FROM...debe usar un nombre entre comillas, de lo contrario se saltará ABCDy contará abcddos veces. El %Iformato of hace esto automáticamente.

  • Con versiones anteriores de Postgres, information_schema.tablesenumera tipos compuestos personalizados además de tablas, incluso cuando table_type es 'BASE TABLE'. Como consecuencia, no podemos iterar information_schema.tables, de lo contrario corremos el riesgo de tenerlo select count(*) from name_of_composite_typey eso fallaría. OTOH pg_class where relkind='r'siempre debería funcionar bien.

  • El tipo de COUNT() es bigint, no int. Es posible que existan tablas con más de 2,150 millones de filas (aunque realizar un recuento (*) 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.

Daniel Vérité avatar Feb 25 '2015 01:02 Daniel Vérité

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

Básicamente, desea ejecutar \dt, psqlcopiar los resultados a 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 ejecute una búsqueda de expresiones regulares y reemplace así:

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

a:

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

lo que te 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ás eliminar el último uniony agregar el punto y coma al final manualmente)

Ejecútelo psqly 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
[..]
Aur Saraf avatar Jul 31 '2016 11:07 Aur Saraf