n+1 queries in Package Pipelines endpoint

Creating this as a separate issue. This was discovered during review of !117539 (merged) (thread).
When loading ::Ci::Pipelines there is an n+1 query situation.
n+1
Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.3ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 135 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'*/
  ↳ lib/api/entities/ci/pipeline_basic.rb:22:in `block in <class:PipelineBasic>'
  Namespace Load (0.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 295 LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/config/routes.rb:319:in `block (4 levels) in <main>'*/
  ↳ config/routes.rb:319:in `block (4 levels) in <main>'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 295 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
  Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 135 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:web,correlation_id:01H2883YSV4HKKTY52XH36JDEW,endpoint_id:GET /api/:version/projects/:id/packages/:package_id/pipelines,db_config_name:main,line:/app/models/concerns/routable.rb:141:in `block in full_attribute'*/
  ↳ app/models/concerns/routable.rb:141:in `block in full_attribute'
This is mitigated by the fact that we only return a maximum of 20 pipelines per request.
This line is the source of the n+1. The project and its 'namespace' gets loaded.
We can fix that with a .includes(project: :namespace) when loading ::Ci::Pipelines. The only thing is that we can't use that in the API controller directly. We'd like to avoid modifying the ::Ci::Pipielines model if possible.
Maybe a compromise is to introduce a new scope in the ::Ci::Pipelines model, and use it in a new Finder class to be used by the API controller.
Edited Jul 20, 2023 by Radamanthus Batnag