Skip to content

ci_builds select -- slow query

This query:

SELECT "ci_builds".* FROM "ci_builds" INNER JOIN "projects" ON "projects"."id" = "ci_builds"."project_id" AND "projects"."pending_delete" = ? WHERE "ci_builds"."type" IN (?) AND "ci_builds"."status" = ? AND (ci_builds.updated_at < ?) ORDER BY "ci_builds"."id" ASC LIMIT ?;

Appears to be called 3 times per hour for and ends up running for ~30 seconds each time.

While this isn't a huge issue given that it gets called infrequently and probably from a background process, I wonder if the ORDER BY in this query is really necessary, having it result in an in-order index scan across the ci_builds primary key index which probably goes through pretty much the entire table.

Edited by Stan Hu