[08/11] Add covering index to patchwork_submissions for /list/ queries

Message ID 20180810080106.10714-9-stewart@linux.ibm.com
State Accepted
Headers show
Series
  • Performance for ALL THE THINGS!
Related show

Commit Message

Stewart Smith Aug. 10, 2018, 8:01 a.m.
This gets PostgreSQL to generate *much* better query plans, gaining us
about two orders of magnitude in performance on the /list/ query for the
worst state project on the patchwork.ozlabs.org instance (qemu-devel).

Signed-off-by: Stewart Smith <stewart@linux.ibm.com>
---
 .../0029_add_submission_covering_index.py     | 19 +++++++++++++++++++
 patchwork/models.py                           |  8 ++++++++
 2 files changed, 27 insertions(+)
 create mode 100644 patchwork/migrations/0029_add_submission_covering_index.py

Comments

Stephen Finucane Aug. 31, 2018, 2:13 p.m. | #1
On Fri, 2018-08-10 at 18:01 +1000, Stewart Smith wrote:
> This gets PostgreSQL to generate *much* better query plans, gaining us
> about two orders of magnitude in performance on the /list/ query for the
> worst state project on the patchwork.ozlabs.org instance (qemu-devel).
> 
> Signed-off-by: Stewart Smith <stewart@linux.ibm.com>

Another easy win that I'm delighted _someone else_ was able to fix :)

Reviewed-by: Stephen Finucane <stephen@that.guru>

Stephen

> ---
>  .../0029_add_submission_covering_index.py     | 19 +++++++++++++++++++
>  patchwork/models.py                           |  8 ++++++++
>  2 files changed, 27 insertions(+)
>  create mode 100644 patchwork/migrations/0029_add_submission_covering_index.py
> 
> diff --git a/patchwork/migrations/0029_add_submission_covering_index.py b/patchwork/migrations/0029_add_submission_covering_index.py
> new file mode 100644
> index 000000000000..064f2ac4ae9d
> --- /dev/null
> +++ b/patchwork/migrations/0029_add_submission_covering_index.py
> @@ -0,0 +1,19 @@
> +# -*- coding: utf-8 -*-
> +# Generated by Django 1.11.15 on 2018-08-10 16:15
> +from __future__ import unicode_literals
> +
> +from django.db import migrations, models
> +
> +
> +class Migration(migrations.Migration):
> +
> +    dependencies = [
> +        ('patchwork', '0028_add_list_covering_index'),
> +    ]
> +
> +    operations = [
> +        migrations.AddIndex(
> +            model_name='submission',
> +            index=models.Index(fields=['date', 'project', 'submitter', 'name'], name='submission_covering_idx'),
> +        ),
> +    ]
> diff --git a/patchwork/models.py b/patchwork/models.py
> index d356a6379ac3..7a8d363c4652 100644
> --- a/patchwork/models.py
> +++ b/patchwork/models.py
> @@ -389,6 +389,14 @@ class Submission(FilenameMixin, EmailMixin, models.Model):
>      class Meta:
>          ordering = ['date']
>          unique_together = [('msgid', 'project')]
> +        indexes = [
> +            # This is a covering index for the /list/ query
> +            # Like what we have for Patch, but used for displaying what we want
> +            # rather than for working out the count
> +            # (of course, this all depends on the SQL optimiser of your db engine)
> +            models.Index(fields=['date','project','submitter','name'],
> +                         name='submission_covering_idx'),
> +            ]
>  
>  
>  class SeriesMixin(object):

Patch

diff --git a/patchwork/migrations/0029_add_submission_covering_index.py b/patchwork/migrations/0029_add_submission_covering_index.py
new file mode 100644
index 000000000000..064f2ac4ae9d
--- /dev/null
+++ b/patchwork/migrations/0029_add_submission_covering_index.py
@@ -0,0 +1,19 @@ 
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.15 on 2018-08-10 16:15
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('patchwork', '0028_add_list_covering_index'),
+    ]
+
+    operations = [
+        migrations.AddIndex(
+            model_name='submission',
+            index=models.Index(fields=['date', 'project', 'submitter', 'name'], name='submission_covering_idx'),
+        ),
+    ]
diff --git a/patchwork/models.py b/patchwork/models.py
index d356a6379ac3..7a8d363c4652 100644
--- a/patchwork/models.py
+++ b/patchwork/models.py
@@ -389,6 +389,14 @@  class Submission(FilenameMixin, EmailMixin, models.Model):
     class Meta:
         ordering = ['date']
         unique_together = [('msgid', 'project')]
+        indexes = [
+            # This is a covering index for the /list/ query
+            # Like what we have for Patch, but used for displaying what we want
+            # rather than for working out the count
+            # (of course, this all depends on the SQL optimiser of your db engine)
+            models.Index(fields=['date','project','submitter','name'],
+                         name='submission_covering_idx'),
+            ]
 
 
 class SeriesMixin(object):