just hitting the first page, limit 20, using lemmy-ui
http://localhost:1234/c/zy_photography
default sort of “Active”
taking over 26 full seconds, each refresh.
463K posts in community, 6.18M posts in database
12K communities
block lists should be entirely empty
this is while logged-in

2023-08-16 12:46:44.049 MST [1520415] lemmy@lemmy_alpha LOG: duration: 26064.185 ms execute s22174:

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” = $2)))
LEFT OUTER JOIN “post_saved” ON ((“post_aggregates”.“post_id” = “post_saved”.“post_id”) AND (“post_saved”.“person_id” = $3)))
LEFT OUTER JOIN “post_read” ON ((“post_aggregates”.“post_id” = “post_read”.“post_id”) AND (“post_read”.“person_id” = $4)))
LEFT OUTER JOIN “person_block” ON ((“post_aggregates”.“creator_id” = “person_block”.“target_id”) AND (“person_block”.“person_id” = $5)))
LEFT OUTER JOIN “post_like” ON ((“post_aggregates”.“post_id” = “post_like”.“post_id”) AND (“post_like”.“person_id” = $6)))
LEFT OUTER JOIN “person_post_aggregates” ON ((“post_aggregates”.“post_id” = “person_post_aggregates”.“post_id”) AND (“person_post_aggregates”.“person_id” = $7)))
LEFT OUTER JOIN “community_block” ON ((“post_aggregates”.“community_id” = “community_block”.“community_id”) AND (“community_block”.“person_id” = $8)))
LEFT OUTER JOIN “local_user_language” ON ((“post”.“language_id” = “local_user_language”.“language_id”) AND (“local_user_language”.“local_user_id” = $9)))

WHERE (((((((((“community”.“removed” = $10) AND (“post”.“removed” = $11)) AND (“post_aggregates”.“community_id” = $12)) AND ((“community”.“hidden” = $13) OR (“community_follower”.“person_id” = $14))) AND (“post”.“nsfw” = $15)) AND (“community”.“nsfw” = $16)) 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_community” DESC , “post_aggregates”.“hot_rank_active” DESC , “post_aggregates”.“published” DESC LIMIT $17 OFFSET $18

  • RoundSparrowOPM
    link
    fedilink
    arrow-up
    1
    ·
    1 year ago

    So perhaps I’ve been over-focused on the quantity of data and back in May 2023 when I started using Lemmy daily, I should have considered that this kind of SQL query could be heavily influenced by statistics update as the table runs background analyze…

    There may be 15 minutes where the stats say there with be 400 rows for a LIMIT 10 page listing… and then the stats get updated and it then projects 50 rows instead of 400. And that’s why the servers go into periods of crashing for 15 minutes and then it clears up.

    All the JOIN logic just invites the query planner to go into some wild technique that it otherwise might not do based on quantity of data in the tables or even just plain wrong estimates in the first place… which get revised.