Is there no index on thread.spool? What about notification.user? How about message.time (without thread as a leading column). Those would all seem very significant. Your row counts are very low to have a query perform so badly. Work_mem could probably be increased above 4MB, but it isn't hurting this query in particular.
My primary concern is that the query is rather chaotic at a glance. It would be great to re-write and remove the unneeded keywords, double quotes, totally worthless parentheses, etc. Something like the below may help you see the crux of the query and what could be done and understand how many rows might be coming out of those subqueries. I re-ordered some joins and there might be syntax errors, but give it a shot once you've added the indexes suggested above. SELECT spool.id, handle.handle, spool.name, thread.id, case.closed, notification.read, notification2.time, message.message, message.time, message.author, thread.name, location.geo FROM spool JOIN handle ON handle.id = spool.id JOIN thread ON thread.spool = spool.id JOIN message ON message.thread = thread.id LEFT JOIN location ON location.id = spool.location LEFT JOIN case ON case.id = spool.id LEFT JOIN notification ON notification.user = 'b16690e4-a3c5-4868-945e-c2458c27a525' AND notification.id = ( SELECT notification3.id FROM notification AS notification3 JOIN notification_thread ON notification_thread.id = notification3.id JOIN thread AS thread2 ON thread2.id = notification_thread.thread WHERE thread2.spool = spool.id AND notification3.user = 'b16690e4-a3c5-4868-945e-c2458c27a525' AND notification3.time <= '2020-09-30 16:32:38.054558' ORDER BY notification3.time DESC LIMIT 1 ) LEFT JOIN notification AS notification2 ON notification2.user = 'b16690e4-a3c5-4868-945e-c2458c27a525' AND notification2.id = ( SELECT notification3.id FROM notification AS notification3 JOIN notification_thread ON notification_thread.id = notification3.id JOIN thread AS thread2 ON thread2.id = notification_thread.thread WHERE thread2.spool = spool.id AND notification3.user = 'b16690e4-a3c5-4868-945e-c2458c27a525' AND notification3.time > '2020-09-30 16:32:38.054558' ORDER BY notification3.time DESC LIMIT 1 ) WHERE message.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 identity ON identity.id = participant.identity LEFT JOIN relation ON relation.to = identity.id AND relation.from = 'b16690e4-a3c5-4868-945e-c2458c27a525' AND relation.manages = TRUE WHERE NOT message2.draft AND ( identity.id = 'b16690e4-a3c5-4868-945e-c2458c27a525' OR NOT relation.to IS NULL ) AND thread2.spool = spool.id LIMIT 1 ) AND notification.id IS NOT NULL ORDER BY message.time DESC LIMIT 31;
