Home | Notifications | New Note | Local | Federated | Search | Logout

Note Detail

Reply to @silverpill@mitra.social
Harblinger@harblinger@wizard.casa (2026-07-27 14:57:13)
Okay, this is above my pay grade (I'm an SQL noob), and I hope it's not annoying slop, let me know if you want me to run any further tests:

  Numbers, from a copy of a production instance: 59,720 conversations, 256,613 posts, post is 562 MB against 128 MB shared_buffers, so it doesn't fit in cache. Postgres 18.3. Public-catalog variant, 40 rows,
  warm, EXPLAIN (ANALYZE, BUFFERS).

  Ordering by a lateral max, no stored column:

  page 0        1621 ms   356,442 buffers
  offset 5000   1784 ms   349,889 buffers

  Stored conversation.last_activity_id with a btree index:

  page 0        0.388 ms  183 buffers
  offset 5000   1.183 ms  188 buffers

  Both return the same 40 conversations in the same order (EXCEPT between them is 0 rows).

  The flat cost across page depth in the first pair comes from where the cursor lands in the plan:

  ->  Subquery Scan on last_post
        Filter: (last_post.id < '019fa02a-...'::uuid)
        ->  Limit
              ->  Sort  (Sort Key: p.id DESC)
                    ->  Index Scan using post_conversation_id_btree on post p
                          Index Searches: 58835

  The cursor is a filter on the lateral's output rather than an index condition, so all 58,835 candidate conversations are probed on every page. With the stored column it's Index Cond: (last_activity_id < ...):
  one btree descent, and constant with depth.

  One structural difference between the two queries: get_direct_conversations filters conversation.audience IS NULL, which on this instance is 467 of 59,720 rows, applied before the sort. The public variant has
  no equivalent — its candidate set is every conversation with a public root, 58,804 here — so that predicate doesn't carry over to the sibling.

  A third shape worth recording: drive from the post index instead of from conversation, scanning post by id descending and deduping on conversation_id. 26.6 ms, 2,187 buffers, page 0. It's window-approximate —
  2,000 posts yielded 1,477 distinct conversatio
---Reply---
silverpill@silverpill@mitra.social (2026-07-28 05:31:19)
@harblinger


True, I'm going to re-skin AP 'repost' as 'bump' eventually.  Good AP primitive to fix the no-signal bump posts common on image boards (picrel).


That's an interesting idea.

Maybe reposts should be tied to conversations, but let's keep this focused on comment-bumps for now.


Just implemented mitra subs recently too :)


🔥


Filtering private comments is to prevent users not in the convo being confused by what appears to be no activity bumped threads.


You can pass the current user to the query... But no need to make it more complicated than necessary.


I hope it's not annoying slop, let me know if you want me to run any further tests


Stored conversation.last_activity_id with a btree index



Premature optimization. It's never too late to add extra column.


Endpoint prefix. /api/v1/conversations is fine. The only wrinkle is that the bare path is Mastodon's DM conversation list, which Mitra already serves, so the public one likely wants to be a sibling path — /api/v1/conversations/public or similar.


Yes, we should use /api/v1/conversations/public


slop slop slop


Do you intend to send a patch?

---Replies---

Harblinger@harblinger@wizard.casa (2026-07-28 06:45:40)
@silverpill
Sounds good!

>Do you intend to send a patch?
I could definitely do that and exercise your new forgejo instance (unless you want me to press the new 'berg ToS, either way)