Bug: Elevated postgres CPU utilization after enabling USE_ARTIFACT_LISTING_API_V2

[ajaydevtron]

📜 Description

he PostgreSQL CPU utilization has reached 100% (4 cores) after enabling the USE_ARTIFACT_LISTING_API_V2 flag in the orchestrator configmap.

Top SQL after debugging the utilisation .

SELECT DISTINCT(ci_artifact.id) , (SELECT COUNT(DISTINCT ci_artifact.id) as total_count from ci_artifact LEFT JOIN cd_workflow ON ci_artifact.id = cd_workflow.ci_artifact_id LEFT JOIN cd_workflow_runner ON cd_workflow_runner.cd_workflow_id=cd_workflow.id Where (((cd_workflow_runner.id in (select MAX(cd_workflow_runner.id) OVER (PARTITION BY cd_workflow.ci_artifact_id) FROM cd_workflow_runner inner join cd_workflow on cd_workflow.id=cd_workflow_runner.cd_workflow_id)) AND ((cd_workflow.pipeline

Currently, we have disabled it from the configmap.

PS:- We are observing inconsistent status when searching for the same image using the its tag, the copy container image plugin, is not working as expected and fetch more Image functionality is also affected.

👟 Reproduction steps

1. Enabled USE_ARTIFACT_LISTING_API_V2
2. Try to click on select image for pipeline.

👍 Expected behavior

We shouldn’t spike on postgres cpu utilization.

👎 Actual Behavior

Cpu utilisation should be normal.

☸ Kubernetes version

1.23

Cloud provider

AWS

🌍 Browser

Chrome

🧱 Your Environment

No response

✅ Proposed Solution

No response

👀 Have you spent some time to check if this issue has been raised before?

• I checked and didn't find any similar issue

🏢 Have you read the Code of Conduct?

• I have read the Code of Conduct

AB#7348