fix: add index for GetProvisionerLogsByIDBetween (#5020)

coadler · web-flow · commit e7f11926141f · 2022-11-16T14:32:29.000-06:00
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000077_job_logs_job_id_id_index.down.sql b/coderd/database/migrations/000077_job_logs_job_id_id_index.down.sql
@@ -0,0 +1 @@
+DROP INDEX provisioner_job_logs_id_job_id_idx;
diff --git a/coderd/database/migrations/000077_job_logs_job_id_id_index.up.sql b/coderd/database/migrations/000077_job_logs_job_id_id_index.up.sql
@@ -0,0 +1 @@
+CREATE INDEX provisioner_job_logs_id_job_id_idx ON provisioner_job_logs USING btree (job_id, id ASC);
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/provisionerjoblogs.sql b/coderd/database/queries/provisionerjoblogs.sql
@@ -8,7 +8,7 @@ WHERE
 	AND (
 		id > @created_after
 		OR id < @created_before
-	) ORDER BY id;
+	) ORDER BY id ASC;
 
 -- name: InsertProvisionerJobLogs :many
 INSERT INTO
diff --git a/coderd/database/queries/provisionerjobs.sql b/coderd/database/queries/provisionerjobs.sql
@@ -23,9 +23,9 @@ WHERE
 			AND nested.completed_at IS NULL
 			AND nested.provisioner = ANY(@types :: provisioner_type [ ])
 		ORDER BY
-			nested.created_at FOR
-		UPDATE
-			SKIP LOCKED
+			nested.created_at
+		FOR UPDATE
+		SKIP LOCKED
 		LIMIT
 			1
 	) RETURNING *;

Original file line number	Diff line number	Diff line change
`@@ -0,0 +1 @@`
	`1`	`+DROP INDEX provisioner_job_logs_id_job_id_idx;`
Original file line number	Diff line number	Diff line change
`@@ -0,0 +1 @@`
	`1`	`+CREATE INDEX provisioner_job_logs_id_job_id_idx ON provisioner_job_logs USING btree (job_id, id ASC);`