Closed ticoombs closed 5 months ago
cc @dessalines @dullbananas
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))
Strange that it works fine on Eternity but not other frontends? Can you share an example URL that loads slowly?
@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.
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:
Logged In:
( A direct link to the problem while logged in would be: https://reddthat.com/?dataType=Post&listingType=Local&pageCursor=P10495a6&sort=New)
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.
@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.
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)
Maybe cause the index doesnt include post_id?
page=2
| Not a problempage=2
| Not a problempage_cursor=P1061ba5
| Is a problempage=P1061ba5
| Is a problempage=P1061ba5
| Is a problemI 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.
Might need @dullbananas to look at this one, might have to do with the way their PaginatedQueryBuilder
is fitting into lemmy.
So... possibly related... https://github.com/LemmyNet/lemmy/issues/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
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)
I've verified that adding the post_id
to that index fixes it. I'll have a PR shortly.
Requirements
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
Technical Details
24s request in db:
Version
0.19.4-beta.3
Lemmy Instance URL
No response