[Bug]: Slow query for specific user on second page of all feed

[Sh4d]

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

One of our users has been reporting repeated timeouts / issues and it's reproducible under their user account only.

I haven't received any other user reports and I can reproduce it by logging into their account, but not my own account.

Steps to Reproduce

1. Go the main page
2. Set it to All / Host
3. Click next page

Technical Details

The query at fault is a beefy one, and my attempts to decipher what's going on haven't gotten anywhere yet.

This takes about 2-3 minutes to run as the affected user, and under 1s for myself.

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, 
        image_details.link, image_details.width, image_details.height, image_details.content_type, 
        EXISTS (
            SELECT 1 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 1 FROM community_person_ban 
            WHERE post_aggregates.community_id = community_person_ban.community_id 
            AND community_person_ban.person_id = 589122
        ),
        EXISTS (
            SELECT 1 FROM community_moderator 
            WHERE post_aggregates.community_id = community_moderator.community_id 
            AND community_moderator.person_id = post_aggregates.creator_id
        ),
        EXISTS (
            SELECT 1 FROM local_user 
            WHERE post_aggregates.creator_id = local_user.person_id 
            AND local_user.admin = TRUE
        ),
        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 = 589122 LIMIT 1),
        (post_saved.person_id IS NOT NULL),
        EXISTS (
            SELECT 1 FROM post_read 
            WHERE post_aggregates.post_id = post_read.post_id 
            AND post_read.person_id = 589122
        ),
        EXISTS (
            SELECT 1 FROM post_hide 
            WHERE post_aggregates.post_id = post_hide.post_id 
            AND post_hide.person_id = 589122
        ),
        EXISTS (
            SELECT 1 FROM person_block 
            WHERE post_aggregates.creator_id = person_block.target_id 
            AND person_block.person_id = 589122
        ),
        (SELECT post_like.score FROM post_like 
         WHERE post_aggregates.post_id = post_like.post_id 
         AND post_like.person_id = 589122 LIMIT 1),
        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 = 589122 LIMIT 1)
            ), 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
    LEFT OUTER JOIN image_details ON post.thumbnail_url = image_details.link
    LEFT OUTER JOIN post_saved ON post_aggregates.post_id = post_saved.post_id 
        AND post_saved.person_id = 589122
    WHERE 
        community.deleted = FALSE 
        AND (post.deleted = FALSE OR post.creator_id = 589122)
        AND community.removed = FALSE 
        AND post.removed = FALSE 
        AND (community.hidden = FALSE OR EXISTS (
            SELECT 1 FROM community_follower 
            WHERE post_aggregates.community_id = community_follower.community_id 
            AND community_follower.person_id = 589122
        ))
        AND post.nsfw = FALSE 
        AND community.nsfw = FALSE
        AND NOT EXISTS (
            SELECT 1 FROM post_hide 
            WHERE post_aggregates.post_id = post_hide.post_id 
            AND post_hide.person_id = 589122
        )
        AND EXISTS (
            SELECT 1 FROM local_user_language 
            WHERE post.language_id = local_user_language.language_id 
            AND local_user_language.local_user_id = 2840
        )
        AND NOT EXISTS (
            SELECT 1 FROM community_block 
            WHERE post_aggregates.community_id = community_block.community_id 
        AND community_block.person_id = 589122
        )
        AND NOT EXISTS (
            SELECT 1 FROM instance_block 
            WHERE post_aggregates.instance_id = instance_block.instance_id 
            AND instance_block.person_id = 589122
        )
        AND NOT EXISTS (
            SELECT 1 FROM person_block 
            WHERE post_aggregates.creator_id = person_block.target_id 
            AND person_block.person_id = 589122
        )
        AND (FALSE, 0.15646542509091246, '2025-02-01 01:30:18.433747+00', 38389472) > 
            (post_aggregates.featured_local, post_aggregates.hot_rank, post_aggregates.published, post_aggregates.post_id)
    ORDER BY 
        post_aggregates.featured_local DESC, 
        post_aggregates.hot_rank DESC, 
        post_aggregates.published DESC, 
        post_aggregates.post_id DESC
    LIMIT 20 OFFSET 0
);

Version

0.19.8

Lemmy Instance URL

lemmy.ca

[Sh4d]

I'm still digging into this, but wondering if anyone else has seen it as well

[dullbananas]

Are there any big differences in things like the number of subscribed communities?

[dessalines]

Also, check the numbers of all those blocks, hidden posts, and possibly user languages?

@dullbananas post_actions rewrite should hopefully fix these slowness issues in 0.20.0

[Sh4d]

That was my thinking too, but their numbers are all fairly normal - 184 languages (all of them), 53 community blocks, 31 hidden posts, 89 community follows.

I tried playing around with the query and removing these lines, makes it execute immediately. Modifying the other "and not exists" chunks doesn't help, only this one.

-        AND NOT EXISTS (
-            SELECT 1 FROM community_block
-            WHERE post_aggregates.community_id = community_block.community_id
-            AND community_block.person_id = 458479
-        )

However the users data in that table looks normal. I even deleted all their rows and it didn't impact performance at all. Maybe this isn't related, and it's just pushing the postgres query optimizer into some other direction?

I tried dropping to a single language as well, no change. I've done a vacuum as well.