Getting top rows with the latest related row in joined table quickly

Question

There are two tables conversations and messages, I want to fetch conversations along with the content of their latest message.

conversations - id(PRIMARY KEY), name, created_at

messages - id, content, created_at, conversation_id

Currently we are running this query to get the required data

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

The above query is returning the valid data but its performance decreases with the increasing number of rows. Is there any other way to write this query with increased performance? Attaching the fiddle for example.

http://sqlfiddle.com/#!17/2decb/2

Also tried the suggestions in one of the deleted answers:

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

But the problem with this query is it doesn't sort by m.created_at. I want the resultset to be sorted by m.created_at DESC

Answer 1

Have you tried a lateral join instead?

SELECT
    conversations.id,
    m.content AS last_message_content,
    m.created_at AS last_message_at
FROM "conversations" 
INNER JOIN LATERAL (
  SELECT content, created_at 
  FROM  messages m
  WHERE conversations.id = m.conversation_id 
  ORDER BY created_at DESC
  FETCH FIRST 1 ROW ONLY
) m ON TRUE
ORDER BY last_message_at DESC
LIMIT 15 OFFSET 0