new file mode 100644
@@ -0,0 +1,19 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.15 on 2018-08-09 17:24
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+ dependencies = [
+ ('patchwork', '0027_add_comment_date_index'),
+ ]
+
+ operations = [
+ migrations.AddIndex(
+ model_name='patch',
+ index=models.Index(fields=['archived', 'patch_project', 'state', 'delegate'], name='patch_list_covering_idx'),
+ ),
+ ]
@@ -598,6 +598,12 @@ class Patch(SeriesMixin, Submission):
if django.VERSION >= (1, 10):
base_manager_name = 'objects'
+ indexes = [
+ # This is a covering index for the /list/ query
+ models.Index(fields=['archived','patch_project','state','delegate'],
+ name='patch_list_covering_idx'),
+ ]
+
class Comment(EmailMixin, models.Model):
# parent
In constructing the list of patches for a project, there are two main queries that are executed: 1) get a count() of how many patches there are 2) Get the page of results being displayed In a test dataset of ~11500 LKML patches and ~4000 others, the existing code would take around 585ms and 858ms with a cold cache and 28ms and 198ms for a warm cache. By adding a covering index, we get down to 4ms and 255ms for a cold cache, and 4ms and 143ms for a warm cache! Additionally, when there's a lot of archived or accepted patches (I used ~11000 archived out of the 15000 total in my test set) the query time goes from 28ms and 72ms down to 2ms and 33-40ms! Signed-off-by: Stewart Smith <stewart@linux.ibm.com> --- .../0028_add_list_covering_index.py | 19 +++++++++++++++++++ patchwork/models.py | 6 ++++++ 2 files changed, 25 insertions(+) create mode 100644 patchwork/migrations/0028_add_list_covering_index.py