| Message ID | 20180810080106.10714-9-stewart@linux.ibm.com |
|---|---|
| State | Accepted |
| Headers | show |
| Series | Performance for ALL THE THINGS! | expand |
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):
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):
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