Based on the execution plan, it looks like the part that takes 13 seconds of the total 14.4 seconds is just calculating the max time used in the where clause. Anytime I see an OR involved in a plan gone off the rails, I always always check if re-writing the query some other way may be faster. How's the plan for something like this?
WHERE message.time = greatest( sub1.time, sub2.time )
/* sub1.time */
(
select
MAX ( message2.time )
FROM
message AS message2
JOIN thread AS thread2 ON thread2.id = message2.thread
JOIN participant ON participant.thread = thread2.id
WHERE
NOT message2.draft
AND participant.identity = 'b16690e4-a3c5-4868-945e-c2458c27a525'
AND thread2.spool = spool.id
)
MAX ( message2.time )
FROM
message AS message2
JOIN thread AS thread2 ON thread2.id = message2.thread
JOIN participant ON participant.thread = thread2.id
WHERE
NOT message2.draft
AND participant.identity = 'b16690e4-a3c5-4868-945e-c2458c27a525'
AND thread2.spool = spool.id
)
/* sub2.time */
(
select
MAX ( message2.time )
FROM
message AS message2
JOIN thread AS thread2 ON thread2.id = message2.thread
JOIN participant ON participant.thread = thread2.id
JOIN relation ON relation.to = participant.identity
AND relation.from = 'b16690e4-a3c5-4868-945e-c2458c27a525'
AND relation.manages = TRUE
WHERE
NOT message2.draft
AND thread2.spool = spool.id
select
MAX ( message2.time )
FROM
message AS message2
JOIN thread AS thread2 ON thread2.id = message2.thread
JOIN participant ON participant.thread = thread2.id
JOIN relation ON relation.to = participant.identity
AND relation.from = 'b16690e4-a3c5-4868-945e-c2458c27a525'
AND relation.manages = TRUE
WHERE
NOT message2.draft
AND thread2.spool = spool.id
)
