We've been struggling with a slow query! -- and it's been exploding as rows have been added to relevant tables. It seems like a fairly common workflow, so we think we're overlooking the potential for an index or rewriting the query.
We’re trying to show a list of active conversations. Each conversation (named a spool in the database) has multiple threads, kind of like Slack channels. And the messages are stored in each thread. We want to return the 30 most recent conversations with recency determined as the most recent message in any thread of the conversation you are a participant of (you may not be a participant of certain threads in a conversation so it’s important those don’t leak sensitive data).
We found that as the number of threads increases, the query slowed down dramatically. We think the issue has to do with the fact that there is no easy way to go from a thread you are a participant to its most recent message, however, it is possible the issue is elsewhere. We’ve provided the full query and a simplified query of where we think the issue is, along with the EXPLAIN ANALYZE BUFFERS result. We figure this is not exactly an uncommon use case, so it’s likely that we are overlooking the potential for some missing indices or a better way to write the query. We appreciate the help and any advice!