Как можно оптимизировать этот sql запрос?

Question

[Maksim Drazdou]

Как можно оптимизировать этот запрос?

Задача - вывести комнаты, в которых пользователь с id = 1 участвует и вывести последнее сообщение либо файл.

SELECT r.id
      ,r.type
      ,r.name
      ,rm.text
      ,rmf.file
FROM rooms r
LEFT JOIN room_participants rp
    on r.id = rp.room_id
INNER JOIN
(
      SELECT room_id, MAX(id) AS max_id
      FROM room_messages
      GROUP BY room_id
) rm_max
    ON r.id = rm_max.room_id
INNER JOIN room_messages rm
    ON r.id = rm.room_id AND rm_max.max_id = rm.id
LEFT JOIN room_message_files rmf
    ON rm.id = rmf.room_message_id
where rp."participantType" = 'USER' and rp.participant_id = 1
ORDER BY rm.room_id;

Comments

[Дмитрий]

Под оптимизацией что подразумается? Скорость работы?

[Yuriy Vorobyov]

EXPLAIN/EXPLAIN ANALYZE в помощь

[Михаил]

Почитайте про ROW_NUMBER, с его использованием вероятно получится более быстрый запрос

[Сергей c0re]

а какой смысл в

LEFT JOIN room_participants rp
on r.id = rp.room_id

когда ниже

where rp."participantType" = 'USER' and rp.participant_id = 1

??

INNER JOIN
(
      SELECT room_id, MAX(id) AS max_id
      FROM room_messages
      GROUP BY room_id
) rm_max
    ON r.id = rm_max.room_id
INNER JOIN room_messages rm
    ON r.id = rm.room_id AND rm_max.max_id = rm.id

надо заменить на использование оконных функций

PS:

и вывести последнее сообщение

чье последнее сообщение? последнее сообщение в комнате вообще? или последнее сообщение этого пользователя в комнате (разные вещи так то)?
и если последнее то где связь сообщений с пользователем?

сообщение либо файл

так сообщение или файл?
или все-таки - вывести файл прикрепленный к сообщению, если он есть?
(разные вещи так то)

[Сергей c0re]

и было бы неплохо если бы хоть небольшой кусок данных закинули на dbfiddle, например.

[Maksim Drazdou]

Сергей c0re, вывести последнее сообщение в комнате и если есть файл к этому сообщению

[Сергей c0re]

Maksim Drazdou, ответ Айнур Бирдин не подходит?

[Айнур Бирдин]

Сергей c0re, Один бог знает что ответ получили а отметить что решение не судьба

Answer 1

[Айнур Бирдин]

SELECT r.id
      ,r.type
      ,r.name
      ,rm.text
      ,rmf.file
FROM rooms r
INNER JOIN (
      SELECT room_id, MAX(id) AS max_id
      FROM room_messages
      GROUP BY room_id
) rm_max ON r.id = rm_max.room_id
INNER JOIN room_messages rm
      ON r.id = rm.room_id AND rm_max.max_id = rm.id
LEFT JOIN room_message_files rmf
      ON rm.id = rmf.room_message_id
WHERE EXISTS (
      SELECT 1
      FROM room_participants rp
      WHERE rp."participantType" = 'USER' 
            AND rp.participant_id = 1
            AND rp.room_id = r.id
)
ORDER BY rm.room_id;