dncrews opened a new pull request, #9059:
URL: https://github.com/apache/devlake/pull/9059
### Summary
`GET /blueprints/:blueprintId/pipelines` runs a `COUNT(*)` and a filtered
`SELECT` on `_devlake_pipelines.blueprint_id`, but that column was not
indexed.
On instances with a large pipeline history this forces a full table scan on
every request.
On a `v1.0.3-beta15` instance, `EXPLAIN ANALYZE` of the count showed a full
scan
of ~34k rows taking ~30s to return the 3 matching pipelines:
```
-> Aggregate: count(0) (actual time=30173..30173 rows=1 loops=1)
-> Filter: (_devlake_pipelines.blueprint_id = 876) (actual
time=26756..30173 rows=3 loops=1)
-> Table scan on _devlake_pipelines (cost=16488 rows=27609) (actual
time=545..30170 rows=34008 loops=1)
```
That ~30s scan exceeds the gateway timeout and surfaces as an `upstream
request
timeout` in config-ui when opening a blueprint's pipeline history.
This PR adds an index on `_devlake_pipelines.blueprint_id`:
- a `gorm:"index"` tag on `Pipeline.BlueprintId` so fresh installs create the
index at table creation, and
- a migration script (`20260818_add_blueprint_id_index_to_pipelines.go`) so
existing installs get the index via `AutoMigrateTables`, following the same
pattern as `addPullRequestIdIndexToPullRequestComments`.
With the index, the `blueprint_id` count/lookup becomes an index seek
instead of
a full table scan; the same `EXPLAIN ANALYZE` returns in sub-millisecond
time.
### Does this close any open issues?
N/A
### Screenshots
N/A (backend performance change, no UI change).
### Other Information
- The change is additive and non-destructive: it only adds a secondary index,
with no schema or data semantics change.
- The `AutoMigrateTables` path is idempotent by index name, so re-running the
migration is safe.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
