N+1 SQL queries in PipelinesController#index when loading merge request pipeline
When I load https://gitlab.com/gitlab-org/gitlab/pipelines, I see this in the performance bar:
cat /tmp/pipelines_perf_bar_1582090196383.json | jq | grep sql | sort | uniq -c | sort -nr | more
62 "sql": "SELECT \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_id\" = 9970 AND \"routes\".\"source_type\" = 'Namespace' LIMIT 1",
62 "sql": "SELECT \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_id\" = 278964 AND \"routes\".\"source_type\" = 'Project' LIMIT 1",
61 "sql": "SELECT \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"id\" = 9970 LIMIT 1",
60 "sql": "SELECT \"projects\".* FROM \"projects\" WHERE \"projects\".\"id\" = 278964 LIMIT 1",
18 "sql": "SELECT \"environments\".* FROM \"environments\" WHERE \"environments\".\"name\" = 'review/auto-cleanup' AND \"environments\".\"project_id\" = 278964 LIMIT 1",
16 "sql": "SELECT \"ci_pipelines\".* FROM \"ci_pipelines\" WHERE \"ci_pipelines\".\"id\" = 119117809 LIMIT 1",
16 "app/models/ci/pipeline.rb:407:in `legacy_stages_using_sql'",
8 "sql": "SELECT \"projects\".* FROM \"projects\" WHERE \"projects\".\"id\" = 278964",
6 "sql": "SELECT \"protected_branches\".* FROM \"protected_branches\" WHERE \"protected_branches\".\"project_id\" = 278964",
5 "sql": "SELECT \"saml_providers\".* FROM \"saml_providers\" WHERE \"saml_providers\".\"group_id\" = 9970 LIMIT 1",
5 "sql": "SELECT \"project_features\".* FROM \"project_features\" WHERE \"project_features\".\"project_id\" = 278964 LIMIT 1",
5 "sql": "SELECT \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"type\" = 'Group' AND \"namespaces\".\"id\" = 9970 AND \"namespaces\".\"type\" = 'Group' LIMIT 1",
4 "sql": "SELECT \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_type\" = 'Namespace' AND \"routes\".\"source_id\" = 9970",
4 "sql": "SELECT \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"id\" = 9970",
4 "sql": "SELECT \"ci_variables\".* FROM \"ci_variables\" WHERE \"ci_variables\".\"project_id\" = 278964 AND \"ci_variables\".\"protected\" = FALSE AND \"ci_variables\".\"environment_scope\" = '*'",
4 "sql": "SELECT \"ci_pipelines\".* FROM \"ci_pipelines\" WHERE \"ci_pipelines\".\"id\" = 119123902 LIMIT 1",
4 "sql": "SELECT \"ci_pipelines\".* FROM \"ci_pipelines\" WHERE \"ci_pipelines\".\"id\" = 119123721 LIMIT 1",
Most of the first queries are due to loading the source and target project of the merge request: https://gitlab.com/gitlab-org/gitlab/blob/072887b0b8122630229a4d47223a80c787d9a471/app/serializers/pipeline_entity.rb#L28
It might help to add these relations to the preloaded_relations: https://gitlab.com/gitlab-org/gitlab/blob/1a9f1db4ee07b0018f7bebe9a2bb00e4e494d213/app/serializers/pipeline_serializer.rb#L59