Saltar al contenido

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

Te damos la bienvenida a nuestra página web, ahora hallarás la solucíon de lo que necesitas.

Solución:

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 hacer escaneos de solo índice posible, agregue la columna que de otro modo no sería necesaria payload en un índice de cobertura con el INCLUDE cláusula (Postgres 11 o posterior):

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

Ver:

  • ¿La cobertura de í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 la consulta de rango de fechas

Para pocos filas por user_id o mesas pequeñas DISTINCT ON suele ser el más rápido y sencillo:

  • ¿Seleccionar la primera fila de cada grupo GROUP BY?

Para muchos filas por user_id un escaneo de salto de índice (o escaneo de índice suelto) es (mucho) más eficiente. Eso no se implementó hasta Postgres 12; el trabajo está en curso para Postgres 14. Pero hay formas de emularlo de manera eficiente.

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

1. No hay una tabla separada con usuarios únicos

Con un separado users mesa, soluciones en 2. a continuación suelen ser más simples y rápidos. Vaya directamente.

1a. CTE recursiva con LATERAL entrar

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;

Esto es simple para recuperar columnas arbitrarias y probablemente sea mejor en Postgres actual. Más explicación en el capítulo 2a. debajo.

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 un una sola columna o la fila entera. El ejemplo usa todo el tipo de fila de la tabla. Son posibles otras variantes.

Para afirmar que se encontró una fila en la iteración anterior, pruebe una sola columna NOT NULL (como la key).

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

Relacionado:

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

2. Con separado users mesa

El diseño de la tabla apenas importa, siempre y cuando exactamente una fila por relevante user_id Está garantizado. Ejemplo:

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

Idealmente, la tabla se ordena físicamente en sincronía con el log mesa. Ver:

  • Optimizar el rango de consulta de la 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. Vea la adición de Gang Liang. Si el orden de clasificación físico del users la tabla coincide con el índice de log, esto puede ser irrelevante.

2a. LATERAL entrar

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 LATERAL permite referenciar precedente FROM elementos en el mismo nivel de consulta. Ver:

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

Resultados en una búsqueda de índice (solo) por usuario.

No devuelve ninguna fila para los usuarios que faltan en el users mesa. Normalmente, un extranjero key la restricción que imponga la integridad referencial lo descartaría.

Además, no hay fila para los usuarios sin una entrada coincidente en log - conforme a la pregunta original. Para mantener a esos usuarios en el uso de resultados LEFT JOIN LATERAL ... ON true en lugar de CROSS JOIN LATERAL:

  • Llamar a una función de devolución de conjuntos con un array argumento varias veces

Usar LIMIT n en lugar de LIMIT 1 para recuperar más de una fila (pero no todos) por usuario.

Efectivamente, todos estos hacen lo mismo:

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

Sin embargo, el último tiene menor prioridad. Explícito JOIN enlaza antes de la coma. Esa sutil diferencia puede ser importante con más tablas de combinació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 elección para recuperar un una sola columna a partir de una unica fila. Ejemplo de código:

  • Optimizar el número máximo de consultas grupales

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;

  • Igual que LEFT JOIN LATERAL arriba, esta variante incluye todos usuarios, incluso sin entradas en log. Usted obtiene NULL por combo1, que puede filtrar fácilmente con un WHERE cláusula 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 de las columnas son NULL - el mismo resultado. Tu necesitas un NOT NULL columna en la subconsulta para evitar esta ambigüedad.

  • Una subconsulta correlacionada solo puede devolver un valor único. 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 definiciones de columna.
    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 (que se elimina 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 descomponer combo1 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 (aún true en Postgres 12). En su lugar, conviértalo en una subconsulta y descomponga 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 <> violín aquí - página 11
Antiguo sqlfiddle - página 9.6

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

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

La razón es que la búsqueda de índices es costosa si user_id los valores son aleatorios. Clasificando user_id Primero, la unión lateral subsiguiente sería como un simple escaneo en el índice de 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 user_id campo.

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

Entonces, me quedaría con ese índice y probaría esta versión:

select *
from log l
where not exists (select 1
                  from log l2
                  where l2.user_id = l.user_id and
                        l2.log_date <= :mydate and
                        l2.log_date > l.log_date
                 );

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

Te invitamos a añadir valor a nuestro contenido informacional aportando tu experiencia en las acotaciones.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)

Tags : / / / / /

Utiliza Nuestro Buscador

Preguntas Relacionadas:

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *