Admin user list is slow to load at GitLab.com scale
Loading https://gitlab.com/admin/users with the performance bar enabled shows some slow queries that should be optimized:
time | query |
---|---|
5693.828ms | SELECT COUNT(*) FROM "users" WHERE (id NOT IN (SELECT DISTINCT(user_id) FROM members WHERE user_id IS NOT NULL AND requested_at IS NULL)); |
863.907ms | SELECT COUNT(*) FROM "users" WHERE "users"."external" = 't'; |
920.105ms | SELECT COUNT(*) FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."ghost" = 'f' OR "users"."ghost" IS NULL) AND ("users"."support_bot" = 'f' OR "users"."support_bot" IS NULL); |
863.907ms | SELECT COUNT(*) FROM "users" WHERE "users"."external" = 't'; |
1150.806ms | SELECT DISTINCT COUNT(DISTINCT "users"."id") FROM "users" LEFT OUTER JOIN u2f_registrations AS u2f ON u2f.user_id = users.id WHERE (u2f.id IS NOT NULL OR otp_required_for_login = 't'); |
1130.110ms | SELECT COUNT(*) FROM "users" LEFT OUTER JOIN u2f_registrations AS u2f ON u2f.user_id = users.id WHERE (u2f.id IS NULL AND otp_required_for_login = 'f'); |
1107.923ms | SELECT COUNT(*) FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."ghost" = 'f' OR "users"."ghost" IS NULL) AND ("users"."support_bot" = 'f' OR "users"."support_bot" IS NULL); |
It takes about 10 seconds to load this page on gitlab.com, and it's paginated.