Как можно оптимизировать этот sql запрос?
как можно оптимизировать этот запрос
задача - вывести комнаты, в которых пользователь с 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
}
]
}
]
}
}
]