Add some more indexes to speed up derivation ordered allocation

7 files changed, 44 insertions, 0 deletions

diff --git a/sqitch/pg/deploy/more_indexes_on_builds_and_derivation_inputs.sql b/sqitch/pg/deploy/more_indexes_on_builds_and_derivation_inputs.sql
new file mode 100644
index 0000000..7d248ea
--- /dev/null
+++ b/sqitch/pg/deploy/more_indexes_on_builds_and_derivation_inputs.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-build-coordinator:more_indexes_on_builds_and_derivation_inputs to pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/revert/more_indexes_on_builds_and_derivation_inputs.sql b/sqitch/pg/revert/more_indexes_on_builds_and_derivation_inputs.sql
new file mode 100644
index 0000000..c6a3f47
--- /dev/null
+++ b/sqitch/pg/revert/more_indexes_on_builds_and_derivation_inputs.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:more_indexes_on_builds_and_derivation_inputs from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/pg/verify/more_indexes_on_builds_and_derivation_inputs.sql b/sqitch/pg/verify/more_indexes_on_builds_and_derivation_inputs.sql
new file mode 100644
index 0000000..aee817e
--- /dev/null
+++ b/sqitch/pg/verify/more_indexes_on_builds_and_derivation_inputs.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:more_indexes_on_builds_and_derivation_inputs on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 20d36dc..0946279 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -14,3 +14,4 @@ allocator_related_indexes 2020-04-29T16:59:14Z Christopher Baines <mail@cbaines.
 unprocessed_hook_events 2020-05-08T12:55:28Z Christopher Baines <mail@cbaines.net> # Add a new table unprocessed_hook_events
 derivation_outputs_derivation_name_index 2020-05-10T16:24:05Z Christopher Baines <mail@cbaines.net> # Add an index on derivation_outputs.derivation_name
 build_results_result_index 2020-05-10T17:27:00Z Christopher Baines <mail@cbaines.net> # Add an index on build_results.result
+more_indexes_on_builds_and_derivation_inputs 2020-05-11T08:38:28Z Christopher Baines <mail@cbaines.net> # Add a couple more indexes
diff --git a/sqitch/sqlite/deploy/more_indexes_on_builds_and_derivation_inputs.sql b/sqitch/sqlite/deploy/more_indexes_on_builds_and_derivation_inputs.sql
new file mode 100644
index 0000000..5f2315b
--- /dev/null
+++ b/sqitch/sqlite/deploy/more_indexes_on_builds_and_derivation_inputs.sql
@@ -0,0 +1,8 @@
+-- Deploy guix-build-coordinator:more_indexes_on_builds_and_derivation_inputs to sqlite
+
+BEGIN;
+
+CREATE INDEX derivation_inputs_derivation_output_id ON derivation_inputs (derivation_output_id);
+CREATE INDEX builds_unprocessed ON builds (processed) WHERE processed = 0;
+
+COMMIT;
diff --git a/sqitch/sqlite/revert/more_indexes_on_builds_and_derivation_inputs.sql b/sqitch/sqlite/revert/more_indexes_on_builds_and_derivation_inputs.sql
new file mode 100644
index 0000000..22d6658
--- /dev/null
+++ b/sqitch/sqlite/revert/more_indexes_on_builds_and_derivation_inputs.sql
@@ -0,0 +1,7 @@
+-- Revert guix-build-coordinator:more_indexes_on_builds_and_derivation_inputs from sqlite
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqlite/verify/more_indexes_on_builds_and_derivation_inputs.sql b/sqitch/sqlite/verify/more_indexes_on_builds_and_derivation_inputs.sql
new file mode 100644
index 0000000..b9dcbe1
--- /dev/null
+++ b/sqitch/sqlite/verify/more_indexes_on_builds_and_derivation_inputs.sql
@@ -0,0 +1,7 @@
+-- Verify guix-build-coordinator:more_indexes_on_builds_and_derivation_inputs on sqlite
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;