A menudo se queja de que el recuento (*) en PostgreSQL es muy lento.En este artículo, quiero explorar opciones para que obtenga el resultado lo más rápido posible.¿Por qué el conteo (*) es tan lento?
La mayoría de las personas entiende sin problemas que la siguiente solicitud se ejecutará lentamente:SELECT count(*)
FROM ;
Después de todo, esta es una consulta compleja, y PostgreSQL debe calcular el resultado antes de saber cuántas filas contendrá.Pero muchas personas se sorprenden cuando descubren que la siguiente consulta es lenta:SELECT count(*) FROM large_table;
Sin embargo, si lo piensa de nuevo, todo lo anterior sigue siendo válido: PostgreSQL debe calcular el conjunto de resultados antes de poder contarlo. Dado que el "contador de filas mágicas" no está almacenado en la tabla (como en MyISAM MySQL), la única forma de contar las filas es mirarlas.Por lo tanto, count (*) generalmente realiza exploraciones de tabla secuenciales, lo que puede ser bastante costoso.¿Es el "*" en el recuento (*) un problema?
El "*" en SELECT * FROM ... se aplica a todas las columnas. Por lo tanto, muchas personas encuentran que usar count (*) es ineficiente, y en su lugar usan count (id) o count (1).Pero el "*" en el recuento (*) es completamente diferente, simplemente significa "cadena" y no se expande en absoluto (de hecho, es "un agregado con un argumento nulo"). El recuento de notación (1) o el recuento (id) en realidad es más lento que el recuento (*), ya que debe verificarse si el argumento es NULL o no (el recuento, como la mayoría de los agregados, ignora los argumentos NULL).Por lo tanto, no logrará nada evitando el "*".Usando escaneo de índice solamente
Es tentador escanear un índice pequeño, no toda la tabla, para contar el número de filas. Sin embargo, esto no es tan simple en PostgreSQL debido a su estrategia de gestión de concurrencia de múltiples versiones. Cada versión de la fila ("tupla") contiene información sobre a qué instantánea de la base de datos está visible . Pero esta información (redundante) no se almacena en índices. Por lo tanto, generalmente no es suficiente contar las entradas en el índice, porque PostgreSQL debe mirar la entrada de la tabla ("tupla de montón") para asegurarse de que la entrada del índice sea visible.Para mitigar este problema, PostgreSQL introdujo un mapa de visibilidad , una estructura de datos que almacena información sobre si todas las tuplas en un bloque de tabla son visibles para todos o no.Si la mayoría de los bloques en la tabla son completamente visibles, entonces los escaneos de índice no requieren visitas frecuentes a un montón de tuplas para determinar la visibilidad. Tal exploración de índice se denomina "exploración de solo índice", y a menudo es más rápido escanear un índice para contar las filas.Ahora es VACUUM el que admite el mapa de visibilidad, así que asegúrese de que el autovacuum se realice con la suficiente frecuencia si desea usar un índice pequeño para acelerar el conteo (*).Usando la tabla pivote
Escribí anteriormente que PostgreSQL no almacena el número de filas en una tabla.Mantener dicho recuento de filas es una gran sobrecarga, ya que este evento ocurre con cada modificación de datos y no vale la pena. Eso sería un mal negocio. Además, dado que diferentes solicitudes pueden ver diferentes versiones de cadenas, el contador también debe ser versionado.Pero nada le impide implementar dicho contador de línea usted mismo.Supongamos que desea realizar un seguimiento del número de filas en una tabla de mytable. Puedes hacerlo de la siguiente manera:START TRANSACTION;
CREATE TABLE mytable_count(c bigint);
CREATE FUNCTION mytable_count() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE mytable_count SET c = c + 1;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE mytable_count SET c = c - 1;
RETURN OLD;
ELSE
UPDATE mytable_count SET c = 0;
RETURN NULL;
END IF;
END;$$;
CREATE CONSTRAINT TRIGGER mytable_count_mod
AFTER INSERT OR DELETE ON mytable
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE mytable_count();
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
-- initialize the counter table
INSERT INTO mytable_count
SELECT count(*) FROM mytable;
COMMIT;
Hacemos todo en una transacción para que no se puedan "perder" los cambios de datos en transacciones concurrentes debido a una condición de timbre.Esto está garantizado por el comando CREATE TRIGGER que bloquea la tabla en modo SHARE ROW EXCLUSIVE, lo que evita todos los cambios concurrentes.La desventaja es que todas las modificaciones de datos paralelos deben esperar hasta que se ejecute el recuento SELECT (*).Esto nos da una alternativa realmente rápida para contar (*), pero a costa de ralentizar todos los cambios de datos en la tabla. El uso de un activador de restricción diferida asegura que el bloqueo de fila en mytable_count sea lo más corto posible para mejorar la concurrencia.A pesar de que esta tabla de contadores puede recibir muchas actualizaciones, no hay peligroNo hay "hinchazón de la tabla" , porque todas estas serán actualizaciones "calientes" (actualizaciones CALIENTES).Realmente necesitas contar (*)
A veces la mejor solución es buscar una alternativa.A menudo, la aproximación es lo suficientemente buena y no necesita la cantidad exacta. En este caso, puede usar la puntuación que PostgreSQL usa para programar consultas:SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';
Este valor se actualiza mediante autovacuum y autoanalyze, por lo que nunca debe superar el 10%. Puede reducir autovacuum_analyze_scale_factor para esta tabla para que autoanalyze se ejecute allí con más frecuencia.Estimar el número de resultados de la consulta.
Hasta ahora, hemos estado explorando cómo acelerar el conteo de las filas de la tabla.Pero a veces necesita saber cuántas filas devolverá la instrucción SELECT sin ejecutar realmente la consulta.Obviamente, la única forma de obtener una respuesta precisa a esta pregunta es completar la solicitud. Pero si la calificación es lo suficientemente buena, puede usar el optimizador PostgreSQL para obtenerla.La siguiente función simple utiliza SQL dinámico y EXPLICAR para obtener el plan de ejecución de la consulta como argumento y devuelve una estimación del número de filas:CREATE FUNCTION row_estimator(query text) RETURNS bigint
LANGUAGE plpgsql AS
$$DECLARE
plan jsonb;
BEGIN
EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;
No utilice esta función para procesar sentencias SQL no confiables, ya que es inherentemente vulnerable a la inyección SQL.