[no subject]

[Mathieu Othacehe]

branch: master
commit f20309c559d0b09d69781885d5a9eb6449975069
Author: Mathieu Othacehe <othacehe@gnu.org>
AuthorDate: Wed Apr 21 09:51:14 2021 +0200

    Add indexes on Builds id foreign keys.
    
    This considerably speeds up cascade deleting.
    
    * src/sql/upgrade-9.sql: New file.
    * Makefile.am (dist_sql_DATA): Add it.
    * src/schema.sql (Jobs_build, BuildProducts_build, Notifications_build): New
    indexes.
---
 Makefile.am           | 4 +++-
 src/schema.sql        | 5 +++++
 src/sql/upgrade-9.sql | 7 +++++++
 3 files changed, 15 insertions(+), 1 deletion(-)

diff --git a/Makefile.am b/Makefile.am
index 74ba573..6469127 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -94,7 +94,9 @@ dist_sql_DATA =                               \
   src/sql/upgrade-4.sql                                \
   src/sql/upgrade-5.sql                                \
   src/sql/upgrade-6.sql                                \
-  src/sql/upgrade-7.sql
+  src/sql/upgrade-7.sql                                \
+  src/sql/upgrade-8.sql                                \
+  src/sql/upgrade-9.sql
 
 dist_css_DATA =                                        \
   src/static/css/choices.min.css               \
diff --git a/src/schema.sql b/src/schema.sql
index 84740a6..181d8dd 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -133,10 +133,15 @@ CREATE INDEX Builds_weather_evaluation ON Builds 
(weather, evaluation);
 
 CREATE INDEX Jobs_name ON Jobs (name);
 CREATE INDEX Jobs_system_status ON Jobs (system, status);
+CREATE INDEX Jobs_build ON Jobs (build); --speeds up delete cascade.
 
 CREATE INDEX Evaluations_status_index ON Evaluations (id, status);
 CREATE INDEX Evaluations_specification_index ON Evaluations (specification, id 
DESC);
 
 CREATE INDEX Outputs_derivation_index ON Outputs (derivation);
 
+CREATE INDEX BuildProducts_build ON BuildProducts(build); --speeds up delete 
cascade.
+
+CREATE INDEX Notifications_build ON Notifications(build); --speeds up delete 
cascade.
+
 COMMIT;
diff --git a/src/sql/upgrade-9.sql b/src/sql/upgrade-9.sql
new file mode 100644
index 0000000..827e492
--- /dev/null
+++ b/src/sql/upgrade-9.sql
@@ -0,0 +1,7 @@
+BEGIN TRANSACTION;
+
+CREATE INDEX Jobs_build ON Jobs (build); --speeds up delete cascade.
+CREATE INDEX BuildProducts_build ON BuildProducts(build); --speeds up delete 
cascade.
+CREATE INDEX Notifications_build ON Notifications(build); --speeds up delete 
cascade.
+
+COMMIT;