Improve query performance

kylecarbs · kylecarbs · commit 1ab03e2756eb · 2023-06-17T17:41:16.000Z
diff --git a/cli/testdata/coder_list_--output_json.golden b/cli/testdata/coder_list_--output_json.golden
@@ -35,7 +35,9 @@
         "file_id": "[workspace build file ID]",
         "tags": {
           "scope": "organization"
-        }
+        },
+        "queue_position": 1,
+        "queue_size": 0
       },
       "reason": "initiator",
       "resources": [],
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/provisionerjobs.sql b/coderd/database/queries/provisionerjobs.sql
@@ -48,23 +48,33 @@ WHERE
 	id = ANY(@ids :: uuid [ ]);
 
 -- name: GetProvisionerJobsByIDsWithQueuePosition :many
+WITH unstarted_jobs AS (
+    SELECT
+        id, created_at
+    FROM
+        provisioner_jobs
+    WHERE
+        started_at IS NULL
+),
+queue_position AS (
+    SELECT
+        id,
+        ROW_NUMBER() OVER (ORDER BY created_at) AS queue_position
+    FROM
+        unstarted_jobs
+),
+queue_size AS (
+		SELECT COUNT(*) as count FROM unstarted_jobs
+)
 SELECT
-	sqlc.embed(pj),
-	COALESCE(a.queue_position, 0) AS queue_position,
-	COALESCE(a.queue_size, 0) AS queue_size
+		sqlc.embed(pj),
+    COALESCE(qp.queue_position, 0) AS queue_position,
+    COALESCE(qs.count, 0) AS queue_size
 FROM
-	provisioner_jobs pj
-LEFT JOIN (
-	SELECT
-		id,
-		ROW_NUMBER() OVER (ORDER BY created_at) AS queue_position,
-		COUNT(*) OVER (PARTITION BY started_at IS NULL) AS queue_size
-	FROM
-		provisioner_jobs
-	WHERE
-		started_at IS NULL
-	GROUP BY id
-) a ON a.id = pj.id
+    provisioner_jobs pj
+LEFT JOIN
+    queue_position qp ON qp.id = pj.id
+LEFT JOIN queue_size qs ON TRUE
 WHERE pj.id = ANY(@ids :: uuid [ ]);
 
 -- name: GetProvisionerJobsCreatedAfter :many
