WesleyAC
commented
1 year ago SQL queries involved in making a request to /user/b (user ID 2) as user "a" (user ID 1):
Queries
``` SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" WHERE "bookwyrm_user"."id" = 1 LIMIT 21; SELECT (1) AS "a" FROM "bookwyrm_ipblocklist" WHERE "bookwyrm_ipblocklist"."address" = '172.23.0.9' LIMIT 1; SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" WHERE ("bookwyrm_user"."is_active" AND NOT (EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U1 WHERE (U1."user_object_id" = 1 AND U1."user_subject_id" = "bookwyrm_user"."id") LIMIT 1)) AND "bookwyrm_user"."localname" = 'b') LIMIT 21; SELECT DISTINCT "bookwyrm_shelf"."id", "bookwyrm_shelf"."created_date", "bookwyrm_shelf"."updated_date", "bookwyrm_shelf"."remote_id", "bookwyrm_shelf"."name", "bookwyrm_shelf"."identifier", "bookwyrm_shelf"."description", "bookwyrm_shelf"."user_id", "bookwyrm_shelf"."editable", "bookwyrm_shelf"."privacy" FROM "bookwyrm_shelf" INNER JOIN "bookwyrm_shelfbook" ON ("bookwyrm_shelf"."id" = "bookwyrm_shelfbook"."shelf_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_shelf"."user_id") LIMIT 1))) AND "bookwyrm_shelf"."privacy" IN ('public', 'followers') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR "bookwyrm_shelf"."user_id" = 1)) AND "bookwyrm_shelf"."privacy" = 'followers') AND "bookwyrm_shelfbook"."book_id" IS NOT NULL AND "bookwyrm_shelf"."user_id" = 2) LIMIT 3; SELECT "bookwyrm_annualgoal"."id", "bookwyrm_annualgoal"."created_date", "bookwyrm_annualgoal"."updated_date", "bookwyrm_annualgoal"."remote_id", "bookwyrm_annualgoal"."user_id", "bookwyrm_annualgoal"."goal", "bookwyrm_annualgoal"."year", "bookwyrm_annualgoal"."privacy" FROM "bookwyrm_annualgoal" WHERE ("bookwyrm_annualgoal"."user_id" = 2 AND "bookwyrm_annualgoal"."year" = 2023) ORDER BY "bookwyrm_annualgoal"."id" ASC LIMIT 1; SELECT COUNT(*) FROM (SELECT DISTINCT "bookwyrm_shelf"."id" AS Col1, "bookwyrm_shelf"."created_date" AS Col2, "bookwyrm_shelf"."updated_date" AS Col3, "bookwyrm_shelf"."remote_id" AS Col4, "bookwyrm_shelf"."name" AS Col5, "bookwyrm_shelf"."identifier" AS Col6, "bookwyrm_shelf"."description" AS Col7, "bookwyrm_shelf"."user_id" AS Col8, "bookwyrm_shelf"."editable" AS Col9, "bookwyrm_shelf"."privacy" AS Col10 FROM "bookwyrm_shelf" INNER JOIN "bookwyrm_shelfbook" ON ("bookwyrm_shelf"."id" = "bookwyrm_shelfbook"."shelf_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_shelf"."user_id") LIMIT 1))) AND "bookwyrm_shelf"."privacy" IN ('public', 'followers') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR "bookwyrm_shelf"."user_id" = 1)) AND "bookwyrm_shelf"."privacy" = 'followers') AND "bookwyrm_shelfbook"."book_id" IS NOT NULL AND "bookwyrm_shelf"."user_id" = 2)) subquery; SELECT COUNT(*) AS "__count" FROM "bookwyrm_status" INNER JOIN "bookwyrm_user" ON ("bookwyrm_status"."user_id" = "bookwyrm_user"."id") LEFT OUTER JOIN "bookwyrm_comment" ON ("bookwyrm_status"."id" = "bookwyrm_comment"."status_ptr_id") LEFT OUTER JOIN "bookwyrm_quotation" ON ("bookwyrm_status"."id" = "bookwyrm_quotation"."status_ptr_id") LEFT OUTER JOIN "bookwyrm_review" ON ("bookwyrm_status"."id" = "bookwyrm_review"."status_ptr_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_status"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_status"."user_id") LIMIT 1))) AND "bookwyrm_status"."privacy" IN ('public', 'unlisted', 'followers', 'direct') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_status"."user_id") LIMIT 1) OR "bookwyrm_status"."user_id" = 1 OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_status_mention_users" U1 WHERE (U1."user_id" = 1 AND U1."status_id" = "bookwyrm_status"."id") LIMIT 1))) AND "bookwyrm_status"."privacy" = 'followers') AND NOT (NOT (("bookwyrm_status"."user_id" = 1 OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_status_mention_users" U1 WHERE (U1."user_id" = 1 AND U1."status_id" = "bookwyrm_status"."id") LIMIT 1))) AND "bookwyrm_status"."privacy" = 'direct') AND NOT "bookwyrm_status"."deleted" AND "bookwyrm_user"."is_active" AND "bookwyrm_status"."user_id" = 2 AND NOT ("bookwyrm_comment"."status_ptr_id" IS NULL AND "bookwyrm_status"."privacy" = 'direct' AND "bookwyrm_quotation"."status_ptr_id" IS NULL AND "bookwyrm_review"."status_ptr_id" IS NULL)); SELECT "bookwyrm_sitesettings"."id", "bookwyrm_sitesettings"."name", "bookwyrm_sitesettings"."instance_tagline", "bookwyrm_sitesettings"."instance_description", "bookwyrm_sitesettings"."instance_short_description", "bookwyrm_sitesettings"."default_theme_id", "bookwyrm_sitesettings"."version", "bookwyrm_sitesettings"."install_mode", "bookwyrm_sitesettings"."admin_code", "bookwyrm_sitesettings"."registration_closed_text", "bookwyrm_sitesettings"."invite_request_text", "bookwyrm_sitesettings"."code_of_conduct", "bookwyrm_sitesettings"."privacy_policy", "bookwyrm_sitesettings"."impressum", "bookwyrm_sitesettings"."show_impressum", "bookwyrm_sitesettings"."allow_registration", "bookwyrm_sitesettings"."allow_invite_requests", "bookwyrm_sitesettings"."invite_request_question", "bookwyrm_sitesettings"."require_confirm_email", "bookwyrm_sitesettings"."default_user_auth_group_id", "bookwyrm_sitesettings"."invite_question_text", "bookwyrm_sitesettings"."logo", "bookwyrm_sitesettings"."logo_small", "bookwyrm_sitesettings"."favicon", "bookwyrm_sitesettings"."preview_image", "bookwyrm_sitesettings"."support_link", "bookwyrm_sitesettings"."support_title", "bookwyrm_sitesettings"."admin_email", "bookwyrm_sitesettings"."footer_item", "bookwyrm_sitesettings"."imports_enabled", "bookwyrm_sitesettings"."import_size_limit", "bookwyrm_sitesettings"."import_limit_reset" FROM "bookwyrm_sitesettings" LIMIT 21; SELECT COUNT(*) AS "__count" FROM "bookwyrm_notification" WHERE ("bookwyrm_notification"."user_id" = 1 AND NOT "bookwyrm_notification"."read"); SELECT (1) AS "a" FROM "bookwyrm_notification" WHERE ("bookwyrm_notification"."user_id" = 1 AND "bookwyrm_notification"."notification_type" IN ('REPLY', 'MENTION', 'TAG', 'REPORT') AND NOT "bookwyrm_notification"."read") LIMIT 1; SELECT (1) AS "a" FROM "bookwyrm_announcement" WHERE (("bookwyrm_announcement"."start_date" IS NULL OR "bookwyrm_announcement"."start_date" <= '2023-04-07T04:11:27.052648+00:00'::timestamptz) AND ("bookwyrm_announcement"."end_date" IS NULL OR "bookwyrm_announcement"."end_date" >= '2023-04-07T04:11:27.052648+00:00'::timestamptz) AND "bookwyrm_announcement"."active") LIMIT 1; SELECT COUNT(*) AS "__count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userfollows" ON ("bookwyrm_user"."id" = "bookwyrm_userfollows"."user_subject_id") INNER JOIN "bookwyrm_userfollows" T4 ON ("bookwyrm_user"."id" = T4."user_object_id") WHERE ("bookwyrm_userfollows"."user_object_id" = 2 AND T4."user_subject_id" = 1); SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userfollows" ON ("bookwyrm_user"."id" = "bookwyrm_userfollows"."user_object_id") WHERE "bookwyrm_userfollows"."user_subject_id" = 2; SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userblocks" ON ("bookwyrm_user"."id" = "bookwyrm_userblocks"."user_object_id") WHERE "bookwyrm_userblocks"."user_subject_id" = 1; SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userfollows" ON ("bookwyrm_user"."id" = "bookwyrm_userfollows"."user_object_id") WHERE "bookwyrm_userfollows"."user_subject_id" = 1; SELECT (1) AS "a" FROM "bookwyrm_groupmember" WHERE "bookwyrm_groupmember"."user_id" = 2 LIMIT 1; SELECT (1) AS "a" FROM "bookwyrm_list" WHERE "bookwyrm_list"."user_id" = 2 LIMIT 1; SELECT (1) AS "a" FROM "bookwyrm_shelf" WHERE "bookwyrm_shelf"."user_id" = 2 LIMIT 1; ```EXPLAIN results
``` fedireads=# EXPLAIN SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" WHERE "bookwyrm_user"."id" = 1 LIMIT 21; QUERY PLAN ------------------------------------------------------------------------------------------------ Limit (cost=0.42..2.64 rows=1 width=899) -> Index Scan using bookwyrm_user_pkey on bookwyrm_user (cost=0.42..2.64 rows=1 width=899) Index Cond: (id = 1) (3 rows) fedireads=# EXPLAIN SELECT (1) AS "a" FROM "bookwyrm_ipblocklist" WHERE "bookwyrm_ipblocklist"."address" = '172.23.0.9' LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ------------------------ Limit (cost=0.14..2.36 rows=1 width=4) -> Index Only Scan using bookwyrm_ipblocklist_address_99febf90_like on bookwyrm_ipblocklist (cost=0.1 4..2.36 rows=1 width=4) Index Cond: (address = '172.23.0.9'::text) (3 rows) fedireads=# EXPLAIN SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" WHERE ("bookwyrm_user"."is_active" AND NOT (EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U1 WHERE (U1."user_object_id" = 1 AND U1."user_subject_id" = "bookwyrm_user"."id") LIMIT 1)) AND "bookwyrm_user"."localname" = 'b') LIMIT 21; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ---------------------------- Limit (cost=0.56..6.53 rows=1 width=899) -> Nested Loop Anti Join (cost=0.56..6.53 rows=1 width=899) Join Filter: (u1.user_subject_id = bookwyrm_user.id) -> Index Scan using bookwyrm_user_localname_key on bookwyrm_user (cost=0.42..2.64 rows=1 width= 899) Index Cond: (localname = 'b'::citext) Filter: is_active -> Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_userblocks u1 (cost =0.14..3.78 rows=9 width=4) Index Cond: (user_object_id = 1) (8 rows) fedireads=# EXPLAIN SELECT DISTINCT "bookwyrm_shelf"."id", "bookwyrm_shelf"."created_date", "bookwyrm_shelf"."updated_date", "bookwyrm_shelf"."remote_id", "bookwyrm_shelf"."name", "bookwyrm_shelf"."identifier", "bookwyrm_shelf"."description", "bookwyrm_shelf"."user_id", "bookwyrm_shelf"."editable", "bookwyrm_shelf"."privacy" FROM "bookwyrm_shelf" INNER JOIN "bookwyrm_shelfbook" ON ("bookwyrm_shelf"."id" = "bookwyrm_shelfbook"."shelf_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_shelf"."user_id") LIMIT 1))) AND "bookwyrm_shelf"."privacy" IN ('public', 'followers') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR "bookwyrm_shelf"."user_id" = 1)) AND "bookwyrm_shelf"."privacy" = 'followers') AND "bookwyrm_shelfbook"."book_id" IS NOT NULL AND "bookwyrm_shelf"."user_id" = 2) LIMIT 3; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- Limit (cost=100.84..101.29 rows=2 width=127) -> Unique (cost=100.84..101.29 rows=2 width=127) -> Sort (cost=100.84..100.88 rows=18 width=127) Sort Key: bookwyrm_shelf.id, bookwyrm_shelf.created_date, bookwyrm_shelf.updated_date, book wyrm_shelf.remote_id, bookwyrm_shelf.name, bookwyrm_shelf.identifier, bookwyrm_shelf.description, bookwyrm _shelf.editable, bookwyrm_shelf.privacy -> Nested Loop (cost=0.71..100.46 rows=18 width=127) -> Index Scan using bookwyrm_shelf_user_id_cb8d2889 on bookwyrm_shelf (cost=0.29..3 2.13 rows=1 width=127) Index Cond: (user_id = 2) Filter: (((privacy)::text = ANY ('{public,followers}'::text[])) AND (NOT (alter natives: SubPlan 1 or hashed SubPlan 2)) AND (NOT (alternatives: SubPlan 3 or hashed SubPlan 4)) AND ((alt ernatives: SubPlan 5 or hashed SubPlan 6) OR (user_id = 1) OR ((privacy)::text <> 'followers'::text))) SubPlan 1 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm _userblocks u2 (cost=0.14..2.37 rows=1 width=0) Index Cond: (user_subject_id = 1) Filter: (user_object_id = bookwyrm_shelf.user_id) SubPlan 2 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm _userblocks u2_1 (cost=0.14..2.36 rows=1 width=4) Index Cond: (user_subject_id = 1) SubPlan 3 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm _userblocks u2_2 (cost=0.14..2.37 rows=1 width=0) Index Cond: (user_subject_id = bookwyrm_shelf.user_id) Filter: (user_object_id = 1) SubPlan 4 -> Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_ userblocks u2_3 (cost=0.14..3.78 rows=9 width=4) Index Cond: (user_object_id = 1) SubPlan 5 -> Index Only Scan using userfollows_unique on bookwyrm_userfollows u2_4 (c ost=0.29..2.51 rows=1 width=0) Index Cond: ((user_subject_id = 1) AND (user_object_id = bookwyrm_shelf .user_id)) SubPlan 6 -> Index Scan using bookwyrm_userfollows_user_subject_id_8f86563d on bookwyr m_userfollows u2_5 (cost=0.29..54.15 rows=157 width=4) Index Cond: (user_subject_id = 1) -> Index Scan using bookwyrm_shelfbook_shelf_id_30ee4fe8 on bookwyrm_shelfbook (cos t=0.42..66.59 rows=174 width=4) Index Cond: (shelf_id = bookwyrm_shelf.id) Filter: (book_id IS NOT NULL) (31 rows) fedireads=# EXPLAIN SELECT "bookwyrm_annualgoal"."id", "bookwyrm_annualgoal"."created_date", "bookwyrm_annualgoal"."updated_date", "bookwyrm_annualgoal"."remote_id", "bookwyrm_annualgoal"."user_id", "bookwyrm_annualgoal"."goal", "bookwyrm_annualgoal"."year", "bookwyrm_annualgoal"."privacy" FROM "bookwyrm_annualgoal" WHERE ("bookwyrm_annualgoal"."user_id" = 2 AND "bookwyrm_annualgoal"."year" = 2023) ORDER BY "bookwyrm_annualgoal"."id" ASC LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ------------------- Limit (cost=2.51..2.52 rows=1 width=87) -> Sort (cost=2.51..2.52 rows=1 width=87) Sort Key: id -> Index Scan using bookwyrm_annualgoal_user_id_990d313d on bookwyrm_annualgoal (cost=0.28..2.5 0 rows=1 width=87) Index Cond: (user_id = 2) Filter: (year = 2023) (6 rows) fedireads=# EXPLAIN SELECT COUNT(*) FROM (SELECT DISTINCT "bookwyrm_shelf"."id" AS Col1, "bookwyrm_shelf"."created_date" AS Col2, "bookwyrm_shelf"."updated_date" AS Col3, "bookwyrm_shelf"."remote_id" AS Col4, "bookwyrm_shelf"."name" AS Col5, "bookwyrm_shelf"."identifier" AS Col6, "bookwyrm_shelf"."description" AS Col7, "bookwyrm_shelf"."user_id" AS Col8, "bookwyrm_shelf"."editable" AS Col9, "bookwyrm_shelf"."privacy" AS Col10 FROM "bookwyrm_shelf" INNER JOIN "bookwyrm_shelfbook" ON ("bookwyrm_shelf"."id" = "bookwyrm_shelfbook"."shelf_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_shelf"."user_id") LIMIT 1))) AND "bookwyrm_shelf"."privacy" IN ('public', 'followers') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR "bookwyrm_shelf"."user_id" = 1)) AND "bookwyrm_shelf"."privacy" = 'followers') AND "bookwyrm_shelfbook"."book_id" IS NOT NULL AND "bookwyrm_shelf"."user_id" = 2)) subquery; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- Aggregate (cost=101.31..101.32 rows=1 width=8) -> Unique (cost=100.84..101.29 rows=2 width=127) -> Sort (cost=100.84..100.88 rows=18 width=127) Sort Key: bookwyrm_shelf.id, bookwyrm_shelf.created_date, bookwyrm_shelf.updated_date, book wyrm_shelf.remote_id, bookwyrm_shelf.name, bookwyrm_shelf.identifier, bookwyrm_shelf.description, bookwyrm _shelf.editable, bookwyrm_shelf.privacy -> Nested Loop (cost=0.71..100.46 rows=18 width=127) -> Index Scan using bookwyrm_shelf_user_id_cb8d2889 on bookwyrm_shelf (cost=0.29..3 2.13 rows=1 width=127) Index Cond: (user_id = 2) Filter: (((privacy)::text = ANY ('{public,followers}'::text[])) AND (NOT (alter natives: SubPlan 1 or hashed SubPlan 2)) AND (NOT (alternatives: SubPlan 3 or hashed SubPlan 4)) AND ((alt ernatives: SubPlan 5 or hashed SubPlan 6) OR (user_id = 1) OR ((privacy)::text <> 'followers'::text))) SubPlan 1 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm _userblocks u2 (cost=0.14..2.37 rows=1 width=0) Index Cond: (user_subject_id = 1) Filter: (user_object_id = bookwyrm_shelf.user_id) SubPlan 2 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm _userblocks u2_1 (cost=0.14..2.36 rows=1 width=4) Index Cond: (user_subject_id = 1) SubPlan 3 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bookwyrm _userblocks u2_2 (cost=0.14..2.37 rows=1 width=0) Index Cond: (user_subject_id = bookwyrm_shelf.user_id) Filter: (user_object_id = 1) SubPlan 4 -> Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on bookwyrm_ userblocks u2_3 (cost=0.14..3.78 rows=9 width=4) Index Cond: (user_object_id = 1) SubPlan 5 -> Index Only Scan using userfollows_unique on bookwyrm_userfollows u2_4 (c ost=0.29..2.51 rows=1 width=0) Index Cond: ((user_subject_id = 1) AND (user_object_id = bookwyrm_shelf .user_id)) SubPlan 6 -> Index Scan using bookwyrm_userfollows_user_subject_id_8f86563d on bookwyr m_userfollows u2_5 (cost=0.29..54.15 rows=157 width=4) Index Cond: (user_subject_id = 1) -> Index Scan using bookwyrm_shelfbook_shelf_id_30ee4fe8 on bookwyrm_shelfbook (cos t=0.42..66.59 rows=174 width=4) Index Cond: (shelf_id = bookwyrm_shelf.id) Filter: (book_id IS NOT NULL) (31 rows) fedireads=# EXPLAIN SELECT COUNT(*) AS "__count" FROM "bookwyrm_status" INNER JOIN "bookwyrm_user" ON ("bookwyrm_status"."user_id" = "bookwyrm_user"."id") LEFT OUTER JOIN "bookwyrm_comment" ON ("bookwyrm_status"."id" = "bookwyrm_comment"."status_ptr_id") LEFT OUTER JOIN "bookwyrm_quotation" ON ("bookwyrm_status"."id" = "bookwyrm_quotation"."status_ptr_id") LEFT OUTER JOIN "bookwyrm_review" ON ("bookwyrm_status"."id" = "bookwyrm_review"."status_ptr_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_status"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_status"."user_id") LIMIT 1))) AND "bookwyrm_status"."privacy" IN ('public', 'unlisted', 'followers', 'direct') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_status"."user_id") LIMIT 1) OR "bookwyrm_status"."user_id" = 1 OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_status_mention_users" U1 WHERE (U1."user_id" = 1 AND U1."status_id" = "bookwyrm_status"."id") LIMIT 1))) AND "bookwyrm_status"."privacy" = 'followers') AND NOT (NOT (("bookwyrm_status"."user_id" = 1 OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_status_mention_users" U1 WHERE (U1."user_id" = 1 AND U1."status_id" = "bookwyrm_status"."id") LIMIT 1))) AND "bookwyrm_status"."privacy" = 'direct') AND NOT "bookwyrm_status"."deleted" AND "bookwyrm_user"."is_active" AND "bookwyrm_status"."user_id" = 2 AND NOT ("bookwyrm_comment"."status_ptr_id" IS NULL AND "bookwyrm_status"."privacy" = 'direct' AND "bookwyrm_quotation"."status_ptr_id" IS NULL AND "bookwyrm_review"."status_ptr_id" IS NULL)); QUERY PLAN ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------- Aggregate (cost=1418.89..1418.90 rows=1 width=8) -> Nested Loop Left Join (cost=1.84..1418.84 rows=22 width=0) Filter: ((bookwyrm_comment.status_ptr_id IS NOT NULL) OR ((bookwyrm_status.privacy)::text <> 'dir ect'::text) OR (bookwyrm_quotation.status_ptr_id IS NOT NULL) OR (bookwyrm_review.status_ptr_id IS NOT NUL L)) -> Nested Loop Left Join (cost=1.55..1373.46 rows=22 width=19) -> Nested Loop Left Join (cost=1.13..1323.08 rows=22 width=15) -> Nested Loop (cost=0.85..1275.73 rows=22 width=11) -> Index Scan using bookwyrm_user_pkey on bookwyrm_user (cost=0.42..2.64 rows =1 width=4) Index Cond: (id = 2) Filter: is_active -> Index Scan using bookwyrm_status_user_id_b343e19b on bookwyrm_status (cost =0.43..1272.87 rows=22 width=15) Index Cond: (user_id = 2) Filter: ((NOT deleted) AND ((privacy)::text = ANY ('{public,unlisted,foll owers,direct}'::text[])) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2)) AND (NOT (alternatives: S ubPlan 3 or hashed SubPlan 4)) AND ((user_id = 1) OR (alternatives: SubPlan 9 or hashed SubPlan 10) OR ((p rivacy)::text <> 'direct'::text)) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (user_id = 1) OR ( alternatives: SubPlan 7 or hashed SubPlan 8) OR ((privacy)::text <> 'followers'::text))) SubPlan 1 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bo okwyrm_userblocks u2 (cost=0.14..2.37 rows=1 width=0) Index Cond: (user_subject_id = 1) Filter: (user_object_id = bookwyrm_status.user_id) SubPlan 2 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bo okwyrm_userblocks u2_1 (cost=0.14..2.36 rows=1 width=4) Index Cond: (user_subject_id = 1) SubPlan 3 -> Index Scan using bookwyrm_userblocks_user_subject_id_52f9bc38 on bo okwyrm_userblocks u2_2 (cost=0.14..2.37 rows=1 width=0) Index Cond: (user_subject_id = bookwyrm_status.user_id) Filter: (user_object_id = 1) SubPlan 4 -> Index Scan using bookwyrm_userblocks_user_object_id_ff33ba18 on boo kwyrm_userblocks u2_3 (cost=0.14..3.78 rows=9 width=4) Index Cond: (user_object_id = 1) SubPlan 9 -> Index Scan using bookwyrm_status_mention_users_status_id_01acc86b o n bookwyrm_status_mention_users u1_2 (cost=0.29..2.54 rows=1 width=0) Index Cond: (status_id = bookwyrm_status.id) Filter: (user_id = 1) SubPlan 10 -> Index Scan using bookwyrm_status_mention_users_user_id_7a5496e2 on bookwyrm_status_mention_users u1_3 (cost=0.29..435.43 rows=830 width=4) Index Cond: (user_id = 1) SubPlan 5 -> Index Only Scan using userfollows_unique on bookwyrm_userfollows u2 _4 (cost=0.29..2.51 rows=1 width=0) Index Cond: ((user_subject_id = 1) AND (user_object_id = bookwyrm _status.user_id)) SubPlan 6 -> Index Scan using bookwyrm_userfollows_user_subject_id_8f86563d on b ookwyrm_userfollows u2_5 (cost=0.29..54.15 rows=157 width=4) Index Cond: (user_subject_id = 1) SubPlan 7 -> Index Scan using bookwyrm_status_mention_users_status_id_01acc86b o n bookwyrm_status_mention_users u1 (cost=0.29..2.54 rows=1 width=0) Index Cond: (status_id = bookwyrm_status.id) Filter: (user_id = 1) SubPlan 8 -> Index Scan using bookwyrm_status_mention_users_user_id_7a5496e2 on bookwyrm_status_mention_users u1_1 (cost=0.29..435.43 rows=830 width=4) Index Cond: (user_id = 1) -> Index Only Scan using bookwyrm_comment_pkey on bookwyrm_comment (cost=0.29..2.15 rows=1 width=4) Index Cond: (status_ptr_id = bookwyrm_status.id) -> Index Only Scan using bookwyrm_review_pkey on bookwyrm_review (cost=0.42..2.29 rows=1 width=4) Index Cond: (status_ptr_id = bookwyrm_status.id) -> Index Only Scan using bookwyrm_quotation_pkey on bookwyrm_quotation (cost=0.28..2.05 rows=1 width=4) Index Cond: (status_ptr_id = bookwyrm_status.id) (52 rows) fedireads=# EXPLAIN SELECT "bookwyrm_sitesettings"."id", "bookwyrm_sitesettings"."name", "bookwyrm_sitesettings"."instance_tagline", "bookwyrm_sitesettings"."instance_description", "bookwyrm_sitesettings"."instance_short_description", "bookwyrm_sitesettings"."default_theme_id", "bookwyrm_sitesettings"."version", "bookwyrm_sitesettings"."install_mode", "bookwyrm_sitesettings"."admin_code", "bookwyrm_sitesettings"."registration_closed_text", "bookwyrm_sitesettings"."invite_request_text", "bookwyrm_sitesettings"."code_of_conduct", "bookwyrm_sitesettings"."privacy_policy", "bookwyrm_sitesettings"."impressum", "bookwyrm_sitesettings"."show_impressum", "bookwyrm_sitesettings"."allow_registration", "bookwyrm_sitesettings"."allow_invite_requests", "bookwyrm_sitesettings"."invite_request_question", "bookwyrm_sitesettings"."require_confirm_email", "bookwyrm_sitesettings"."default_user_auth_group_id", "bookwyrm_sitesettings"."invite_question_text", "bookwyrm_sitesettings"."logo", "bookwyrm_sitesettings"."logo_small", "bookwyrm_sitesettings"."favicon", "bookwyrm_sitesettings"."preview_image", "bookwyrm_sitesettings"."support_link", "bookwyrm_sitesettings"."support_title", "bookwyrm_sitesettings"."admin_email", "bookwyrm_sitesettings"."footer_item", "bookwyrm_sitesettings"."imports_enabled", "bookwyrm_sitesettings"."import_size_limit", "bookwyrm_sitesettings"."import_limit_reset" FROM "bookwyrm_sitesettings" LIMIT 21; QUERY PLAN ------------------------------------------------------------------------------ Limit (cost=0.00..3.01 rows=1 width=4097) -> Seq Scan on bookwyrm_sitesettings (cost=0.00..3.01 rows=1 width=4097) (2 rows) fedireads=# EXPLAIN SELECT COUNT(*) AS "__count" FROM "bookwyrm_notification" WHERE ("bookwyrm_notification"."user_id" = 1 AND NOT "bookwyrm_notification"."read"); QUERY PLAN ---------------------------------------------------------------------------------------------------------- -------------------- Aggregate (cost=511.56..511.57 rows=1 width=8) -> Index Scan using bookwyrm_notification_user_id_5d525342 on bookwyrm_notification (cost=0.29..510.7 5 rows=321 width=0) Index Cond: (user_id = 1) Filter: (NOT read) (4 rows) fedireads=# EXPLAIN SELECT (1) AS "a" FROM "bookwyrm_notification" WHERE ("bookwyrm_notification"."user_id" = 1 AND "bookwyrm_notification"."notification_type" IN ('REPLY', 'MENTION', 'TAG', 'REPORT') AND NOT "bookwyrm_notification"."read") LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ------------------- Limit (cost=0.29..15.99 rows=1 width=4) -> Index Scan using bookwyrm_notification_user_id_5d525342 on bookwyrm_notification (cost=0.29..518.6 0 rows=33 width=4) Index Cond: (user_id = 1) Filter: ((NOT read) AND ((notification_type)::text = ANY ('{REPLY,MENTION,TAG,REPORT}'::text[]))) (4 rows) fedireads=# EXPLAIN SELECT (1) AS "a" FROM "bookwyrm_announcement" WHERE (("bookwyrm_announcement"."start_date" IS NULL OR "bookwyrm_announcement"."start_date" <= '2023-04-07T04:11:27.052648+00:00'::timestamptz) AND ("bookwyrm_announcement"."end_date" IS NULL OR "bookwyrm_announcement"."end_date" >= '2023-04-07T04:11:27.052648+00:00'::timestamptz) AND "bookwyrm_announcement"."active") LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- ------------------ Limit (cost=0.00..3.63 rows=1 width=4) -> Seq Scan on bookwyrm_announcement (cost=0.00..10.90 rows=3 width=4) Filter: (active AND ((start_date IS NULL) OR (start_date <= '2023-04-07 04:11:27.052648+00'::time stamp with time zone)) AND ((end_date IS NULL) OR (end_date >= '2023-04-07 04:11:27.052648+00'::timestamp with time zone))) (3 rows) fedireads=# EXPLAIN SELECT COUNT(*) AS "__count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userfollows" ON ("bookwyrm_user"."id" = "bookwyrm_userfollows"."user_subject_id") INNER JOIN "bookwyrm_userfollows" T4 ON ("bookwyrm_user"."id" = T4."user_object_id") WHERE ("bookwyrm_userfollows"."user_object_id" = 2 AND T4."user_subject_id" = 1); QUERY PLAN ---------------------------------------------------------------------------------------------------------- --------------------------------- Aggregate (cost=11.91..11.92 rows=1 width=8) -> Nested Loop (cost=0.99..11.91 rows=1 width=0) -> Nested Loop (cost=0.71..10.97 rows=3 width=8) -> Index Scan using bookwyrm_userfollows_user_object_id_01314278 on bookwyrm_userfollows (cost=0.29..4.16 rows=3 width=4) Index Cond: (user_object_id = 2) -> Index Only Scan using bookwyrm_user_pkey on bookwyrm_user (cost=0.42..2.27 rows=1 widt h=4) Index Cond: (id = bookwyrm_userfollows.user_subject_id) -> Index Only Scan using userfollows_unique on bookwyrm_userfollows t4 (cost=0.29..0.31 rows=1 width=4) Index Cond: ((user_subject_id = 1) AND (user_object_id = bookwyrm_user.id)) (9 rows) fedireads=# EXPLAIN SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userfollows" ON ("bookwyrm_user"."id" = "bookwyrm_userfollows"."user_object_id") WHERE "bookwyrm_userfollows"."user_subject_id" = 2; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ---------------------- Nested Loop (cost=0.71..8.18 rows=2 width=899) -> Index Scan using bookwyrm_userfollows_user_subject_id_8f86563d on bookwyrm_userfollows (cost=0.29. .2.90 rows=2 width=4) Index Cond: (user_subject_id = 2) -> Index Scan using bookwyrm_user_pkey on bookwyrm_user (cost=0.42..2.64 rows=1 width=899) Index Cond: (id = bookwyrm_userfollows.user_object_id) (5 rows) fedireads=# EXPLAIN SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userblocks" ON ("bookwyrm_user"."id" = "bookwyrm_userblocks"."user_object_id") WHERE "bookwyrm_userblocks"."user_subject_id" = 1; QUERY PLAN -------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.56..5.00 rows=1 width=899) -> Index Only Scan using userblocks_unique on bookwyrm_userblocks (cost=0.14..2.36 rows=1 width=4) Index Cond: (user_subject_id = 1) -> Index Scan using bookwyrm_user_pkey on bookwyrm_user (cost=0.42..2.64 rows=1 width=899) Index Cond: (id = bookwyrm_userblocks.user_object_id) (5 rows) fedireads=# EXPLAIN SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userfollows" ON ("bookwyrm_user"."id" = "bookwyrm_userfollows"."user_object_id") WHERE "bookwyrm_userfollows"."user_subject_id" = 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ------------------------- Nested Loop (cost=0.71..450.64 rows=157 width=899) -> Index Scan using bookwyrm_userfollows_user_subject_id_8f86563d on bookwyrm_userfollows (cost=0.29. .54.15 rows=157 width=4) Index Cond: (user_subject_id = 1) -> Index Scan using bookwyrm_user_pkey on bookwyrm_user (cost=0.42..2.53 rows=1 width=899) Index Cond: (id = bookwyrm_userfollows.user_object_id) (5 rows) fedireads=# EXPLAIN SELECT (1) AS "a" FROM "bookwyrm_groupmember" WHERE "bookwyrm_groupmember"."user_id" = 2 LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ------------------- Limit (cost=0.27..2.48 rows=1 width=4) -> Index Only Scan using bookwyrm_groupmember_user_id_a74235e3 on bookwyrm_groupmember (cost=0.27..2. 48 rows=1 width=4) Index Cond: (user_id = 2) (3 rows) fedireads=# EXPLAIN SELECT (1) AS "a" FROM "bookwyrm_list" WHERE "bookwyrm_list"."user_id" = 2 LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ----- Limit (cost=0.28..2.50 rows=1 width=4) -> Index Only Scan using bookwyrm_list_user_id_4f06fe40 on bookwyrm_list (cost=0.28..2.50 rows=1 widt h=4) Index Cond: (user_id = 2) (3 rows) fedireads=# EXPLAIN SELECT (1) AS "a" FROM "bookwyrm_shelf" WHERE "bookwyrm_shelf"."user_id" = 2 LIMIT 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- ------- Limit (cost=0.29..1.00 rows=1 width=4) -> Index Only Scan using bookwyrm_shelf_user_id_cb8d2889 on bookwyrm_shelf (cost=0.29..3.15 rows=4 wi dth=4) Index Cond: (user_id = 2) (3 rows) ```So from that, the most impactful queries seem to be:
(cost=1418.89..1418.90) SELECT COUNT(*) AS "__count" FROM "bookwyrm_status" INNER JOIN "bookwyrm_user" ON ("bookwyrm_status"."user_id" = "bookwyrm_user"."id") LEFT OUTER JOIN "bookwyrm_comment" ON ("bookwyrm_status"."id" = "bookwyrm_comment"."status_ptr_id") LEFT OUTER JOIN "bookwyrm_quotation" ON ("bookwyrm_status"."id" = "bookwyrm_quotation"."status_ptr_id") LEFT OUTER JOIN "bookwyrm_review" ON ("bookwyrm_status"."id" = "bookwyrm_review"."status_ptr_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_status"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_status"."user_id") LIMIT 1))) AND "bookwyrm_status"."privacy" IN ('public', 'unlisted', 'followers', 'direct') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_status"."user_id") LIMIT 1) OR "bookwyrm_status"."user_id" = 1 OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_status_mention_users" U1 WHERE (U1."user_id" = 1 AND U1."status_id" = "bookwyrm_status"."id") LIMIT 1))) AND "bookwyrm_status"."privacy" = 'followers') AND NOT (NOT (("bookwyrm_status"."user_id" = 1 OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_status_mention_users" U1 WHERE (U1."user_id" = 1 AND U1."status_id" = "bookwyrm_status"."id") LIMIT 1))) AND "bookwyrm_status"."privacy" = 'direct') AND NOT "bookwyrm_status"."deleted" AND "bookwyrm_user"."is_active" AND "bookwyrm_status"."user_id" = 2 AND NOT ("bookwyrm_comment"."status_ptr_id" IS NULL AND "bookwyrm_status"."privacy" = 'direct' AND "bookwyrm_quotation"."status_ptr_id" IS NULL AND "bookwyrm_review"."status_ptr_id" IS NULL));
(cost=511.56..511.57) SELECT COUNT(*) AS "__count" FROM "bookwyrm_notification" WHERE ("bookwyrm_notification"."user_id" = 1 AND NOT "bookwyrm_notification"."read");
(cost=0.71..450.64) SELECT "bookwyrm_user"."id", "bookwyrm_user"."password", "bookwyrm_user"."last_login", "bookwyrm_user"."is_superuser", "bookwyrm_user"."first_name", "bookwyrm_user"."last_name", "bookwyrm_user"."is_staff", "bookwyrm_user"."is_active", "bookwyrm_user"."date_joined", "bookwyrm_user"."username", "bookwyrm_user"."email", "bookwyrm_user"."key_pair_id", "bookwyrm_user"."inbox", "bookwyrm_user"."shared_inbox", "bookwyrm_user"."federated_server_id", "bookwyrm_user"."outbox", "bookwyrm_user"."summary", "bookwyrm_user"."local", "bookwyrm_user"."bookwyrm_user", "bookwyrm_user"."localname", "bookwyrm_user"."name", "bookwyrm_user"."avatar", "bookwyrm_user"."preview_image", "bookwyrm_user"."followers_url", "bookwyrm_user"."default_post_privacy", "bookwyrm_user"."remote_id", "bookwyrm_user"."created_date", "bookwyrm_user"."updated_date", "bookwyrm_user"."last_active_date", "bookwyrm_user"."manually_approves_followers", "bookwyrm_user"."theme_id", "bookwyrm_user"."hide_follows", "bookwyrm_user"."show_goal", "bookwyrm_user"."show_suggested_users", "bookwyrm_user"."discoverable", "bookwyrm_user"."show_guided_tour", "bookwyrm_user"."feed_status_types", "bookwyrm_user"."summary_keys", "bookwyrm_user"."preferred_timezone", "bookwyrm_user"."preferred_language", "bookwyrm_user"."deactivation_reason", "bookwyrm_user"."deactivation_date", "bookwyrm_user"."allow_reactivation", "bookwyrm_user"."confirmation_code", "bookwyrm_user"."two_factor_auth", "bookwyrm_user"."otp_secret", "bookwyrm_user"."hotp_secret", "bookwyrm_user"."hotp_count" FROM "bookwyrm_user" INNER JOIN "bookwyrm_userfollows" ON ("bookwyrm_user"."id" = "bookwyrm_userfollows"."user_object_id") WHERE "bookwyrm_userfollows"."user_subject_id" = 1;
(cost=101.31..101.32) SELECT COUNT(*) FROM (SELECT DISTINCT "bookwyrm_shelf"."id" AS Col1, "bookwyrm_shelf"."created_date" AS Col2, "bookwyrm_shelf"."updated_date" AS Col3, "bookwyrm_shelf"."remote_id" AS Col4, "bookwyrm_shelf"."name" AS Col5, "bookwyrm_shelf"."identifier" AS Col6, "bookwyrm_shelf"."description" AS Col7, "bookwyrm_shelf"."user_id" AS Col8, "bookwyrm_shelf"."editable" AS Col9, "bookwyrm_shelf"."privacy" AS Col10 FROM "bookwyrm_shelf" INNER JOIN "bookwyrm_shelfbook" ON ("bookwyrm_shelf"."id" = "bookwyrm_shelfbook"."shelf_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_shelf"."user_id") LIMIT 1))) AND "bookwyrm_shelf"."privacy" IN ('public', 'followers') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR "bookwyrm_shelf"."user_id" = 1)) AND "bookwyrm_shelf"."privacy" = 'followers') AND "bookwyrm_shelfbook"."book_id" IS NOT NULL AND "bookwyrm_shelf"."user_id" = 2)) subquery;
(cost=100.84..101.29) SELECT DISTINCT "bookwyrm_shelf"."id", "bookwyrm_shelf"."created_date", "bookwyrm_shelf"."updated_date", "bookwyrm_shelf"."remote_id", "bookwyrm_shelf"."name", "bookwyrm_shelf"."identifier", "bookwyrm_shelf"."description", "bookwyrm_shelf"."user_id", "bookwyrm_shelf"."editable", "bookwyrm_shelf"."privacy" FROM "bookwyrm_shelf" INNER JOIN "bookwyrm_shelfbook" ON ("bookwyrm_shelf"."id" = "bookwyrm_shelfbook"."shelf_id") WHERE (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userblocks" U2 WHERE (U2."user_object_id" = 1 AND U2."user_subject_id" = "bookwyrm_shelf"."user_id") LIMIT 1))) AND "bookwyrm_shelf"."privacy" IN ('public', 'followers') AND NOT (NOT ((EXISTS(SELECT (1) AS "a" FROM "bookwyrm_userfollows" U2 WHERE (U2."user_subject_id" = 1 AND U2."user_object_id" = "bookwyrm_shelf"."user_id") LIMIT 1) OR "bookwyrm_shelf"."user_id" = 1)) AND "bookwyrm_shelf"."privacy" = 'followers') AND "bookwyrm_shelfbook"."book_id" IS NOT NULL AND "bookwyrm_shelf"."user_id" = 2) LIMIT 3;However, there are a couple things to keep in mind about this:
- This might not reflect real-world results, since it's on my dev instance without much content, and there may be messy N+1 query stuff that causes most of the slowness
- This is with a logged-in user, the results will be different when not authed (the second most expensive query there is the notification counter, which I think is on every page!)
mouse-reeve
Looking at Honeycomb data for HTTP latency, the pages that stand out the most are
/inbox(expected due to #2717, although we should improve that) and/user/. Specifically:We should figure out what queries are responsible for this and improve them if possible.