tablas - sql contar registros agrupados

¿Cómo acelero el conteo de filas en una tabla PostgreSQL?

Question

Necesitamos contar el número de filas en una tabla PostgreSQL. En nuestro caso, no es necesario cumplir ninguna condición, y sería perfectamente aceptable obtener una estimación de la fila si eso mejorara significativamente la velocidad de consulta.

Básicamente, queremos que select count(id) from <table> ejecute lo más rápido posible, incluso si eso implica no obtener resultados exactos.

¡Gracias! ¡Cualquier sugerencia será apreciada!

Answer 1

Además de ejecutar COUNT () en un campo indexado (que con suerte es ''id''), lo mejor sería almacenar en caché el recuento de filas en alguna tabla usando un disparador en INSERT. Naturalmente, usted revisará el caché en su lugar.

Para una aproximación puedes probar esto (desde https://wiki.postgresql.org/wiki/Count_estimate ):

select reltuples from pg_class where relname=''tablename'';

Answer 2

El recuento es lento para las tablas grandes, por lo que puede obtener una estimación aproximada de esta manera:

SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname=''tableName'';

y es extremadamente rápido, los resultados no son flotantes, pero aún así son una estimación aproximada.

• reltuples es una columna de la tabla pg_class , contiene datos sobre "número de filas en la tabla. Esta es solo una estimación utilizada por el planificador. Se actualiza mediante VACUUM, ANALYZE y algunos comandos DDL como CREATE INDEX" (manual )
• El catálogo pg_class cataloga tablas y casi todo lo demás que tiene columnas o es similar a una tabla. Esto incluye índices (pero vea también pg_index), secuencias, vistas, tipos compuestos y algunos tipos de relación especial (manual)
• "¿Por qué" SELECT count (*) FROM bigtable; "slow?" : http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F

Answer 3

Para una estimación rápida:

SELECT reltuples FROM pg_class WHERE oid = ''my_schema.my_table''::regclass;

Esto es superior a las consultas presentadas hasta ahora, incluido el consejo en el wiki.postgresql.org/wiki/Slow_Counting (Lo actualicé por ahora):
relname no es único en pg_class . Puede haber varias tablas con el mismo relname en varios esquemas de la base de datos. Ese es regularmente el caso en mis instalaciones.

Y una consulta en pg_stat_user_tables es 500 veces más lenta, porque esa es una vista en un par de tablas.

Si no califica el esquema con el nombre de la tabla, un molde a regclass observa el actual search_path para elegir la mejor coincidencia. Y si la tabla no existe (o no se puede ver) en ninguno de los esquemas en la search_path , search_path un mensaje de error.

Detalles sobre los tipos de identificadores de objeto en el manual .

Respuesta relacionada con nuevas opciones:

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

Answer 4

Puede obtener una estimación de la tabla del sistema "pg_stat_user_tables".

select schemaname, relname, n_live_tup
from pg_stat_user_tables
where schemaname = ''your_schema_name''
and relname = ''your_table_name'';

Answer 5

Puede pedir el valor exacto del conteo en la tabla simplemente usando el gatillo DESPUÉS DE INSERTAR O ELIMINAR Algo como esto

CREATE TABLE tcounter(id serial primary key,table_schema text, table_name text, count serial);
insert into tcounter(table_schema, table_name,count) select ''my_schema'', ''my_table'', count(*) from my_schema.my_table;

y usa el disparador

CREATE OR REPLACE FUNCTION ex_count()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP=''INSERT'') THEN
UPDATE tcounter set count = count + 1 where table_schema = TG_TABLE_SCHEMA::TEXT and table_name = TG_TABLE_NAME::TEXT;
ELSIF (TG_OP=''DELETE'') THEN
UPDATE tcounter set count = count - 1 where table_schema = TG_TABLE_SCHEMA::TEXT and table_name = TG_TABLE_NAME::TEXT;
END IF;
RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER tg_counter AFTER INSERT OR DELETE
ON my_schema.my_table FOR EACH ROW EXECUTE PROCEDURE ex_count();

Y pide contar

select * from tcounter where table_schema = ''my_schema'' and table_name = ''my_table''

significa que selecciona conteo (*) una vez para inicializar el primer registro

Answer 6

Si su base de datos es pequeña, puede obtener una estimación de todas sus tablas, como se sugirió @ mike-sherrill-cat-recall. Este comando enumerará todas las tablas sin embargo.

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

La salida sería algo como esto:

 schemaname | relname | n_live_tup
------------+--------------------+------------
public | items | 21806
public | tags | 11213
public | sessions | 3269
public | users | 266
public | shops | 259
public | quantities | 34
public | schema_migrations | 30
public | locations | 8
(8 rows)