It’s been a week since I’ve had to do a major attitude adjustment.

I’ve created tons of local comments on this server to try and figure out what is going on with performance that lemmy.world is still blocking all comment links: https://lemmy.world/post/2697806

I really wish developer would have rushed in a fix for the ampersand munging in URL and other content… a lot of links now on Lemmy are broken because of the damage to the URL.

  • RoundSparrow @ BTOPM
    1•1 year ago
                                                        "Plans": [
                                                      {
                                                        "Node Type": "Index Scan",
                                                        "Parent Relationship": "Outer",
                                                        "Parallel Aware": false,
                                                        "Async Capable": false,
                                                        "Scan Direction": "Forward",
                                                        "Index Name": "idx_post_aggregates_featured_community_active",
                                                        "Relation Name": "post_aggregates",
                                                        "Alias": "post_aggregates",
                                                        "Startup Cost": 0.42,
                                                        "Total Cost": 41907.69,
                                                        "Plan Rows": 93,
                                                        "Plan Width": 90,
                                                        "Actual Startup Time": 153.975,
                                                        "Actual Total Time": 327.882,
                                                        "Actual Rows": 9,
                                                        "Actual Loops": 1,
                                                        "Filter": "(community_id = $14)",
                                                        "Rows Removed by Filter": 459490,
                                                        "Shared Hit Blocks": 434687,
                                                        "Shared Read Blocks": 10015,
                                                        "Shared Dirtied Blocks": 47,
                                                        "Shared Written Blocks": 2950,
                                                        "Local Hit Blocks": 0,
                                                        "Local Read Blocks": 0,
                                                        "Local Dirtied Blocks": 0,
                                                        "Local Written Blocks": 0,
                                                        "Temp Read Blocks": 0,
                                                        "Temp Written Blocks": 0
                                                      },
    • RoundSparrow @ BTOPM
      1•1 year ago
      -- Add missing most comments index
create index idx_post_aggregates_featured_local_most_comments on post_aggregates (featured_local desc, comments desc, published desc);
create index idx_post_aggregates_featured_community_most_comments on post_aggregates (featured_community desc, comments desc, published desc);

-- featured_local
create index idx_post_aggregates_featured_local_hot on post_aggregates (featured_local desc, hot_rank desc, published desc);
create index idx_post_aggregates_featured_local_active on post_aggregates (featured_local desc, hot_rank_active desc, published desc);
create index idx_post_aggregates_featured_local_score on post_aggregates (featured_local desc, score desc, published desc);

-- featured_community
create index idx_post_aggregates_featured_community_hot on post_aggregates (featured_community desc, hot_rank desc, published desc);
create index idx_post_aggregates_featured_community_active on post_aggregates (featured_community desc, hot_rank_active desc, published desc);
create index idx_post_aggregates_featured_community_score on post_aggregates (featured_community desc, score desc, published desc);

-- Fixing some comment aggregates ones
create index idx_comment_aggregates_hot on comment_aggregates (hot_rank desc, published desc);
create index idx_comment_aggregates_score on comment_aggregates (score desc, published desc);
    • RoundSparrow @ BTOPM
      1•1 year ago
      duration: 329.450 ms  plan:
        {
          "Query Text": "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\", \"community_person_ban\".\"community_id\", \"community_person_ban\".\"person_id\", \"community_person_ban\".\"published\", \"community_person_ban\".\"expires\", \"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\".\"id\", \"community_follower\".\"community_id\", \"community_follower\".\"person_id\", \"community_follower\".\"published\", \"community_follower\".\"pending\", \"post_saved\".\"id\", \"post_saved\".\"post_id\", \"post_saved\".\"person_id\", \"post_saved\".\"published\", \"post_read\".\"id\", \"post_read\".\"post_id\", \"post_read\".\"person_id\", \"post_read\".\"published\", \"person_block\".\"id\", \"person_block\".\"person_id\", \"person_block\".\"target_id\", \"person_block\".\"published\", \"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\".\"deleted\" = $10) AND (\"post\".\"deleted\" = $11)) AND (\"community\".\"removed\" = $12)) AND (\"post\".\"removed\" = $13)) AND (\"post_aggregates\".\"community_id\" = $14)) AND ((\"community\".\"hidden\" = $15) OR (\"community_follower\".\"person_id\" = $16))) AND (\"post\".\"nsfw\" = $17)) AND (\"community\".\"nsfw\" = $18))

 ORDER BY \"post_aggregates\".\"featured_community\" DESC , \"post_aggregates\".\"hot_rank_active\" DESC , \"post_aggregates\".\"published\" DESC  LIMIT $19 OFFSET $20",
    • RoundSparrow
      1•1 year ago

      So, I queried live instance and i find no index on post_aggregates community_id

                                                          "Filter": "(community_id = $14)",
                                                    "Rows Removed by Filter": 459490,