Obteniendo las filas superiores con la fila relacionada más reciente en una tabla unida rápidamente.
Hay dos tablas, conversations
y messages
, y quiero obtener las conversaciones junto con el contenido de su último mensaje.
conversations
– id (CLAVE PRIMARIA), name, created_at
messages
– id, content, createdat, conversationid
Actualmente estamos ejecutando esta consulta para obtener los datos necesarios:
SELECT
conversations.id,
m.content AS last_message_content,
m.created_at AS last_message_at
FROM
conversations
INNER JOIN messages m ON conversations.id = m.conversation_id
AND m.id = (
SELECT
id
FROM
messages _m
WHERE
m.conversation_id = _m.conversation_id
ORDER BY
created_at DESC
LIMIT 1)
ORDER BY
last_message_at DESC
LIMIT 15
OFFSET 0
La consulta anterior devuelve los datos necesarios, pero su rendimiento disminuye a medida que aumenta el número de filas. ¿Existe alguna otra forma de escribir esta consulta con un rendimiento mejorado? Adjunto el ejemplo de Sqlfiddle.
http://sqlfiddle.com/#!17/2decb/2
También se intentaron las sugerencias en una de las respuestas eliminadas:
SELECT DISTINCT ON (c.id)
c.id,
m.content AS last_message_content,
m.created_at AS last_message_at
FROM conversations AS c
INNER JOIN messages AS m
ON c.id = m.conversation_id
ORDER BY c.id, m.created_at DESC
LIMIT 15 OFFSET 0
http://sqlfiddle.com/#!17/2decb/5
Pero el problema con esta consulta es que no se ordena por m.created_at
. Quiero que el conjunto de resultados esté ordenado por m.created_at DESC
.
davy.ai
Respuesta
Sí, hay una manera mejor de escribir esta consulta con un aumento de rendimiento. Podemos usar la técnica de “greatest-n-per-group” para obtener el último mensaje de cada conversación.
Aquí está la consulta optimizada:
Esta consulta utiliza una subconsulta para obtener el último mensaje de cada conversación utilizando
ORDER BY created_at DESC LIMIT 1
. Unimos este resultado utilizando un JOIN LATERAL a la tablaconversations
para obtener el resultado deseado. Este enfoque es más eficiente y funciona mejor que la consulta anterior.Aquí está el sqlfiddle actualizado: http://sqlfiddle.com/#!17/2decb/9
Esto debería darle el resultado deseado ordenado por
m.created_at DESC
.