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

Рейтинг: 0Ответов: 1Опубликовано: 02.03.2023

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

задача - вывести комнаты, в которых пользователь с 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;

explain выдал

[
  {
    "Plan": {
      "Node Type": "Sort",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 71.06,
      "Total Cost": 71.06,
      "Plan Rows": 1,
      "Plan Width": 1604,
      "Sort Key": ["r.id"],
      "Plans": [
        {
          "Node Type": "Nested Loop",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Join Type": "Left",
          "Startup Cost": 54.36,
          "Total Cost": 71.05,
          "Plan Rows": 1,
          "Plan Width": 1604,
          "Inner Unique": false,
          "Join Filter": "(rm.id = rmf.room_message_id)",
          "Plans": [
            {
              "Node Type": "Nested Loop",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Join Type": "Inner",
              "Startup Cost": 54.36,
              "Total Cost": 59.47,
              "Plan Rows": 1,
              "Plan Width": 572,
              "Inner Unique": true,
              "Join Filter": "(rp.room_id = r.id)",
              "Plans": [
                {
                  "Node Type": "Nested Loop",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Join Type": "Inner",
                  "Startup Cost": 54.21,
                  "Total Cost": 59.24,
                  "Plan Rows": 1,
                  "Plan Width": 56,
                  "Inner Unique": true,
                  "Join Filter": "(rp.room_id = rm.room_id)",
                  "Plans": [
                    {
                      "Node Type": "Hash Join",
                      "Parent Relationship": "Outer",
                      "Parallel Aware": false,
                      "Async Capable": false,
                      "Join Type": "Inner",
                      "Startup Cost": 54.06,
                      "Total Cost": 58.82,
                      "Plan Rows": 1,
                      "Plan Width": 12,
                      "Inner Unique": false,
                      "Hash Cond": "(room_messages.room_id = rp.room_id)",
                      "Plans": [
                        {
                          "Node Type": "Aggregate",
                          "Strategy": "Hashed",
                          "Partial Mode": "Simple",
                          "Parent Relationship": "Outer",
                          "Parallel Aware": false,
                          "Async Capable": false,
                          "Startup Cost": 23.65,
                          "Total Cost": 25.65,
                          "Plan Rows": 200,
                          "Plan Width": 8,
                          "Group Key": ["room_messages.room_id"],
                          "Planned Partitions": 0,
                          "Plans": [
                            {
                              "Node Type": "Seq Scan",
                              "Parent Relationship": "Outer",
                              "Parallel Aware": false,
                              "Async Capable": false,
                              "Relation Name": "room_messages",
                              "Alias": "room_messages",
                              "Startup Cost": 0.00,
                              "Total Cost": 19.10,
                              "Plan Rows": 910,
                              "Plan Width": 8
                            }
                          ]
                        },
                        {
                          "Node Type": "Hash",
                          "Parent Relationship": "Inner",
                          "Parallel Aware": false,
                          "Async Capable": false,
                          "Startup Cost": 30.40,
                          "Total Cost": 30.40,
                          "Plan Rows": 1,
                          "Plan Width": 4,
                          "Plans": [
                            {
                              "Node Type": "Seq Scan",
                              "Parent Relationship": "Outer",
                              "Parallel Aware": false,
                              "Async Capable": false,
                              "Relation Name": "room_participants",
                              "Alias": "rp",
                              "Startup Cost": 0.00,
                              "Total Cost": 30.40,
                              "Plan Rows": 1,
                              "Plan Width": 4,
                              "Filter": "((\"participantType\" = 'USER'::\"RoomParticipanType\") AND (participant_id = 1))"
                            }
                          ]
                        }
                      ]
                    },
                    {
                      "Node Type": "Index Scan",
                      "Parent Relationship": "Inner",
                      "Parallel Aware": false,
                      "Async Capable": false,
                      "Scan Direction": "Forward",
                      "Index Name": "PK_ROOM_MESSAGES",
                      "Relation Name": "room_messages",
                      "Alias": "rm",
                      "Startup Cost": 0.15,
                      "Total Cost": 0.41,
                      "Plan Rows": 1,
                      "Plan Width": 48,
                      "Index Cond": "(id = (max(room_messages.id)))",
                      "Filter": "(room_messages.room_id = room_id)"
                    }
                  ]
                },
                {
                  "Node Type": "Index Scan",
                  "Parent Relationship": "Inner",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Scan Direction": "Forward",
                  "Index Name": "PK_ROOMS",
                  "Relation Name": "rooms",
                  "Alias": "r",
                  "Startup Cost": 0.14,
                  "Total Cost": 0.22,
                  "Plan Rows": 1,
                  "Plan Width": 524,
                  "Index Cond": "(id = rm.room_id)"
                }
              ]
            },
            {
              "Node Type": "Seq Scan",
              "Parent Relationship": "Inner",
              "Parallel Aware": false,
              "Async Capable": false,
              "Relation Name": "room_message_files",
              "Alias": "rmf",
              "Startup Cost": 0.00,
              "Total Cost": 10.70,
              "Plan Rows": 70,
              "Plan Width": 1040
            }
          ]
        }
      ]
    }
  }
]

Ответы

▲ 0

Было бы неплохо увидеть структуру таблиц.

А так на вскидку - условие для выборки комнат наверное лучше сделать как

WHERE r.id IN (
  SELECT room_id 
  FROM room_participants 
  WHERE participantType = 'USER' and rp.participant_id = 1
)

И соответственно убрать JOIN для room_participants

И второй момент, попробовать сделать выборку сообщений и файлов как подзапросы и убрать JOIN ы

Т.е. rm.text заменяется на

(SELECT text 
 FROM room_messages AS rm 
 WHERE rm.room_id = r.id 
 ORDER BY id DESC 
 LIMIT 1
) AS text

Еще как вариант можно сделать view в котором сразу собирать данные по комнате, по последним сообщениям и файлам. А потом уже из этого view делать выборку комнат для конкретного пользователя. Возможно будет и проще и быстрей. И выглядеть в коде будет красиво.

Но все это теоретические выкладки и мысли. Лучше перед применением проверить по EXPLAIN.

Да и первоначальный запрос можно по EXPLAIN првоерить (если еще этого не делали). Возможно что каких-то индексов не хватает.