I sail the high seas of the Lemmyverse, posting snarky + Lefty comments

  • 0 Posts
  • 15 Comments
Joined 2 years ago
Cake day: May 7th, 2023
  • subversive_dev@lemmy.mltoAnnouncements@lemmy.ml•Lemmy AMA March 2025English
    4·
    7 days ago

    Good evening Dessalines, I have started looking at the posts query.

    The lowest hanging fruit I think would be if we could replace some of the joins with WHERE EXISTS which can have a huge impact on the query time. It seems this is supported in Diesel: https://stackoverflow.com/a/74300447

    This is my first time looking at the codebase so I can’t tell yet which joins are purely for filtering (in which case they can be replaced by WHERE EXISTS) and which joins need to be left in because some of their columns end up in the final SELECT

    I can’t tell for sure yet but it also looks like this might also be using LIMIT...OFFSET pagination? That can be a real drag on performance but isn’t as easy to fix.

    EDIT:

    Looking some more, and reading some linked github discussion - I think to really get this out of the performance pits will require some denormalization like a materialized view or manual cache tables populated by triggers. I really like the ranking algorithm but so far I’m finding it difficult to optimize from a query perspective