fix: add index for GetProvisionerLogsByIDBetween

coder · coadler · Nov 16, 2022 · Nov 10, 2022 · Nov 15, 2022 · Nov 15, 2022
commit 453731ba0132fb680df906d6d10572744759b036
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000076_job_logs_job_id_id_index.down.sql b/coderd/database/migrations/000076_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/000076_job_logs_job_id_id_index.up.sql b/coderd/database/migrations/000076_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 @@
		CREATE INDEX provisioner_job_logs_id_job_id_idx ON provisioner_job_logs USING btree (job_id, id ASC);