ultima - obtener ultimo registro por fecha sql

Optimizar la consulta GROUP BY para recuperar el último registro por usuario

Question

Tengo la siguiente tabla (formulario simplificado) en Postgres 9.2

CREATE TABLE user_msg_log (
aggr_date DATE,
user_id INTEGER,
running_total INTEGER
);

Contiene hasta un registro por usuario y por día. Habrá aproximadamente 500K registros por día durante 300 días. running_total siempre está aumentando para cada usuario.

Quiero recuperar de manera eficiente el último registro para cada usuario antes de una fecha específica. Mi consulta es:

SELECT user_id, max(aggr_date), max(running_total)
FROM user_msg_log
WHERE aggr_date <= :mydate
GROUP BY user_id

que es extremadamente lento También he intentado:

SELECT DISTINCT ON(user_id), aggr_date, running_total
FROM user_msg_log
WHERE aggr_date <= :mydate
ORDER BY user_id, aggr_date DESC;

que tiene el mismo plan y es igualmente lento.

Hasta ahora tengo un índice único en user_msg_log (aggr_date), pero no ayuda mucho. ¿Hay algún otro índice que deba usar para acelerar esto o cualquier otra forma de lograr lo que quiero?

Answer 1

Esta no es una respuesta independiente sino más bien un comentario a la answer @ Erwin. Para 2a, el ejemplo de unión lateral, la consulta se puede mejorar clasificando la tabla de users para explotar la localidad del índice en user_msg_log .

SELECT u.user_id, l.aggr_date, l.running_total
FROM (SELECT user_id FROM users ORDER BY user_id) u,
LATERAL (SELECT aggr_date, running_total
FROM user_msg_log
WHERE user_id = u.user_id -- lateral reference
AND aggr_date <= :mydate
ORDER BY aggr_date DESC NULLS LAST
LIMIT 1) l;

El razonamiento es que la búsqueda de índice es costosa si los valores de user_id son aleatorios. Al ordenar user_id primero, la unión lateral posterior sería como un simple escaneo en el índice de user_msg_log . Aunque ambos planes de consulta se parecen, el tiempo de ejecución sería muy diferente, especialmente para tablas grandes.

El costo de la clasificación es mínimo, especialmente si hay un índice en el campo user_id .

Answer 2

Para obtener el mejor rendimiento de lectura necesita un índice de columnas múltiples :

CREATE INDEX user_msg_log_combo_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST)

Para que solo sea ​​posible realizar escaneos indexados , agregue la columna de otra manera no necesaria running_total :

CREATE INDEX user_msg_log_combo_covering_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST, running_total)

¿Por qué DESC NULLS LAST ?

• Índice no utilizado en el rango de consulta de fechas

Para algunas filas por user_id o tablas pequeñas, una DISTINCT ON simple está entre las soluciones más rápidas y simples:

• Seleccione la primera fila en cada grupo GROUP BY?

Para muchas filas por user_id un escaneo de índice flojo sería (mucho) más eficiente. Eso no está implementado en Postgres (al menos hasta Postgres 10), pero hay formas de emularlo:

1. No hay una tabla separada con usuarios únicos

Las siguientes soluciones van más allá de lo que cubre la Wiki de Postgres .
^{Con una tabla de users separada, las soluciones en 2. a continuación son generalmente más simples y rápidas.}

1a. CTE recursivo con unión LATERAL

Las expresiones de tabla comunes requieren Postgres 8.4+ .
LATERAL requiere Postgres 9.3+ .

WITH RECURSIVE cte AS (
( -- parentheses required
SELECT user_id, aggr_date, running_total
FROM user_msg_log
WHERE aggr_date <= :mydate
ORDER BY user_id, aggr_date DESC NULLS LAST
LIMIT 1
)
UNION ALL
SELECT u.user_id, u.aggr_date, u.running_total
FROM cte c
, LATERAL (
SELECT user_id, aggr_date, running_total
FROM user_msg_log
WHERE user_id > c.user_id -- lateral reference
AND aggr_date <= :mydate -- repeat condition
ORDER BY user_id, aggr_date DESC NULLS LAST
LIMIT 1
) u
)
SELECT user_id, aggr_date, running_total
FROM cte
ORDER BY user_id;

Esto es preferible en las versiones actuales de Postgres y es sencillo recuperar columnas arbitrarias. Más explicación en el capítulo 2a. abajo.

1b. CTE recursivo con subconsultas correlacionadas

Conveniente para recuperar una sola columna o toda la fila . El ejemplo usa todo el tipo de fila de la tabla. Otras variantes son posibles

WITH RECURSIVE cte AS (
(
SELECT u -- whole row
FROM user_msg_log u
WHERE aggr_date <= :mydate
ORDER BY user_id, aggr_date DESC NULLS LAST
LIMIT 1
)
UNION ALL
SELECT (SELECT u1 -- again, whole row
FROM user_msg_log u1
WHERE user_id > (c.u).user_id -- parentheses to access row type
AND aggr_date <= :mydate -- repeat predicate
ORDER BY user_id, aggr_date DESC NULLS LAST
LIMIT 1)
FROM cte c
WHERE (c.u).user_id IS NOT NULL -- any NOT NULL column of the row
)
SELECT (u).* -- finally decompose row
FROM cte
WHERE (u).user_id IS NOT NULL -- any column defined NOT NULL
ORDER BY (u).user_id;

Podría ser engañoso probar el valor de la fila con cu IS NOT NULL . Esto solo devuelve true si cada columna de la fila probada NOT NULL es NOT NULL y fallaría si se incluye un único valor NULL . (Tuve este error en mi respuesta durante un tiempo). En su lugar, para afirmar que se encontró una fila en la iteración anterior, pruebe una sola columna de la fila que se define como NOT NULL (como la clave principal). Más:

• Restricción NOT NULL sobre un conjunto de columnas
• IS NOT NULL La prueba de un registro no devuelve TRUE cuando se establece una variable

Más explicación para esta consulta en el capítulo 2b. abajo.
Respuestas relacionadas:

• Consultar las últimas N filas relacionadas por fila
• Agrupe por una columna, mientras ordena por otro en PostgreSQL

2. Con tabla de users separados

El diseño de la tabla apenas importa, siempre que tengamos exactamente una fila por cada user_id relevante. Ejemplo:

CREATE TABLE users (
user_id serial PRIMARY KEY
, username text NOT NULL
);

Idealmente, la tabla está clasificada físicamente. Ver:

• Optimizar el rango de consultas de marca de tiempo de Postgres

O es lo suficientemente pequeño (cardinalidad baja) que apenas importa.
De lo contrario, ordenar filas en la consulta puede ayudar a optimizar aún más el rendimiento. Ver la adición de Gang Liang.

2a. LATERAL unirse

SELECT u.user_id, l.aggr_date, l.running_total
FROM users u
CROSS JOIN LATERAL (
SELECT aggr_date, running_total
FROM user_msg_log
WHERE user_id = u.user_id -- lateral reference
AND aggr_date <= :mydate
ORDER BY aggr_date DESC NULLS LAST
LIMIT 1
) l;

JOIN LATERAL permite hacer referencia a elementos FROM anteriores en el mismo nivel de consulta. Obtiene un índice (solo) búsqueda por usuario.

• ¿Cuál es la diferencia entre LATERAL y una subconsulta en PostgreSQL?

Considere la posible mejora ordenando la tabla de users sugerida por Gang Liang en otra respuesta . Si el orden físico de clasificación de la tabla de users coincide con el índice en user_msg_log , no es necesario.

No obtiene resultados para los usuarios que faltan en la tabla de users , incluso si tiene entradas en user_msg_log . Normalmente, tendría una restricción de clave externa que imponga la integridad referencial para descartarla.

Tampoco obtiene una fila para ningún usuario que no tenga ninguna entrada coincidente en user_msg_log . Eso se ajusta a tu pregunta original. Si necesita incluir esas filas en el resultado, use LEFT JOIN LATERAL ... ON true lugar de CROSS JOIN LATERAL :

• Llamar a una función de devolución de conjunto con un argumento de matriz varias veces

Este formulario también es mejor para recuperar más de una fila (pero no todas) por usuario. Simplemente use LIMIT n lugar de LIMIT 1 .

Efectivamente, todos estos harían lo mismo:

JOIN LATERAL ... ON true
CROSS JOIN LATERAL ...
, LATERAL ...

Sin embargo, este último tiene una prioridad menor. Explicit JOIN une antes de la coma.

2b. Subconsulta correlacionada

Buena opción para recuperar una sola columna de una sola fila . Ejemplo de código:

• Optimizar la consulta máxima de Groupwise

Lo mismo es posible para columnas múltiples , pero necesita más inteligencia:

CREATE TEMP TABLE combo (aggr_date date, running_total int);
SELECT user_id, (my_combo).* -- note the parentheses
FROM (
SELECT u.user_id
, (SELECT (aggr_date, running_total)::combo
FROM user_msg_log
WHERE user_id = u.user_id
AND aggr_date <= :mydate
ORDER BY aggr_date DESC NULLS LAST
LIMIT 1) AS my_combo
FROM users u
) sub;

• Al igual que LEFT JOIN LATERAL anterior, esta variante incluye todos los usuarios, incluso sin entradas en user_msg_log . my_combo NULL para my_combo , que puedes filtrar fácilmente con una cláusula WHERE en la consulta externa si es necesario.
  _{Nitpick: en la consulta externa no se puede distinguir si la subconsulta no encontró una fila o si todos los valores devueltos son NULL: el mismo resultado. Tendría que incluir una columna NOT NULL en la subconsulta para estar seguro.}

• Una subconsulta correlacionada solo puede devolver un solo valor . Puede envolver varias columnas en un tipo compuesto. Pero para descomponerlo más tarde, Postgres exige un tipo compuesto conocido. Los registros anónimos solo se pueden descomponer proporcionando una lista de definición de columna.

• Use un tipo registrado como el tipo de fila de una tabla existente, o cree un tipo. Registre un tipo compuesto de forma explícita (y permanente) con CREATE TYPE , o cree una tabla temporal (que se elimine automáticamente al final de la sesión) para proporcionar un tipo de fila temporalmente. Transmitir a ese tipo: (aggr_date, running_total)::combo

• Finalmente, no queremos descomponer el combo en el mismo nivel de consulta. Debido a una debilidad en el planificador de consultas, esto evaluaría la subconsulta una vez para cada columna (hasta Postgres 9.6 - se planean mejoras para Postgres 10). En su lugar, haga una subconsulta y descompóngase en la consulta externa.

Relacionado:

• Obtener valores de la primera y la última fila por grupo

Demostrando las 4 consultas con 100k entradas de registro y 1k usuarios:
SQL Fiddle - pg 9.6
db <> violín here - pg 10

Answer 3

Quizás un índice diferente sobre la mesa ayudaría. Prueba este: user_msg_log(user_id, aggr_date) . No estoy seguro de que Postgres haga un uso óptimo con distinct on .

Por lo tanto, me quedaría con ese índice y probaré esta versión:

select *
from user_msg_log uml
where not exists (select 1
from user_msg_log uml2
where uml2.user_id = u.user_id and
uml2.aggr_date <= :mydate and
uml2.aggr_date > uml.aggr_date
);

Esto debería reemplazar la clasificación / agrupación con búsquedas de índice. Puede ser más rápido.