[Bug]: pagination on Local & All is dreadfully slow
ticoombs opened this issue · 14 comments
ticoombs commented
Requirements
- Is this a bug report? For questions or discussions use https://lemmy.ml/c/lemmy_support
- Did you check to see if this issue already exists?
- Is this only a single bug? Do not put multiple bugs in one issue.
- Do you agree to follow the rules in our Code of Conduct?
- Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.
Summary
Since updating clicking next results in HUGE pageload response times.
This happens via lemmy-ui (same version), and jerboa 0.0.66 on android 14.
Interestingly enough this is not an issue with using Eternity on Android (I physically cannot scroll fast enough, and it loads every post without issue )
Steps to Reproduce
- Update lemmy & lemmy-ui to 0.19.4-beta.3
- View /
- Change to "Local" (or "All")
- Click "Next"
Technical Details
24s request in db:
postgres-1 | 2024-04-11 22:45:26.917 GMT [284493] LOG: duration: 24398.193 ms plan:
postgres-1 | Query Text:
postgres-1 | -- PostQuery::list
postgres-1 | SELECT * FROM (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", "post"."url_content_type", "post"."alt_text", "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"."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"."visibility", EXISTS (SELECT "community_person_ban"."community_id", "community_person_ban"."person_id", "community_person_ban"."published", "community_person_ban"."expires" FROM "community_person_ban" WHERE (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))), $1, EXISTS (SELECT "community_moderator"."community_id", "community_moderator"."person_id", "community_moderator"."published" FROM "community_moderator" WHERE (("post_aggregates"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = "post_aggregates"."creator_id"))), EXISTS (SELECT "local_user"."id", "local_user"."person_id", "local_user"."password_encrypted", "local_user"."email", "local_user"."show_nsfw", "local_user"."theme", "local_user"."default_sort_type", "local_user"."default_listing_type", "local_user"."interface_language", "local_user"."show_avatars", "local_user"."send_notifications_to_email", "local_user"."show_scores", "local_user"."show_bot_accounts", "local_user"."show_read_posts", "local_user"."email_verified", "local_user"."accepted_application", "local_user"."totp_2fa_secret", "local_user"."open_links_in_new_tab", "local_user"."blur_nsfw", "local_user"."auto_expand", "local_user"."infinite_scroll_enabled", "local_user"."admin", "local_user"."post_listing_mode", "local_user"."totp_2fa_enabled", "local_user"."enable_keyboard_navigation", "local_user"."enable_animated_images", "local_user"."collapse_bot_comments" FROM "local_user" WHERE (("post_aggregates"."creator_id" = "local_user"."person_id") AND ("local_user"."admin" = $2))), "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", "post_aggregates"."instance_id", "post_aggregates"."scaled_rank", $3, ($4 IS NOT NULL), $5, $6, $7, $8, coalesce(("post_aggregates"."comments" - $9), "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")) INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")) WHERE ((((((((("community"."deleted" = $10) AND ("post"."deleted" = $11)) AND ("community"."removed" = $12)) AND ("post"."removed" = $13)) AND ("community"."hidden" = $14)) AND ("post"."nsfw" = $15)) AND ("community"."nsfw" = $16)) AND ("community"."visibility" = $17)) AND ($18, $19, $20) > ("post_aggregates"."featured_local", "post_aggregates"."published", "post_aggregates"."post_id")) ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC , "post_aggregates"."post_id" DESC LIMIT $21 OFFSET $22) dullbananas_i_love_jesus_subquery
postgres-1 | Query Parameters: $1 = 'f', $2 = 't', $3 = NULL, $4 = NULL, $5 = 'f', $6 = 'f', $7 = 'f', $8 = NULL, $9 = NULL, $10 = 'f', $11 = 'f', $12 = 'f', $13 = 'f', $14 = 'f', $15 = 'f', $16 = 'f', $17 = 'Public', $18 = 'f', $19 = '2024-04-11 21:56:58.95235+00', $20 = '17133669', $21 = '25', $22 = '0'
postgres-1 | Limit (cost=55311.16..55318.68 rows=1 width=3163)
postgres-1 | -> Result (cost=55311.16..55318.68 rows=1 width=3163)
postgres-1 | -> Sort (cost=55311.16..55311.17 rows=1 width=3160)
postgres-1 | Sort Key: post_aggregates.featured_local DESC, post_aggregates.published DESC, post.id DESC
postgres-1 | -> Nested Loop (cost=1001.14..55311.15 rows=1 width=3160)
postgres-1 | -> Nested Loop (cost=1000.71..55308.50 rows=1 width=2186)
postgres-1 | -> Nested Loop (cost=1000.42..55306.00 rows=1 width=1003)
postgres-1 | -> Gather (cost=1000.00..55303.36 rows=1 width=106)
postgres-1 | Workers Planned: 4
postgres-1 | -> Parallel Seq Scan on post_aggregates (cost=0.00..54303.26 rows=1 width=106)
postgres-1 | Filter: (ROW(false, '2024-04-11 21:56:58.95235+00'::timestamp with time zone, 17133669) > ROW(featured_local, published, post_id))
postgres-1 | -> Index Scan using person__pkey on person (cost=0.42..2.64 rows=1 width=897)
postgres-1 | Index Cond: (id = post_aggregates.creator_id)
postgres-1 | -> Index Scan using community_pkey on community (cost=0.29..2.51 rows=1 width=1183)
postgres-1 | Index Cond: (id = post_aggregates.community_id)
postgres-1 | Filter: ((NOT deleted) AND (NOT removed) AND (NOT hidden) AND (NOT nsfw) AND (visibility = 'Public'::community_visibility))
postgres-1 | -> Index Scan using post_pkey on post (cost=0.43..2.65 rows=1 width=958)
postgres-1 | Index Cond: (id = post_aggregates.post_id)
postgres-1 | Filter: ((NOT deleted) AND (NOT removed) AND (NOT nsfw))postgres-1 | SubPlan 1
postgres-1 | -> Index Only Scan using community_person_ban_pkey on community_person_ban (cost=0.28..2.50 rows=1 width=0)
postgres-1 | Index Cond: ((person_id = post_aggregates.creator_id) AND (community_id = post_aggregates.community_id))
postgres-1 | SubPlan 3
postgres-1 | -> Index Scan using idx_community_moderator_community on community_moderator (cost=0.29..2.51 rows=1 width=0)
postgres-1 | Index Cond: (community_id = post_aggregates.community_id)
postgres-1 | Filter: (person_id = post_aggregates.creator_id)
postgres-1 | SubPlan 5
postgres-1 | -> Index Scan using local_user_person_id_key on local_user (cost=0.28..2.50 rows=1 width=0)
postgres-1 | Index Cond: (person_id = post_aggregates.creator_id)
postgres-1 | Filter: admin
Version
0.19.4-beta.3
Lemmy Instance URL
No response
Nutomic commented
dullbananas commented
Possibly missing index for this filter:
-> Parallel Seq Scan on post_aggregates (cost=0.00..54303.26 rows=1 width=106)
Filter: (ROW(false, '2024-04-11 21:56:58.95235+00'::timestamp with time zone, 17133669) > ROW(featured_local, published, post_id))
Die4Ever commented
Strange that it works fine on Eternity but not other frontends? Can you share an example URL that loads slowly?
dullbananas commented
@ticoombs Run this command in psql, and post the output: \d idx_post_aggregates_featured_local_published
If it's not a problem with your database, then maybe we should try adding the post_id column to the indexes.
ticoombs commented
lemmy=# \d idx_post_aggregates_featured_local_published;
Index "public.idx_post_aggregates_featured_local_published"
Column | Type | Key? | Definition
----------------+--------------------------+------+----------------
featured_local | boolean | yes | featured_local
published | timestamp with time zone | yes | published
btree, for table "public.post_aggregates"
I don't believe there is anything wrong with the db. We have lots of headroom available and near zero iowait/iosteal.
@Die4Ever
In testing for the issue in getting a link for you (I run reddthat.com), it seems it is only an issue for Logged In Users.
Logged Out:
- Start: https://reddthat.com/?dataType=Post&listingType=Local&sort=Active
- Click Next
- Instant next 25
Logged In:
- Start: https://reddthat.com/?dataType=Post&listingType=Local&sort=Active
- Click Next
- 25 to 30 seconds load time
( A direct link to the problem while logged in would be: https://reddthat.com/?dataType=Post&listingType=Local&pageCursor=P10495a6&sort=New)
ticoombs commented
Since Updating to -beta.3: (replaced the User ID with xx to ensure no leaking
2024-04-15 07:30:18.544 GMT [753] LOG: duration: 67757.215 ms execute s8109:
-- PostQuery::list
SELECT * FROM (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", "post"."url_content_type", "post"."alt_text", "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"."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"."visibility", EXISTS (SELECT "community_person_ban"."community_id", "community_person_ban"."person_id", "community_person_ban"."published", "community_person_ban"."expires" FROM "community_person_ban" WHERE (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))), EXISTS (SELECT "community_person_ban"."community_id", "community_person_ban"."person_id", "community_person_ban"."published", "community_person_ban"."expires" FROM "community_person_ban" WHERE (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = $1))), EXISTS (SELECT "community_moderator"."community_id", "community_moderator"."person_id", "community_moderator"."published" FROM "community_moderator" WHERE (("post_aggregates"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = "post_aggregates"."creator_id"))), EXISTS (SELECT "local_user"."id", "local_user"."person_id", "local_user"."password_encrypted", "local_user"."email", "local_user"."show_nsfw", "local_user"."theme", "local_user"."default_sort_type", "local_user"."default_listing_type", "local_user"."interface_language", "local_user"."show_avatars", "local_user"."send_notifications_to_email", "local_user"."show_scores", "local_user"."show_bot_accounts", "local_user"."show_read_posts", "local_user"."email_verified", "local_user"."accepted_application", "local_user"."totp_2fa_secret", "local_user"."open_links_in_new_tab", "local_user"."blur_nsfw", "local_user"."auto_expand", "local_user"."infinite_scroll_enabled", "local_user"."admin", "local_user"."post_listing_mode", "local_user"."totp_2fa_enabled", "local_user"."enable_keyboard_navigation", "local_user"."enable_animated_images", "local_user"."collapse_bot_comments" FROM "local_user" WHERE (("post_aggregates"."creator_id" = "local_user"."person_id") AND ("local_user"."admin" = $2))), "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", "post_aggregates"."instance_id", "post_aggregates"."scaled_rank", (SELECT "community_follower"."pending" FROM "community_follower" WHERE (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $3)) LIMIT $4), ((SELECT "post_saved"."published" FROM "post_saved" WHERE (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = $5)) LIMIT $6) IS NOT NULL), EXISTS (SELECT "post_read"."post_id", "post_read"."person_id", "post_read"."published" FROM "post_read" WHERE (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = $7))), EXISTS (SELECT "post_hide"."post_id", "post_hide"."person_id", "post_hide"."published" FROM "post_hide" WHERE (("post_aggregates"."post_id" = "post_hide"."post_id") AND ("post_hide"."person_id" = $8))), EXISTS (SELECT "person_block"."person_id", "person_block"."target_id", "person_block"."published" FROM "person_block" WHERE (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $9))), (SELECT "post_like"."score" FROM "post_like" WHERE (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = $10)) LIMIT $11), coalesce(("post_aggregates"."comments" - (SELECT "person_post_aggregates"."read_comments" FROM "person_post_aggregates" WHERE (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = $12)) LIMIT $13)), "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")) INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")) WHERE ((((((((((("community"."deleted" = $14) AND (("post"."deleted" = $15) OR ("post"."creator_id" = $16))) AND ("community"."removed" = $17)) AND ("post"."removed" = $18)) AND (("community"."hidden" = $19) OR EXISTS (SELECT "community_follower"."community_id", "community_follower"."person_id", "community_follower"."published", "community_follower"."pending" FROM "community_follower" WHERE (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $20))))) AND NOT (EXISTS (SELECT "post_hide"."post_id", "post_hide"."person_id", "post_hide"."published" FROM "post_hide" WHERE (("post_aggregates"."post_id" = "post_hide"."post_id") AND ("post_hide"."person_id" = $21))))) AND EXISTS (SELECT "local_user_language"."local_user_id", "local_user_language"."language_id" FROM "local_user_language" WHERE (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = $22)))) AND NOT (EXISTS (SELECT "community_block"."person_id", "community_block"."community_id", "community_block"."published" FROM "community_block" WHERE (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = $23))))) AND NOT (EXISTS (SELECT "instance_block"."person_id", "instance_block"."instance_id", "instance_block"."published" FROM "instance_block" WHERE (("post_aggregates"."instance_id" = "instance_block"."instance_id") AND ("instance_block"."person_id" = $24))))) AND NOT (EXISTS (SELECT "person_block"."person_id", "person_block"."target_id", "person_block"."published" FROM "person_block" WHERE (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $25))))) AND ($26, $27, $28, $29) > ("post_aggregates"."featured_local", "post_aggregates"."hot_rank_active", "post_aggregates"."published", "post_aggregates"."post_id")) ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."hot_rank_active" DESC , "post_aggregates"."published" DESC , "post_aggregates"."post_id" DESC LIMIT $30 OFFSET $31) dullbananas_i_love_jesus_subquery
2024-04-15 07:30:18.544 GMT [753] DETAIL: parameters: $1 = 'xx', $2 = 't', $3 = 'xx', $4 = '1', $5 = 'xx', $6 = '1', $7 = 'xx', $8 = 'xx', $9 = 'xx', $10 = 'xx', $11 = '1', $12 = 'xx', $13 = '1', $14 = 'f', $15 = 'f', $16 = 'xx', $17 = 'f', $18 = 'f', $19 = 'f', $20 = 'xx', $21 = 'xx', $22 = '5299', $23 = 'xx', $24 = 'xx', $25 = 'xx', $26 = 'f', $27 = '0.5677054998208626', $28 = '2024-04-14 10:46:25.502725+00', $29 = '17261302', $30 = '10', $31 = '0'
2024-04-15 07:30:18.545 GMT [753] LOG: duration: 67757.188 ms plan:
Query Text:
-- PostQuery::list
SELECT * FROM (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", "post"."url_content_type", "post"."alt_text", "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"."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"."visibility", EXISTS (SELECT "community_person_ban"."community_id", "community_person_ban"."person_id", "community_person_ban"."published", "community_person_ban"."expires" FROM "community_person_ban" WHERE (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))), EXISTS (SELECT "community_person_ban"."community_id", "community_person_ban"."person_id", "community_person_ban"."published", "community_person_ban"."expires" FROM "community_person_ban" WHERE (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = $1))), EXISTS (SELECT "community_moderator"."community_id", "community_moderator"."person_id", "community_moderator"."published" FROM "community_moderator" WHERE (("post_aggregates"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = "post_aggregates"."creator_id"))), EXISTS (SELECT "local_user"."id", "local_user"."person_id", "local_user"."password_encrypted", "local_user"."email", "local_user"."show_nsfw", "local_user"."theme", "local_user"."default_sort_type", "local_user"."default_listing_type", "local_user"."interface_language", "local_user"."show_avatars", "local_user"."send_notifications_to_email", "local_user"."show_scores", "local_user"."show_bot_accounts", "local_user"."show_read_posts", "local_user"."email_verified", "local_user"."accepted_application", "local_user"."totp_2fa_secret", "local_user"."open_links_in_new_tab", "local_user"."blur_nsfw", "local_user"."auto_expand", "local_user"."infinite_scroll_enabled", "local_user"."admin", "local_user"."post_listing_mode", "local_user"."totp_2fa_enabled", "local_user"."enable_keyboard_navigation", "local_user"."enable_animated_images", "local_user"."collapse_bot_comments" FROM "local_user" WHERE (("post_aggregates"."creator_id" = "local_user"."person_id") AND ("local_user"."admin" = $2))), "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", "post_aggregates"."instance_id", "post_aggregates"."scaled_rank", (SELECT "community_follower"."pending" FROM "community_follower" WHERE (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $3)) LIMIT $4), ((SELECT "post_saved"."published" FROM "post_saved" WHERE (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = $5)) LIMIT $6) IS NOT NULL), EXISTS (SELECT "post_read"."post_id", "post_read"."person_id", "post_read"."published" FROM "post_read" WHERE (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = $7))), EXISTS (SELECT "post_hide"."post_id", "post_hide"."person_id", "post_hide"."published" FROM "post_hide" WHERE (("post_aggregates"."post_id" = "post_hide"."post_id") AND ("post_hide"."person_id" = $8))), EXISTS (SELECT "person_block"."person_id", "person_block"."target_id", "person_block"."published" FROM "person_block" WHERE (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $9))), (SELECT "post_like"."score" FROM "post_like" WHERE (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = $10)) LIMIT $11), coalesce(("post_aggregates"."comments" - (SELECT "person_post_aggregates"."read_comments" FROM "person_post_aggregates" WHERE (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = $12)) LIMIT $13)), "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")) INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")) WHERE ((((((((((("community"."deleted" = $14) AND (("post"."deleted" = $15) OR ("post"."creator_id" = $16))) AND ("community"."removed" = $17)) AND ("post"."removed" = $18)) AND (("community"."hidden" = $19) OR EXISTS (SELECT "community_follower"."community_id", "community_follower"."person_id", "community_follower"."published", "community_follower"."pending" FROM "community_follower" WHERE (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $20))))) AND NOT (EXISTS (SELECT "post_hide"."post_id", "post_hide"."person_id", "post_hide"."published" FROM "post_hide" WHERE (("post_aggregates"."post_id" = "post_hide"."post_id") AND ("post_hide"."person_id" = $21))))) AND EXISTS (SELECT "local_user_language"."local_user_id", "local_user_language"."language_id" FROM "local_user_language" WHERE (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = $22)))) AND NOT (EXISTS (SELECT "community_block"."person_id", "community_block"."community_id", "community_block"."published" FROM "community_block" WHERE (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = $23))))) AND NOT (EXISTS (SELECT "instance_block"."person_id", "instance_block"."instance_id", "instance_block"."published" FROM "instance_block" WHERE (("post_aggregates"."instance_id" = "instance_block"."instance_id") AND ("instance_block"."person_id" = $24))))) AND NOT (EXISTS (SELECT "person_block"."person_id", "person_block"."target_id", "person_block"."published" FROM "person_block" WHERE (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $25))))) AND ($26, $27, $28, $29) > ("post_aggregates"."featured_local", "post_aggregates"."hot_rank_active", "post_aggregates"."published", "post_aggregates"."post_id")) ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."hot_rank_active" DESC , "post_aggregates"."published" DESC , "post_aggregates"."post_id" DESC LIMIT $30 OFFSET $31) dullbananas_i_love_jesus_subquery
Query Parameters: $1 = 'XX', $2 = 't', $3 = 'xx', $4 = '1', $5 = 'xx', $6 = '1', $7 = 'xx', $8 = 'xx', $9 = 'xx', $10 = 'xx', $11 = '1', $12 = 'xx', $13 = '1', $14 = 'f', $15 = 'f', $16 = 'xx', $17 = 'f', $18 = 'f', $19 = 'f', $20 = 'xx', $21 = 'xx', $22 = '5299', $23 = 'xx', $24 = 'xx', $25 = 'xx', $26 = 'f', $27 = '0.5677054998208626', $28 = '2024-04-14 10:46:25.502725+00', $29 = '17261302', $30 = '10', $31 = '0'
Limit (cost=57014.43..57042.44 rows=1 width=3149)
-> Result (cost=57014.43..57042.44 rows=1 width=3149)
-> Sort (cost=57014.43..57014.44 rows=1 width=3130)
Sort Key: post_aggregates.featured_local DESC, post_aggregates.hot_rank_active DESC, post_aggregates.published DESC, post.id DESC
-> Nested Loop Anti Join (cost=1002.43..57014.42 rows=1 width=3130)
Join Filter: (post_aggregates.creator_id = person_block.target_id)
-> Nested Loop Anti Join (cost=1002.15..57010.79 rows=1 width=3130)
Join Filter: (post_aggregates.instance_id = instance_block.instance_id)
-> Nested Loop Anti Join (cost=1002.00..57008.42 rows=1 width=3130)
-> Nested Loop (cost=1001.71..57003.69 rows=1 width=3130)
-> Nested Loop Anti Join (cost=1001.29..57003.24 rows=1 width=3130)
-> Nested Loop (cost=1001.14..56998.65 rows=1 width=3130)
-> Nested Loop (cost=1000.71..56996.00 rows=1 width=2177)
-> Nested Loop (cost=1000.42..56990.98 rows=1 width=1000)
-> Gather (cost=1000.00..56988.33 rows=1 width=106)
Workers Planned: 4
-> Parallel Seq Scan on post_aggregates (cost=0.00..55988.24 rows=1 width=106)
Filter: (ROW(false, '0.5677054998208626'::double precision, '2024-04-14 10:46:25.502725+00'::timestamp with time zone, 17261302) > ROW(featured_local, hot_rank_active, published, post_id))
-> Index Scan using person__pkey on person (cost=0.42..2.64 rows=1 width=894)
Index Cond: (id = post_aggregates.creator_id)
-> Index Scan using community_pkey on community (cost=0.29..5.01 rows=1 width=1177)
Index Cond: (id = post_aggregates.community_id)
Filter: ((NOT deleted) AND (NOT removed) AND ((NOT hidden) OR (SubPlan 19)))
SubPlan 19
-> Index Only Scan using community_follower_pkey on community_follower community_follower_1 (cost=0.29..2.51 rows=1 width=0)
Index Cond: ((person_id = xx) AND (community_id = post_aggregates.community_id))
-> Index Scan using post_pkey on post (cost=0.43..2.65 rows=1 width=953)
Index Cond: (id = post_aggregates.post_id)
Filter: ((NOT removed) AND ((NOT deleted) OR (creator_id = xx)))
-> Index Only Scan using post_hide_pkey on post_hide (cost=0.15..2.37 rows=1 width=4)
Index Cond: ((person_id = xx) AND (post_id = post_aggregates.post_id))
-> Index Only Scan using local_user_language_pkey on local_user_language (cost=0.42..0.45 rows=1 width=4)
Index Cond: ((local_user_id = 5299) AND (language_id = post.language_id))
-> Index Only Scan using community_block_pkey on community_block (cost=0.29..2.51 rows=1 width=4)
Index Cond: ((person_id = xx) AND (community_id = post_aggregates.community_id))
-> Index Only Scan using instance_block_pkey on instance_block (cost=0.14..2.36 rows=1 width=4)
Index Cond: (person_id = xx)
-> Index Only Scan using person_block_pkey on person_block (cost=0.28..3.60 rows=2 width=4)
Index Cond: (person_id = xx)
SubPlan 1
-> Index Only Scan using community_person_ban_pkey on community_person_ban (cost=0.28..2.50 rows=1 width=0)
Index Cond: ((person_id = post_aggregates.creator_id) AND (community_id = post_aggregates.community_id))
SubPlan 3
-> Index Only Scan using community_person_ban_pkey on community_person_ban community_person_ban_1 (cost=0.28..2.50 rows=1 width=0)
Index Cond: ((person_id = xx) AND (community_id = post_aggregates.community_id))
SubPlan 5
-> Index Scan using idx_community_moderator_community on community_moderator (cost=0.29..2.51 rows=1 width=0)
Index Cond: (community_id = post_aggregates.community_id)
Filter: (person_id = post_aggregates.creator_id)
SubPlan 7
-> Index Scan using local_user_person_id_key on local_user (cost=0.28..2.50 rows=1 width=0)
Index Cond: (person_id = post_aggregates.creator_id)
Filter: admin
SubPlan 9
-> Limit (cost=0.29..2.51 rows=1 width=1)
-> Index Scan using community_follower_pkey on community_follower (cost=0.29..2.51 rows=1 width=1)
Index Cond: ((person_id = xx) AND (community_id = post_aggregates.community_id))
SubPlan 10
-> Limit (cost=0.29..2.51 rows=1 width=8)
-> Index Scan using post_saved_pkey on post_saved (cost=0.29..2.51 rows=1 width=8)
Index Cond: ((person_id = xx) AND (post_id = post_aggregates.post_id))
SubPlan 11
-> Index Only Scan using post_read_pkey on post_read (cost=0.43..2.65 rows=1 width=0)
Index Cond: ((person_id = xx) AND (post_id = post_aggregates.post_id))
SubPlan 13
-> Index Only Scan using post_hide_pkey on post_hide post_hide_1 (cost=0.15..2.37 rows=1 width=0)
Index Cond: ((person_id = xx) AND (post_id = post_aggregates.post_id))
SubPlan 15
-> Index Only Scan using person_block_pkey on person_block person_block_1 (cost=0.28..2.50 rows=1 width=0)
Index Cond: ((person_id = xx) AND (target_id = post_aggregates.creator_id))
SubPlan 17
-> Limit (cost=0.56..2.78 rows=1 width=2)
-> Index Scan using post_like_pkey on post_like (cost=0.56..2.78 rows=1 width=2)
Index Cond: ((person_id = xx) AND (post_id = post_aggregates.post_id))
SubPlan 18
-> Limit (cost=0.43..2.65 rows=1 width=8)
-> Index Scan using person_post_aggregates_pkey on person_post_aggregates (cost=0.43..2.65 rows=1 width=8)
Index Cond: ((person_id = xx) AND (post_id = post_aggregates.post_id))
(Which is what I see you already responded with @dullbananas )
Can you provide some guidance on the index creation that is needed that might help this problem? (I can then test in isolation)
I also attempted to signup to voyager.lemmy.ml (awaiting application approval) to try and see if this was an issue on your beta version too.
Nutomic commented
@ticoombs I approved your registration on voyager. The pagination there is also slow (a few seconds), but still much faster than in your case. Probably because the test instance has much less data stored.
dessalines commented
I wonder why its ignoring the index for this one:
> Parallel Seq Scan on post_aggregates (cost=0.00..55988.24 rows=1 width=106)
> Filter: (ROW(false, '0.5677054998208626'::double precision, '2024-04-14 10:46:25.502725+00'::timestamp with time zone, 17261302) >
ROW(featured_local, hot_rank_active, published, post_id))
And we have an index there:
"idx_post_aggregates_featured_local_active" btree (featured_local DESC, hot_rank_active DESC, published DESC)
Nutomic commented
Maybe cause the index doesnt include post_id?
ticoombs commented
- mlmym (old) uses
page=2| Not a problem - alexandrite uses
page=2| Not a problem - voyager users
page_cursor=P1061ba5| Is a problem - lemmy-ui uses
page=P1061ba5| Is a problem - next uses
page=P1061ba5| Is a problem
I think it is something to do with page_cursor=P<id> vs page=1/2/3 ?
https://reddthat.com/api/v3/post/list?page_cursor=P1061ba5&limit=50&sort=Active&type_=Subscribed- Fasthttps://reddthat.com/api/v3/post/list?page_cursor=P1061ba5&limit=50&sort=Active&type_=Local- Slowhttps://reddthat.com/api/v3/post/list?page_cursor=P1061ba5&limit=50&sort=Active&type_=All- Slowhttps://reddthat.com/api/v3/post/list?page=3&limit=50&sort=Active&type_=Subscribed- Fasthttps://reddthat.com/api/v3/post/list?page=4&limit=50&sort=Active&type_=Local- Fasthttps://reddthat.com/api/v3/post/list?page=5&limit=50&sort=Active&type_=All- Fast
As a reminder this only happens while logged in. I'm very confident that its a pagecursor issue (somewhere...)
Edit: I should note page=3 did work and got the '3rd page' of local/all posts.
dessalines commented
Might need @dullbananas to look at this one, might have to do with the way their PaginatedQueryBuilder is fitting into lemmy.
ticoombs commented
So... possibly related... #4641 (My indexes are in there). I wonder if something happened with my indexes?
When testing a -beta.3 instance using my dev box i get the exact same instances
dessalines commented
Its not your indexes... I set up lemmy.ml's prod DB on my local machine, and can verify that paging is a problem. Some of the costly ones:
Parallel Seq Scan on community (cost=0.00..2299.43 rows=12 width=1160) (actual time=0.015..7.390 rows=1790 loops=2)
postgres-1 | Filter: ((NOT deleted) AND (NOT removed) AND local AND (NOT hidden) AND (NOT nsfw) AND (visibility = 'Public'::community_visibility))
postgres-1 | Rows Removed by Filter: 6058
postgres-1 | -> Bitmap Heap Scan on post_aggregates (cost=6.20..800.11 rows=1 width=106) (actual time=0.023..0.241 rows=52 loops=3581)
postgres-1 | Recheck Cond: (community.id = community_id)
postgres-1 | Filter: (ROW(false, '0.11711972755984451'::double precision, '2024-04-15 00:17:27.211268+00'::timestamp with time zone, 14466401) > ROW(featured_local, hot_rank_active, published, post_id))
postgres-1 | Rows Removed by Filter: 0
postgres-1 | Heap Blocks: exact=972
postgres-1 | -> Bitmap Index Scan on idx_post_aggregates_featured_community_published_asc (cost=0.00..6.20 rows=236 width=0) (actual time=0.013..0.013 rows=52 loops=3581)
postgres-1 | Index Cond: (community_id = community.id)
dessalines commented
I've verified that adding the post_id to that index fixes it. I'll have a PR shortly.