Issue Summary

When the Team custom field value is fetched, it can result in up to 4 database queries being executed. This can be especially problematic in situations that retrieve the value for many issues, like exporting or viewing Agile boards with the Team field displayed on cards.

Those SQL queries are related to permission checks. For example, if a team exists only in a single plan, we should only display it to users who can access that plan.

This is reproducible on Data Center: yes

Steps to Reproduce

1. Create 1000 issues with a non-empty Team field from Advanced Roadmaps for Jira
2. Go to Issues > Search for issues > Export > CSV (All Fields)

Expected Results

Fetching the value of the Team custom field has an acceptable performance

Actual Results

Fetching the value of the Team custom field is slow, and can involve 4 SQL queries.

The slowest query is used to check in which plan the team is being used. It often involves a full table scan and includes a JOIN.

Workaround

• The permission checks for Shared Teams are significantly simpler, and they don't need to execute the two slowest queries. If changing your teams to shareable (which will make them visible to more users on the instance, and make them reusable across plans) is acceptable, it will improve the performance.
• You can remove the need for a full table scan from the slowest query by adding an index to the TEAM_ID column in the AO_D9132D_PLANTEAM table:

  CREATE INDEX idx_planteam_team_id ON AO_D9132D_PLANTEAM (TEAM_ID);
  

  This won't fully solve the problem, as the query still performs a potentially expensive LEFT JOIN.