Optimice la consulta GROUP BY para recuperar la última fila por usuario

Para obtener el mejor rendimiento de lectura, necesita un índice de varias columnas :

CREATE INDEX log_combo_idx
ON log (user_id, log_date DESC NULLS LAST);

Para que los escaneos solo de índice sean posibles, agregue la columna que de otro modo no sería necesaria payloaden un índice de cobertura con la INCLUDEcláusula (Postgres 11 o posterior):

CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST) INCLUDE (payload);

Ver:

• ¿Cubrir índices en PostgreSQL ayuda a UNIR columnas?

Respaldo para versiones anteriores:

CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST, payload);

Por qué DESC NULLS LAST?

• Índice no utilizado en consulta de rango de fechas

Para pocas filas por tabla user_ido tablas pequeñas DISTINCT ONsuele ser lo más rápido y sencillo:

• ¿Seleccionar la primera fila en cada grupo GRUPO POR?

Para muchas filas por user_idun escaneo de omisión de índice (o escaneo de índice suelto ) es (mucho) más eficiente. Esto no se implementó hasta Postgres 15 (el trabajo está en curso) . Pero hay formas de emularlo de manera eficiente.

Las expresiones de tabla comunes requieren Postgres 8.4+ .
LATERALrequiere Postgres 9.3+ .
Las siguientes soluciones van más allá de lo que se cubre en Postgres Wiki .

1. No hay una tabla separada con usuarios únicos

Con una userstabla separada, las soluciones del punto 2. a continuación suelen ser más simples y rápidas. Vaya directamente.

1a. CTE recursivo con LATERALunión

WITH RECURSIVE cte AS (
   (                                -- parentheses required
   SELECT user_id, log_date, payload
   FROM   log
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT l.user_id, l.log_date, l.payload
      FROM   log l
      WHERE  l.user_id > c.user_id  -- lateral reference
      AND    log_date <= :mydate    -- repeat condition
      ORDER  BY l.user_id, l.log_date DESC NULLS LAST
      LIMIT  1
      ) l
   )
TABLE  cte
ORDER  BY user_id;

Es sencillo recuperar columnas arbitrarias y probablemente sea mejor en Postgres actual. Más explicación en el capítulo 2a. abajo.

1b. CTE recursivo con subconsulta correlacionada

WITH RECURSIVE cte AS (
   (                                           -- parentheses required
   SELECT l AS my_row                          -- whole row
   FROM   log l
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT l                            -- whole row
           FROM   log l
           WHERE  l.user_id > (c.my_row).user_id
           AND    l.log_date <= :mydate        -- repeat condition
           ORDER  BY l.user_id, l.log_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.my_row).user_id IS NOT NULL       -- note parentheses
   )
SELECT (my_row).*                              -- decompose row
FROM   cte
WHERE  (my_row).user_id IS NOT NULL
ORDER  BY (my_row).user_id;

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

Para afirmar que se encontró una fila en la iteración anterior, pruebe una sola columna NO NULA (como la clave principal).

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

Relacionado:

• Consultar las últimas N filas relacionadas por fila
• AGRUPAR POR una columna, mientras ordena por otra en PostgreSQL

2. Con usersmesa separada

El diseño de la tabla apenas importa siempre que user_idse garantice exactamente una fila por cada elemento relevante. Ejemplo:

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

Lo ideal es que la tabla esté físicamente ordenada en sincronía con la logtabla. Ver:

• Optimice la consulta de Postgres en el rango de marca de tiempo

O es lo suficientemente pequeño (baja cardinalidad) que apenas importa. De lo contrario, ordenar filas en la consulta puede ayudar a optimizar aún más el rendimiento. Vea la incorporación de Gang Liang. Si el orden físico de la userstabla coincide con el índice de log, esto puede ser irrelevante.

2a. LATERALunirse

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

JOIN LATERALpermite hacer referencia FROMa elementos anteriores en el mismo nivel de consulta. Ver:

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

Da como resultado una búsqueda de índice (solo) por usuario.

No devuelve ninguna fila para los usuarios que faltan en la userstabla. Normalmente, una restricción de clave externa que imponga la integridad referencial descartaría esto.

Además, no hay fila para usuarios sin una entrada coincidente log, de conformidad con la pregunta original. Para mantener a esos usuarios en el resultado, utilice LEFT JOIN LATERAL ... ON trueen lugar de CROSS JOIN LATERAL:

• Llame a una función de retorno de conjunto con un argumento de matriz varias veces

Úselo LIMIT nen lugar de LIMIT 1para recuperar más de una fila (pero no todas) por usuario.

Efectivamente, todos estos hacen lo mismo:

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

Sin embargo, el último tiene menor prioridad. JOINEnlaces explícitos antes de la coma. Esa sutil diferencia puede ser importante con más tablas de unión. Ver:

• "referencia no válida a la entrada de la cláusula FROM para la tabla" en la consulta de Postgres

2b. Subconsulta correlacionada

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

• Optimizar la consulta máxima por grupo

Lo mismo es posible para varias columnas , pero necesitas más inteligencia:

CREATE TEMP TABLE combo (log_date date, payload int);

SELECT user_id, (combo1).*              -- note parentheses
FROM (
   SELECT u.user_id
        , (SELECT (l.log_date, l.payload)::combo
           FROM   log l
           WHERE  l.user_id = u.user_id
           AND    l.log_date <= :mydate
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   ) sub;

Como LEFT JOIN LATERALarriba, esta variante incluye a todos los usuarios, incluso sin entradas en log. Obtienes NULLfor combo1, que puedes filtrar fácilmente con una WHEREcláusula en la consulta externa si es necesario.
_{Quisquilloso: en la consulta externa no se puede distinguir si la subconsulta no encontró una fila o si todos los valores de las columnas son NULL; el mismo resultado. Necesita una NOT NULLcolumna en la subconsulta para evitar esta ambigüedad.}

Una subconsulta correlacionada sólo puede devolver un único valor . Puede envolver varias columnas en un tipo compuesto. Pero para descomponerlo más tarde, Postgres exige un tipo compuesto bien conocido. Los registros anónimos sólo se pueden descomponer proporcionando una lista de definición de columnas.
Utilice un tipo registrado como el tipo de fila de una tabla existente. O registre un tipo compuesto explícitamente (y permanentemente) con CREATE TYPE. O cree una tabla temporal (eliminada automáticamente al final de la sesión) para registrar su tipo de fila temporalmente. Sintaxis de transmisión:(log_date, payload)::combo

Finalmente, no queremos descomponernos combo1en el mismo nivel de consulta. Debido a una debilidad en el planificador de consultas, esto evaluaría la subconsulta una vez para cada columna (aún así en Postgres 12). En su lugar, conviértala en una subconsulta y descompóngala en la consulta externa.

Relacionado:

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

Demostrando las 4 consultas con 100.000 entradas de registro y 1.000 usuarios:
db<>fiddle aquí - página 11
_{Antiguo sqlfiddle}

Aug 27 '2014 20:08 Erwin Brandstetter