Перечислите каждое сообщение из таблицы A с самым ранним ответом в таблице B в хронологическом порядке

Используйте CTE, чтобы ранжировать сообщения пользователя в каждом разговоре в зависимости от времени их создания, а затем используйте JOIN, чтобы связать каждое системное сообщение со следующим сообщением пользователя, используя номер строки. Попробуй это:

WITH ranked_user_messages AS (
    SELECT
        id,
        conversation_id,
        content,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_at) AS rn
    FROM user_messages
)
SELECT
    sm.content AS system_said,
    um.content AS user_said
FROM system_messages sm
LEFT JOIN LATERAL (
    SELECT content
    FROM user_messages um
    WHERE um.conversation_id = sm.conversation_id
      AND um.created_at > sm.created_at
    ORDER BY um.created_at
    LIMIT 1
) um ON true
WHERE sm.conversation_id = ?
ORDER BY sm.created_at;

Я бы попробовал:

Схема (PostgreSQL v15)

create table system_messages (
  id serial primary key,
  conversation_id int,
  content text,
  created_at timestamp
);

create table user_messages (
  id serial primary key,
  conversation_id int,
  content text,
  created_at timestamp
);

insert into system_messages(conversation_id, content, created_at) values
   (1, 'Hi!', '2024-05-31 08:00:00'),
   (1, 'How''s it going?', '2024-05-31 08:01:00'),
   (1, 'I''m good!', '2024-05-31 08:02:00');

insert into user_messages(conversation_id, content, created_at) values
   (1, 'Hello', '2024-05-31 08:00:30'),
   (1, 'I''m alright, you?', '2024-05-31 08:01:30'),
   (1, 'Nice.', '2024-05-31 08:02:30');

Запрос №1

with s_m (r_n, system_said) as (
  select row_number() over (order by created_at),
         content
  from system_messages
  where conversation_id = 1
),
u_m (r_n, player_said) as (
  select row_number() over (order by created_at),
         content
  from user_messages
  where conversation_id = 1
)
select s_m.system_said, u_m.player_said from
s_m join u_m on s_m.r_n = u_m.r_n
order by s_m.r_n;

Посмотреть на DB Fiddle

Ваша идея с отличным от звучит хорошо, но есть несколько вещей, которые вы могли бы улучшить:

1. Вам не нужен подзапрос, вы можете присоединиться напрямую.
2. Distinct on(sm) проверяет всю запись FROM system_messages sm. Поскольку у него есть первичный ключ, distinct on(sm.id) достаточно.
3. order by, который вы используете с distinct on, может использовать не только уникальный столбец. Если вы хотите получить ответ как можно скорее, добавьте его временную метку: order by sm.id,um.created_at.

В демо-версии db<>fiddle с 20 тысячами разговоров и 200 тысячами сообщений в них требуется 40ms:

SELECT
    DISTINCT ON (sm.id) sm.id AS message,
    sm.content AS system_said,
    um.content AS user_said
FROM system_messages sm
JOIN user_messages um 
   ON um.conversation_id = sm.conversation_id 
  AND um.created_at > sm.created_at
  AND sm.conversation_id = 1
ORDER BY sm.id,um.created_at;

Вы также можете добавить покрывающие индексы, чтобы ускорить процесс сканирования только индексов, что сокращает время выполнения, как показано ниже 1ms:

create index on user_messages(conversation_id,created_at)
   include(content,id);
create index on system_messages(conversation_id,created_at)
   include(content,id);

При их наличии похоже, что ваша первоначальная идея выигрывает с точки зрения производительности, независимо от того, опускаете ли вы коррелированный скалярный подзапрос вниз для перекрестного соединения, как латеральный подзапрос , выполняющий то же самое, или нет: demo2 at дб<>рабочий пример

SELECT sm.content AS system_said,
       um.content AS user_said
FROM system_messages sm
CROSS JOIN LATERAL (
    SELECT content
    FROM   user_messages um
    WHERE um.conversation_id = sm.conversation_id 
      AND um.created_at > sm.created_at
    ORDER BY um.created_at
    LIMIT 1 ) um
WHERE sm.conversation_id = 999
ORDER BY sm.created_at;