registros - Una forma rápida de descubrir el recuento de filas de una tabla en PostgreSQL

manual de postgresql 10 en español pdf

Question

Necesito saber el número de filas en una tabla para calcular un porcentaje. Si el conteo total es mayor que una constante predefinida, usaré el valor constante. De lo contrario, usaré el número real de filas.

Puedo usar el SELECT count(*) FROM table . Pero si mi valor constante es 500,000 y tengo 5,000,000,000 de filas en mi tabla, contar todas las filas perderá mucho tiempo.

¿Es posible dejar de contar tan pronto como mi valor constante sea superado?

Necesito el número exacto de filas solo mientras esté por debajo del límite establecido. De lo contrario, si el recuento está por encima del límite, utilizo el valor límite en su lugar y quiero la respuesta lo más rápido posible.

Algo como esto:

SELECT text,count(*), percentual_calculus()  
FROM token  
GROUP BY text  
ORDER BY count DESC;

Answer 1

¿Qué tan ancha es la columna de texto?

Con un GROUP BY no hay mucho que pueda hacer para evitar un escaneo de datos (al menos un escaneo de índice).

Yo lo recomiendo:

1. Si es posible, cambie el esquema para eliminar la duplicación de datos de texto. De esta forma, el recuento se realizará en un campo de clave externa angosto en la tabla ''muchos''.

2. Alternativamente, creando una columna generada con un HASH del texto, luego GROUP BY la columna hash. Una vez más, esto es para disminuir la carga de trabajo (escanear a través de un índice de columna estrecha)

Editar:

Tu pregunta original no coincidía exactamente con tu edición. No estoy seguro si sabe que el COUNT, cuando se utiliza con un GROUP BY, devolverá el recuento de elementos por grupo y no el recuento de elementos en toda la tabla.

Answer 2

En Oracle, puede usar rownum para limitar el número de filas devueltas. Supongo que existe una construcción similar en otros SQL también. Entonces, para el ejemplo que dio, podría limitar el número de filas devueltas a 500001 y aplicar un count(*) luego:

SELECT (case when cnt > 500000 then 500000 else cnt end) myCnt
FROM (SELECT count(*) cnt FROM table WHERE rownum<=500001)

Answer 3

Lo hice una vez en una aplicación postgres ejecutando:

EXPLAIN SELECT * FROM foo;

A continuación, examina la salida con una expresión regular o lógica similar. Para un SELECT * simple, la primera línea de salida debería verse de la siguiente manera:

Seq Scan on uids  (cost=0.00..1.21 rows=8 width=75)

Puede usar el valor rows=(/d+) como una estimación aproximada de la cantidad de filas que se devolverán, y luego solo haga el recuento SELECT COUNT(*) real si la estimación es, digamos, menor que 1.5x su umbral (o cualquier número que consideres que tenga sentido para tu aplicación).

Dependiendo de la complejidad de su consulta, este número puede ser cada vez menos preciso. De hecho, en mi aplicación, cuando agregamos combinaciones y condiciones complejas, se volvió tan impreciso que no valía nada, ni siquiera para saber cómo, con una potencia de 100, cuántas filas hubiéramos devuelto, así que tuvimos que abandonar esa estrategia.

Pero si su consulta es lo suficientemente simple como para que Pg pueda predecir, dentro de un margen de error razonable, cuántas filas devolverá, puede funcionar para usted.

Answer 4

Para SQL Server (2005 o superior), un método rápido y confiable es:

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID(''MyTableName'')   
AND (index_id=0 or index_id=1);

Los detalles sobre sys.dm_db_partition_stats se explican en MSDN

La consulta agrega filas de todas las partes de una tabla (posiblemente) particionada.

index_id = 0 es una tabla desordenada (Heap) e index_id = 1 es una tabla ordenada (índice agrupado)

Incluso los métodos más rápidos (pero no confiables) se detallan here.

Answer 5

Se sabe que contar filas en tablas grandes es lento en PostgreSQL. Para obtener un número preciso, tiene que hacer un recuento completo de filas debido a la naturaleza de MVCC . Hay una manera de acelerar esto dramáticamente si el recuento no tiene que ser exacto como parece ser en su caso.

En lugar de obtener el recuento exacto ( lento con tablas grandes):

SELECT count(*) AS exact_count FROM myschema.mytable;

Obtienes un estimado cercano como este ( extremadamente rápido ):

SELECT reltuples::bigint AS estimate FROM pg_class where relname=''mytable'';

Qué tan cerca está la estimación depende de si ejecuta ANALYZE suficiente. Por lo general, está muy cerca.
Consulte las Preguntas frecuentes sobre el Wiki de PostgreSQL .
O la página wiki dedicada para el rendimiento de recuento (*) .

Mejor todavía

El artículo en la Wiki de PostgreSQL es un poco descuidado . Ignoraba la posibilidad de que haya múltiples tablas del mismo nombre en una base de datos, en diferentes esquemas. Para dar cuenta de eso:

SELECT c.reltuples::bigint AS estimate
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname = ''mytable''
AND    n.nspname = ''myschema''

O mejor aún

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = ''myschema.mytable''::regclass;

Más rápido, más simple, más seguro, más elegante. Vea el manual sobre Tipos de Identificador de Objeto .

Use to_regclass(''myschema.mytable'') en Postgres 9.4+ para evitar excepciones para los nombres de tabla inválidos:

• Cómo verificar si una tabla existe en un esquema dado

TABLESAMPLE SYSTEM (n) en Postgres 9.5+

SELECT 100 * count(*) AS estimate FROM mytable TABLESAMPLE SYSTEM (1);

Al igual que @a_horse , la nueva cláusula añadida para el comando SELECT podría ser útil si las estadísticas en pg_class no son lo suficientemente actuales por alguna razón. Por ejemplo:

• No hay autovacuum ejecución.
• Inmediatamente después de un gran INSERT o DELETE .
• Tablas TEMPORARY (que no están cubiertas por autovacuum ).

Esto solo mira una selección aleatoria de n % ( 1 en el ejemplo) de bloques y cuenta filas en ella. Una muestra más grande aumenta el costo y reduce el error, su elección. La precisión depende de más factores:

• Distribución del tamaño de fila. Si un bloque determinado pasa a tener filas más anchas que las habituales, el recuento es menor que el habitual, etc.
• Las tuplas muertas o un FILLFACTOR ocupan espacio por bloque. Si se distribuye de manera desigual en toda la tabla, la estimación puede estar desactivada.
• Errores generales de redondeo.

En la mayoría de los casos, la estimación de pg_class será más rápida y más precisa.

Responder a una pregunta real

Primero, necesito saber el número de filas en esa tabla, si el conteo total es mayor que alguna constante predefinida,

Y si ...

... es posible en el momento en que el conteo pase mi valor constante, detendrá el conteo (y no esperará a terminar el recuento para informar que el número de filas es mayor).

Sí. Puede usar una subconsulta con LIMIT :

SELECT count(*) FROM (SELECT 1 FROM token LIMIT 500000) t;

De hecho, Postgres deja de contar más allá del límite dado, obtienes un recuento exacto y actual de hasta n filas (500000 en el ejemplo) yn de lo contrario. No pg_class , no es tan rápido como el estimado en pg_class .

Answer 6

Referencia tomada de este Blog.

Puede utilizar a continuación para consultar para encontrar el recuento de filas.

Usando pg_class:

 SELECT reltuples::bigint AS EstimatedCount
    FROM   pg_class
    WHERE  oid = ''public.TableName''::regclass;

Usando pg_stat_user_tables:

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