Long running queries on p_ci_builds table in postgresql after upgrade from 16.3.7 to 16.7.4

After upgrading gitlab from 16.3.7 to 16.7.4 I noticed a significant increase of the load on the postgresql database.

It’s continuously these 2 queries that keep a high load on the database:

/application:sidekiq,correlation_id:a06ae1adb93976a060b17e227ecfab2b,jid:d61b72f686c4d227ad863ab1,endpoint_id:Ci::UnlockPipelinesInQueueWorker,db_config_name:ci/ SELECT “p_ci_builds”.“id” FROM “p_ci_builds” WHERE “p_ci_builds”.“type” = ‘Ci::Build’ AND “p_ci_builds”.“commit_id” = 791881 AND “p_ci_builds”.“partition_id” = 100 ORDER BY “p_ci_builds”.“id” ASC LIMIT 1;

/application:sidekiq,correlation_id:ec71b25f845c6c79704665cef427cc0c,jid:a952ab8b3969f763ab3c4272,endpoint_id:Ci::ArchiveTraceWorker,db_config_name:ci/ SELECT “ci_build_trace_metadata”.* FROM “ci_build_trace_metadata” WHERE “ci_build_trace_metadata”.“build_id” = 17172549 AND ci_build_trace_metadata".“partition_id” = 100 LIMIT 1;

/application:sidekiq,correlation_id:93ac6a309af4f5dbacb82de129a4c175,jid:e7390bdb71cfe06cd644f8bc,endpoint_id:Ci::UnlockPipelinesInQueueWorker,db_config_name:ci/ SELECT “p_ci_builds”.“id” FROM “p_ci_builds” WHERE “p_ci_builds”.“type” = ‘Ci::Build’ AND “p_ci_builds”.“commit_id” = 791881 AND “p_ci_builds”.“partition_id” = 100 ORDER BY “p_ci_builds”.“id” ASC LIMIT 1;

very strange:
executes immediately when a limit of 2 is used and always hangs with a limit of 1.
Using PostgreSQL 13.12

Found similar post:

But in my case the query keeps running

Performing an “ANALYZE” solved my issue.

1 Like