Linked Items BE: Fix N+1 queries executed when fetching linked items widgets
When querying linked work items (introduced with !126980 (merged)) we execute N+1 queries for the nested widgets.
Example queries for assignees widget
WorkItems::Type Load (0.2ms) SELECT "work_item_types".* FROM "work_item_types" WHERE "work_item_types"."id" = 2417 LIMIT 1 /*application:web,correlation_id:01H6RBH2Q7BBDNGERZYX88WDMZ,endpoint_id:graphql:unknown,db_config_name:main,line:/ee/app/models/ee/work_item.rb:27:in `block in widgets'*/
↳ ee/app/models/ee/work_item.rb:27:in `block in widgets'
WorkItems::WidgetDefinition Load (0.1ms) SELECT "work_item_widget_definitions".* FROM "work_item_widget_definitions" WHERE "work_item_widget_definitions"."work_item_type_id" = 2417 AND "work_item_widget_definitions"."disabled" = FALSE /*application:web,correlation_id:01H6RBH2Q7BBDNGERZYX88WDMZ,endpoint_id:graphql:unknown,db_config_name:main,line:/app/models/work_items/type.rb:90:in `filter_map'*/
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
User Load (0.5ms) SELECT "users".* FROM "users" INNER JOIN "issue_assignees" ON "users"."id" = "issue_assignees"."user_id" WHERE "issue_assignees"."issue_id" = 1891 ORDER BY "users"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6RBH2Q7BBDNGERZYX88WDMZ,endpoint_id:graphql:unknown,db_config_name:main,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
CACHE WorkItems::Type Load (0.0ms) SELECT "work_item_types".* FROM "work_item_types" WHERE "work_item_types"."id" = 2417 LIMIT 1
↳ ee/app/models/ee/work_item.rb:27:in `block in widgets'
CACHE WorkItems::WidgetDefinition Load (0.0ms) SELECT "work_item_widget_definitions".* FROM "work_item_widget_definitions" WHERE "work_item_widget_definitions"."work_item_type_id" = 2417 AND "work_item_widget_definitions"."disabled" = FALSE
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
User Load (0.5ms) SELECT "users".* FROM "users" INNER JOIN "issue_assignees" ON "users"."id" = "issue_assignees"."user_id" WHERE "issue_assignees"."issue_id" = 1892 ORDER BY "users"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6RBH2Q7BBDNGERZYX88WDMZ,endpoint_id:graphql:unknown,db_config_name:main,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
CACHE WorkItems::Type Load (0.0ms) SELECT "work_item_types".* FROM "work_item_types" WHERE "work_item_types"."id" = 2417 LIMIT 1
↳ ee/app/models/ee/work_item.rb:27:in `block in widgets'
CACHE WorkItems::WidgetDefinition Load (0.0ms) SELECT "work_item_widget_definitions".* FROM "work_item_widget_definitions" WHERE "work_item_widget_definitions"."work_item_type_id" = 2417 AND "work_item_widget_definitions"."disabled" = FALSE
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
User Load (0.7ms) SELECT "users".* FROM "users" INNER JOIN "issue_assignees" ON "users"."id" = "issue_assignees"."user_id" WHERE "issue_assignees"."issue_id" = 1893 ORDER BY "users"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6RBH2Q7BBDNGERZYX88WDMZ,endpoint_id:graphql:unknown,db_config_name:main,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
CACHE WorkItems::Type Load (0.0ms) SELECT "work_item_types".* FROM "work_item_types" WHERE "work_item_types"."id" = 2417 LIMIT 1
↳ ee/app/models/ee/work_item.rb:27:in `block in widgets'
CACHE WorkItems::WidgetDefinition Load (0.0ms) SELECT "work_item_widget_definitions".* FROM "work_item_widget_definitions" WHERE "work_item_widget_definitions"."work_item_type_id" = 2417 AND "work_item_widget_definitions"."disabled" = FALSE
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
User Load (0.5ms) SELECT "users".* FROM "users" INNER JOIN "issue_assignees" ON "users"."id" = "issue_assignees"."user_id" WHERE "issue_assignees"."issue_id" = 1894 ORDER BY "users"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6RBH2Q7BBDNGERZYX88WDMZ,endpoint_id:graphql:unknown,db_config_name:main,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
CACHE WorkItems::Type Load (0.0ms) SELECT "work_item_types".* FROM "work_item_types" WHERE "work_item_types"."id" = 2417 LIMIT 1
↳ ee/app/models/ee/work_item.rb:27:in `block in widgets'
CACHE WorkItems::WidgetDefinition Load (0.0ms) SELECT "work_item_widget_definitions".* FROM "work_item_widget_definitions" WHERE "work_item_widget_definitions"."work_item_type_id" = 2417 AND "work_item_widget_definitions"."disabled" = FALSE
↳ config/initializers/active_record_relation_union_reset.rb:7:in `exec_queries'
User Load (0.5ms) SELECT "users".* FROM "users" INNER JOIN "issue_assignees" ON "users"."id" = "issue_assignees"."user_id" WHERE "issue_assignees"."issue_id" = 1895 ORDER BY "users"."id" DESC LIMIT 101 /*application:web,correlation_id:01H6RBH2Q7BBDNGERZYX88WDMZ,endpoint_id:graphql:unknown,db_config_name:main,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:122:in `block in limited_nodes'*/
Although we are using WorkItemsResolver
that uses Lookahead to preload the widget fields, these fields are not being preloaded because they are nested in a deeper level. To solve this we might need to modify LooksAhead
logic to include a deeper nested field.