Slow Query

Hi all,

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.

I've linked a document compiling the information as per the Postgresql
recommendation for Slow Query Questions. Here's the link:
https://docs.google.com/document/d/10qO5jkQNVtKw2Af1gcKAKiNw7tYFNQruzOQrUYXd4hk/edit?usp=sharing
(we've enabled commenting)

Here's a high-level summary of the issue:
______

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!

______

We'd really appreciate any help and advice!

Best,
Parth