Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE messages (id int, content text, created_at date, conversation_id int);
INSERT INTO messages VALUES
  ( 1,'msg13', '2021-12-12', 1)
, ( 2,'msg12', '2021-12-11', 1)
, ( 3,'msg11', '2021-12-01', 1)
, ( 4,'msg24', '2021-11-11', 2)  -- old
, ( 5,'msg32', '2021-12-12', 3)
, ( 6,'msg31', '2021-12-11', 3)
, ( 7,'msg41', '2021-12-04', 4)
, ( 8,'msg51', '2021-12-05', 5)
, ( 9,'msg61', '2021-12-06', 6)
, (17,'msg41', '2021-11-04', 4)
, (18,'msg51', '2021-11-05', 5)
, (19,'msg61', '2021-11-06', 6)
;

CREATE INDEX idx1 ON messages (created_at DESC);
12 rows affected

WITH RECURSIVE cte AS (
   (
   SELECT conversation_id, content, created_at, ARRAY[conversation_id] AS latest_ids
   FROM   messages
   ORDER  BY created_at DESC
   LIMIT  1
   )
   UNION ALL
   SELECT m.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT m.conversation_id, m.content, m.created_at, c.latest_ids || m.conversation_id
      FROM   messages m
      WHERE  m.created_at <= c.created_at
      AND    m.conversation_id <> ALL (c.latest_ids)
      ORDER  BY m.created_at DESC
      LIMIT  1
      ) m
   -- WHERE  cardinality(c.latest_ids) < 5  -- not necessary
   )
SELECT conversation_id
     , content    AS last_message_content
     , created_at AS last_message_at
FROM   cte
LIMIT  5; -- smaller limit to see effect


conversation_id
last_message_content
last_message_at


1
msg13
2021-12-12

3
msg32
2021-12-12

6
msg61
2021-12-06

5
msg51
2021-12-05

4
msg41
2021-12-04


SET enable_seqscan = off;  -- to see possible index use with minimal table

EXPLAIN ANALYZE
WITH RECURSIVE cte AS (
   (
   SELECT conversation_id, content, created_at, ARRAY[conversation_id] AS latest_ids
   FROM   messages
   ORDER  BY created_at DESC
   LIMIT  1
   )
   UNION ALL
   SELECT m.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT m.conversation_id, m.content, m.created_at, c.latest_ids || m.conversation_id
      FROM   messages m
      WHERE  m.created_at <= c.created_at
      AND    m.conversation_id <> ALL (c.latest_ids)
      ORDER  BY m.created_at DESC
      LIMIT  1
      ) m
   -- WHERE  cardinality(c.latest_ids) < 5  -- not necessary
   )
SELECT conversation_id
     , content    AS last_message_content
     , created_at AS last_message_at
FROM   cte
LIMIT  5;


QUERY PLAN


Limit  (cost=426.92..427.02 rows=5 width=40) (actual time=0.016..0.093 rows=5 loops=1)

  CTE cte

    ->  Recursive Union  (cost=0.14..426.92 rows=101 width=72) (actual time=0.014..0.086 rows=5 loops=1)

          ->  Limit  (cost=0.14..1.15 rows=1 width=72) (actual time=0.013..0.013 rows=1 loops=1)

                ->  Index Scan using idx1 on messages  (cost=0.14..12.31 rows=12 width=72) (actual time=0.012..0.012 rows=1 loops=1)

          ->  Nested Loop  (cost=0.14..42.38 rows=10 width=72) (actual time=0.013..0.016 rows=1 loops=4)

                ->  WorkTable Scan on cte c  (cost=0.00..0.20 rows=10 width=36) (actual time=0.000..0.000 rows=1 loops=4)

                ->  Limit  (cost=0.14..4.20 rows=1 width=72) (actual time=0.011..0.011 rows=1 loops=4)

                      ->  Index Scan using idx1 on messages m  (cost=0.14..8.26 rows=2 width=72) (actual time=0.010..0.010 rows=1 loops=4)

                            Index Cond: (created_at <= c.created_at)

                            Filter: (conversation_id <> ALL (c.latest_ids))

                            Rows Removed by Filter: 2

  ->  CTE Scan on cte  (cost=0.00..2.02 rows=101 width=40) (actual time=0.015..0.079 rows=5 loops=1)

Planning Time: 0.229 ms

Execution Time: 0.210 ms
conversation_id	last_message_content	last_message_at
1	msg13	2021-12-12
3	msg32	2021-12-12
6	msg61	2021-12-06
5	msg51	2021-12-05
4	msg41	2021-12-04