• BitOneZero@beehaw.org
    link
    fedilink
    arrow-up
    4
    ·
    edit-2
    1 year ago

    Just because it’s written in Rust doesn’t make your app safe, or performant.

    Lemmy 0.18.4 listing posts, frequently via ORM Diesel:

                SELECT "post"."id", "post"."name", "post"."url", "post"."body", "post"."creator_id", "post"."community_id", "post"."removed",
                  "post"."locked", "post"."published", "post"."updated", "post"."deleted", "post"."nsfw", "post"."embed_title", "post"."embed_description",
                  "post"."thumbnail_url", "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community",
                  "post"."featured_local",
                  "person"."id", "person"."name", "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated",
                  "person"."actor_id", "person"."bio", "person"."local", "person"."private_key", "person"."public_key", "person"."last_refreshed_at",
                  "person"."banner", "person"."deleted", "person"."inbox_url", "person"."shared_inbox_url", "person"."matrix_user_id",
                  "person"."admin",
                  "person"."bot_account", "person"."ban_expires", "person"."instance_id",
                  "community"."id", "community"."name", "community"."title", "community"."description", "community"."removed", "community"."published",
                  "community"."updated", "community"."deleted", "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key",
                  "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner", "community"."followers_url",
                  "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods",
                  "community"."instance_id", "community"."moderators_url", "community"."featured_url",
                  ("community_person_ban"."id" IS NOT NULL),
                  "post_aggregates"."id", "post_aggregates"."post_id", "post_aggregates"."comments", "post_aggregates"."score", "post_aggregates"."upvotes",
                  "post_aggregates"."downvotes", "post_aggregates"."published", "post_aggregates"."newest_comment_time_necro",
                  "post_aggregates"."newest_comment_time", "post_aggregates"."featured_community", "post_aggregates"."featured_local",
                  "post_aggregates"."hot_rank", "post_aggregates"."hot_rank_active", "post_aggregates"."community_id", "post_aggregates"."creator_id",
                  "post_aggregates"."controversy_rank", "community_follower"."pending",
                  ("post_saved"."id" IS NOT NULL),
                  ("post_read"."id" IS NOT NULL),
                  ("person_block"."id" IS NOT NULL),
                  "post_like"."score",
                  coalesce(("post_aggregates"."comments" - "person_post_aggregates"."read_comments"),
                  "post_aggregates"."comments")
                 
                  FROM ((((((((((((
                    ("post_aggregates"
                       INNER JOIN "person" ON ("post_aggregates"."creator_id" = "person"."id"))
                    INNER JOIN "community" ON ("post_aggregates"."community_id" = "community"."id"))
                    LEFT OUTER JOIN "community_person_ban" ON (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))
                    )
                    INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")
                    )
                    LEFT OUTER JOIN "community_follower" ON (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $1))
                    )
                    LEFT OUTER JOIN "community_moderator" ON (("post"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = $1))
                    )
                    LEFT OUTER JOIN "post_saved" ON (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = $1))
                    )
                    LEFT OUTER JOIN "post_read" ON (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = $1))
                    )
                    LEFT OUTER JOIN "person_block" ON (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $1))
                    )
                    LEFT OUTER JOIN "post_like" ON (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = $1))
                    )
                    LEFT OUTER JOIN "person_post_aggregates" ON (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = $1))
                    )
                    LEFT OUTER JOIN "community_block" ON (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = $1))
                    )
                    LEFT OUTER JOIN "local_user_language" ON (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = $2))
                    )
                    
                    WHERE
                    (((
                        (((
                        (
                        ("community"."removed" = $3) AND ("post"."removed" = $4))
                        AND ("community_follower"."pending" IS NOT NULL)
                        )
                        AND ("post"."nsfw" = $5)
                        )
                        AND ("community"."nsfw" = $6)
                        )
                        AND ("local_user_language"."language_id" IS NOT NULL)
                        )
                        AND ("community_block"."person_id" IS NULL)
                        )
                        AND ("person_block"."person_id" IS NULL)
                        )
                        
                    ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."hot_rank_active" DESC , "post_aggregates"."published" DESC
                        
                    LIMIT $7
                    OFFSET $8
    			;`
    
    

    That is with hand-optimized person_id = $1, which the Rust code does not do.

    • CanadaPlus@lemmy.sdf.org
      link
      fedilink
      arrow-up
      7
      ·
      1 year ago

      See, I noticed this stuff reading through the Lemmy source code, but I assumed the authors just were on another level of database use than me. Is this actually just a mess? How exactly is it bad, beyond being opaque?

    • anlumo@feddit.de
      link
      fedilink
      arrow-up
      1
      ·
      1 year ago

      This doesn’t look like anything out of the ordinary in a real-world application to me. We have way more complex queries in our service, even though ours are hand crafted.

      One thing we did notice though is that sometimes, it’s faster to just query the whole dataset and do the complex filtering in Rust. As soon as you hit the seq scan heuristic in PostgreSQL, there’s nothing to be gained from doing it in SQL.

      • BitOneZero@beehaw.org
        link
        fedilink
        arrow-up
        1
        ·
        1 year ago

        We have way more complex queries

        It isn’t the complexity that is the problem. It is the open-ended nature. It lacks any WHERE clause that specifies which posts to get. It just kicks off join after join without restricting what it is looking for. It relies on the “LIMIT 50” that Lemmy restricts post listings too. Which worked OK in March 2023 when Lemmy was over 4 years old and still had very tiny amounts of data in all these tables that it joins, but once even a modest amount of data got point in the open-ended nature of the WHERE clause kept making it slower and slower as more and more content.