Merge pull request #5366 from trofi/speedup-ca-query

ca-specific-schema.sql: add index on RealisationsRefs(referrer)

2 files changed, 17 insertions, 1 deletions

diff --git a/src/libstore/ca-specific-schema.sql b/src/libstore/ca-specific-schema.sql
index 08af0cc1f..64cc97fde 100644
--- a/src/libstore/ca-specific-schema.sql
+++ b/src/libstore/ca-specific-schema.sql
@@ -19,3 +19,8 @@ create table if not exists RealisationsRefs (
     foreign key (referrer) references Realisations(id) on delete cascade,
     foreign key (realisationReference) references Realisations(id) on delete restrict
 );
+
+-- used by QueryRealisationReferences
+create index if not exists IndexRealisationsRefs on RealisationsRefs(referrer);
+-- used by cascade deletion when ValidPaths is deleted
+create index if not exists IndexRealisationsRefsOnOutputPath on Realisations(outputPath);
diff --git a/src/libstore/local-store.cc b/src/libstore/local-store.cc
index 79011b522..1b8e77ead 100644
--- a/src/libstore/local-store.cc
+++ b/src/libstore/local-store.cc
@@ -80,7 +80,7 @@ int getSchema(Path schemaPath)
 
 void migrateCASchema(SQLite& db, Path schemaPath, AutoCloseFD& lockFd)
 {
-    const int nixCASchemaVersion = 2;
+    const int nixCASchemaVersion = 3;
     int curCASchema = getSchema(schemaPath);
     if (curCASchema != nixCASchemaVersion) {
         if (curCASchema > nixCASchemaVersion) {
@@ -131,6 +131,17 @@ void migrateCASchema(SQLite& db, Path schemaPath, AutoCloseFD& lockFd)
             txn.commit();
         }
 
+        if (curCASchema < 3) {
+            SQLiteTxn txn(db);
+            // Apply new indices added in this schema update.
+            db.exec(R"(
+                -- used by QueryRealisationReferences
+                create index if not exists IndexRealisationsRefs on RealisationsRefs(referrer);
+                -- used by cascade deletion when ValidPaths is deleted
+                create index if not exists IndexRealisationsRefsOnOutputPath on Realisations(outputPath);
+            )");
+            txn.commit();
+        }
         writeFile(schemaPath, fmt("%d", nixCASchemaVersion));
         lockFile(lockFd.get(), ltRead, true);
     }