On Fri, 2018-08-10 at 18:01 +1000, Stewart Smith wrote:
> 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 <stew...@linux.ibm.com>
As before, I'm trusting your leet skillz here in all DB-related things.
I don't have a large enough dataset to validate this properly but I am
seeing a performance improvement in the smaller set I do have.
Reviewed-by: Stephen Finucane <step...@that.guru>
Stephen
> ---
> .../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
>
> diff --git a/patchwork/migrations/0028_add_list_covering_index.py
> b/patchwork/migrations/0028_add_list_covering_index.py
> new file mode 100644
> index 000000000000..65ebaefbead7
> --- /dev/null
> +++ b/patchwork/migrations/0028_add_list_covering_index.py
> @@ -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'),
> + ),
> + ]
> diff --git a/patchwork/models.py b/patchwork/models.py
> index d2389cfdad29..15224ad69cfa 100644
> --- a/patchwork/models.py
> +++ b/patchwork/models.py
> @@ -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
_______________________________________________
Patchwork mailing list
Patchwork@lists.ozlabs.org
https://lists.ozlabs.org/listinfo/patchwork
