Improve how we query invited members
Problem
With the introduction of User Caps, we not only have users in the invited state, but also in the awaiting state !71257 (merged).
Both states appear in the invited tab for the user, so they need to be queried together:
This raised a discussion on how we can improve this query. @jagood did an initial investigation
SQL:
SELECT COUNT(*) FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 9970 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5 AND ("members"."invite_token" IS NOT NULL OR "members"."state" = 1)
Which looks like this in database lab for GitLab.org:
Slack: https://gitlab.slack.com/archives/CLJMDRD8C/p1635805177402900
Postgres.ai: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7122/commands/25195
Proposal
We discussed this a bit on this MR !72805 (comment 717516977)
We may need to look into putting some kind of index on this?
@jagood
tried to put an index on it: https://gitlab.slack.com/archives/CLJMDRD8C/p1635805856405500
Which improved the performance: https://gitlab.slack.com/archives/CLJMDRD8C/p1635805947406400
There are more details on this thread !72805 (comment 717516977)
Result
We need to investigate possible approaches to this and how we can improve the performance on this query