[layerindex-web][PATCH] Add indexes to speed up recipe, patch and bbappend lookups

[layerindex-web][PATCH] Add indexes to speed up recipe, patch and bbappend lookups

[Michael Halstead]

Slow query digest (events_statements_summary_by_digest) showed the
hottest queries on the site filtering by Recipe.pn and joining
BBAppend/Patch/RecipeUpgrade with no supporting index. Add composite
indexes for the actual filter/join patterns instead of the FK columns,
which Django already indexes by default. Also index SiteNotice.expires,
which is checked on every page load via the notices context processor.

Signed-off-by: Michael Halstead <mhalstead@linuxfoundation.org>
Co-authored-by: Claude Opus 5 <noreply@anthropic.com>
---
 .../migrations/0053_add_query_indexes.py      | 35 +++++++++++++++++++
 .../0054_alter_sitenotice_expires.py          | 23 ++++++++++++
 layerindex/models.py                          | 14 +++++++-
 rrs/migrations/0031_add_query_indexes.py      | 24 +++++++++++++
 rrs/models.py                                 | 10 ++++++
 5 files changed, 105 insertions(+), 1 deletion(-)
 create mode 100644 layerindex/migrations/0053_add_query_indexes.py
 create mode 100644 layerindex/migrations/0054_alter_sitenotice_expires.py
 create mode 100644 rrs/migrations/0031_add_query_indexes.py

diff --git a/layerindex/migrations/0053_add_query_indexes.py b/layerindex/migrations/0053_add_query_indexes.py
new file mode 100644
index 0000000..0aa6ee1
--- /dev/null
+++ b/layerindex/migrations/0053_add_query_indexes.py
@@ -0,0 +1,35 @@
+# Generated by Django 6.1 on 2026-08-11 19:35
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ("layerindex", "0052_source_path"),
+    ]
+
+    operations = [
+        migrations.AddIndex(
+            model_name="bbappend",
+            index=models.Index(
+                fields=["layerbranch", "filename"], name="bbappend_lb_filename"
+            ),
+        ),
+        migrations.AddIndex(
+            model_name="patch",
+            index=models.Index(fields=["recipe", "status"], name="patch_recipe_status"),
+        ),
+        migrations.AddIndex(
+            model_name="recipe",
+            index=models.Index(
+                fields=["layerbranch", "pn"], name="recipe_layerbranch_pn"
+            ),
+        ),
+        migrations.AddIndex(
+            model_name="recipe",
+            index=models.Index(
+                fields=["pn", "layerbranch"], name="recipe_pn_layerbranch"
+            ),
+        ),
+    ]
diff --git a/layerindex/migrations/0054_alter_sitenotice_expires.py b/layerindex/migrations/0054_alter_sitenotice_expires.py
new file mode 100644
index 0000000..ea826a0
--- /dev/null
+++ b/layerindex/migrations/0054_alter_sitenotice_expires.py
@@ -0,0 +1,23 @@
+# Generated by Django 6.1 on 2026-08-11 21:07
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ("layerindex", "0053_add_query_indexes"),
+    ]
+
+    operations = [
+        migrations.AlterField(
+            model_name="sitenotice",
+            name="expires",
+            field=models.DateTimeField(
+                blank=True,
+                db_index=True,
+                help_text="Optional date/time when this notice will stop showing",
+                null=True,
+            ),
+        ),
+    ]
diff --git a/layerindex/models.py b/layerindex/models.py
index eb7d8bf..1a2ec60 100644
--- a/layerindex/models.py
+++ b/layerindex/models.py
@@ -478,6 +478,12 @@ class Recipe(models.Model):
     configopts = models.CharField(max_length=4096, blank=True)
     srcrev = models.CharField(max_length=64, blank=True)
 
+    class Meta:
+        indexes = [
+            models.Index(fields=['layerbranch', 'pn'], name='recipe_layerbranch_pn'),
+            models.Index(fields=['pn', 'layerbranch'], name='recipe_pn_layerbranch'),
+        ]
+
     def vcs_web_url(self):
         url = self.layerbranch.file_url(os.path.join(self.filepath, self.filename))
         return url or ''
@@ -582,6 +588,9 @@ class Patch(models.Model):
     class Meta:
         verbose_name_plural = 'Patches'
         ordering = ['recipe', 'apply_order']
+        indexes = [
+            models.Index(fields=['recipe', 'status'], name='patch_recipe_status'),
+        ]
 
     def vcs_web_url(self):
         url = self.recipe.layerbranch.file_url(self.path)
@@ -769,6 +778,9 @@ class BBAppend(models.Model):
 
     class Meta:
         verbose_name = "Append"
+        indexes = [
+            models.Index(fields=['layerbranch', 'filename'], name='bbappend_lb_filename'),
+        ]
 
     def vcs_web_url(self):
         url = self.layerbranch.file_url(os.path.join(self.filepath, self.filename))
@@ -910,7 +922,7 @@ class SiteNotice(models.Model):
     text = models.TextField(help_text='Text to show in the notice. A limited subset of HTML is supported for formatting.')
     level = models.CharField(max_length=1, choices=NOTICE_LEVEL_CHOICES, default='I', help_text='Level of notice to display')
     disabled = models.BooleanField('Disabled', default=False, help_text='Use to temporarily disable this notice')
-    expires = models.DateTimeField(blank=True, null=True, help_text='Optional date/time when this notice will stop showing')
+    expires = models.DateTimeField(blank=True, null=True, db_index=True, help_text='Optional date/time when this notice will stop showing')
 
     def __str__(self):
         prefix = ''
diff --git a/rrs/migrations/0031_add_query_indexes.py b/rrs/migrations/0031_add_query_indexes.py
new file mode 100644
index 0000000..91a6d5c
--- /dev/null
+++ b/rrs/migrations/0031_add_query_indexes.py
@@ -0,0 +1,24 @@
+# Generated by Django 6.1 on 2026-08-11 19:35
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ("layerindex", "0053_add_query_indexes"),
+        ("rrs", "0030_alter_recipeupgrade_maintainer"),
+    ]
+
+    operations = [
+        migrations.AddIndex(
+            model_name="recipesymbol",
+            index=models.Index(fields=["layerbranch", "pn"], name="recipesymbol_lb_pn"),
+        ),
+        migrations.AddIndex(
+            model_name="recipeupgrade",
+            index=models.Index(
+                fields=["recipesymbol", "commit_date"], name="recipeupgrade_rs_date"
+            ),
+        ),
+    ]
diff --git a/rrs/models.py b/rrs/models.py
index df30995..e1de689 100644
--- a/rrs/models.py
+++ b/rrs/models.py
@@ -214,6 +214,11 @@ class RecipeSymbol(models.Model):
             rsym.save()
         return rsym
 
+    class Meta:
+        indexes = [
+            models.Index(fields=['layerbranch', 'pn'], name='recipesymbol_lb_pn'),
+        ]
+
     def __str__(self):
         return "%s: %s" % (str(self.layerbranch), self.pn)
 
@@ -509,6 +514,11 @@ class RecipeUpgrade(models.Model):
     group = models.ForeignKey(RecipeUpgradeGroup, blank=True, null=True, on_delete=models.SET_NULL)
     prev_version = models.CharField(max_length=100, blank=True)
 
+    class Meta:
+        indexes = [
+            models.Index(fields=['recipesymbol', 'commit_date'], name='recipeupgrade_rs_date'),
+        ]
+
     @staticmethod
     def get_by_recipe_and_date(recipe, end_date):
         ru = RecipeUpgrade.objects.filter(recipesymbol__pn=recipe.pn,
-- 
2.55.0