Manera rápida de descubrir el recuento de filas de una tabla en PostgreSQL

Se sabe que contar filas en tablas grandes es lento en PostgreSQL. El modelo MVCC requiere un recuento completo de filas activas para obtener un número preciso. Existen soluciones para acelerar esto drásticamente si el recuento no tiene que ser exacto como parece ser en su caso.

(Recuerde que incluso un recuento "exacto" está potencialmente muerto al llegar bajo una carga de escritura simultánea).

recuento exacto

Lento para mesas grandes.
Con operaciones de escritura simultáneas, es posible que quede obsoleto en el momento en que lo obtenga.

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

Estimar

Extremadamente rápido :

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

Normalmente, la estimación es muy cercana. Qué tan cerca depende de si ANALYZEse VACUUMejecutan lo suficiente, donde "suficiente" se define por el nivel de actividad de escritura en su tabla.

Estimación más segura

Lo anterior ignora la posibilidad de que haya varias tablas con el 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';

El elenco bigintformatea bien el realnúmero, especialmente para conteos grandes.

Mejor estimación

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

Más rápido, más sencillo, más seguro, más elegante. Consulte el manual sobre tipos de identificadores de objetos .

Reemplace 'myschema.mytable'::regclasscon to_regclass('myschema.mytable')en Postgres 9.4+ para no obtener nada en lugar de una excepción para nombres de tablas no válidos. Ver:

• Cómo comprobar si existe una tabla en un esquema determinado

Mejor estimación aún (por muy poco costo adicional)

Esto no funciona para tablas particionadas porque relpagessiempre es -1 para la tabla principal (aunque reltuplescontiene una estimación real que cubre todas las particiones), probado en Postgres 14.
En su lugar, debe sumar estimaciones para todas las particiones.

Podemos hacer lo que hace el planificador de Postgres. Citando los ejemplos de estimación de filas en el manual :

Estos números están actualizados hasta el último VACUUMo ANALYZEen la tabla. Luego, el planificador recupera el número actual de páginas de la tabla (esta es una operación económica que no requiere un escaneo de la tabla). Si es diferente, relpagesse reltuplesescala en consecuencia para llegar a una estimación actual del número de filas.

Postgres usa estimate_rel_sizedefinido en src/backend/utils/adt/plancat.c, que también cubre el caso de esquina sin datos pg_classporque la relación nunca fue eliminada. Podemos hacer algo similar en SQL:

forma mínima

SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM   pg_class
WHERE  oid = 'mytable'::regclass;  -- your table here

Seguro y explícito

SELECT (CASE WHEN c.reltuples < 0 THEN NULL       -- never vacuumed
             WHEN c.relpages = 0 THEN float8 '0'  -- empty table
             ELSE c.reltuples / c.relpages END
     * (pg_catalog.pg_relation_size(c.oid)
      / pg_catalog.current_setting('block_size')::int)
       )::bigint
FROM   pg_catalog.pg_class c
WHERE  c.oid = 'myschema.mytable'::regclass;      -- schema-qualified table here

No rompe con mesas vacías y mesas que nunca has visto VACUUMo ANALYZE. El manual sobrepg_class :

Si la tabla aún no se ha aspirado ni analizado, reltuplescontiene -1una indicación de que se desconoce el recuento de filas.

Si esta consulta devuelve NULL, ejecute ANALYZEo VACUUMpara la tabla y repita. (Como alternativa, puede estimar el ancho de fila basándose en los tipos de columnas como lo hace Postgres, pero eso es tedioso y propenso a errores).

Si esta consulta devuelve 0, la tabla parece estar vacía. Pero me ANALYZEaseguraría. (Y tal vez verifique su autovacuumconfiguración).

Normalmente, block_sizees 8192. current_setting('block_size')::intCubre raras excepciones.

Las calificaciones de tablas y esquemas lo hacen inmune a cualquier search_pathalcance.

De cualquier manera, la consulta siempre toma <0,1 ms para mí.

Más recursos web:

• Preguntas frecuentes sobre Postgres Wiki
• Las páginas wiki de Postgres para estimaciones de recuento y rendimiento de recuento (*)

TABLESAMPLE SYSTEM (n)en Postgres 9.5+

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

Como comentó @a_horse , la cláusula agregada para el SELECTcomando puede ser útil si las estadísticas pg_classno están lo suficientemente actualizadas por algún motivo. Por ejemplo:

• No autovacuumcorrer.
• Inmediatamente después de un gran INSERT// .UPDATEDELETE
• TEMPORARYtablas (que no están cubiertas por autovacuum).

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

• Distribución del tamaño de fila. Si un bloque determinado contiene filas más anchas de lo habitual, el recuento es menor de lo habitual, etc.
• Tuplas muertas o FILLFACTORocupan espacio por bloque. Si se distribuye de manera desigual en la tabla, la estimación puede estar equivocada.
• Errores generales de redondeo.

Normalmente, la estimación pg_classserá más rápida y precisa.

Respuesta a pregunta real

Primero, necesito saber el número de filas en esa tabla, si el recuento 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 conteo para informar que el conteo de filas es mayor).

Sí. Puede utilizar una subconsulta conLIMIT :

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

Postgres en realidad deja de contar más allá del límite dado, obtienes un recuento exacto y actual de hasta n filas (500000 en el ejemplo), y n en caso contrario. Sin embargo, no es tan rápido como la estimación de pg_class.

Oct 30 '2011 13:10 Erwin Brandstetter