Add indices to help the series list page.  The project is almost always
used as a search key, either directly or through a parent project, so
include both a variant with the project and one without.  The sorting
keys are left last, while filter keys should come first.

This reduces the SELECT COUNT(*) query in the series list from 180 ms
to 5 ms on a full dump from a few weeks ago.

Signed-off-by: Paolo Bonzini <pbonzini@redhat.com>
---
 api/migrations/0037_auto_20181031_1439.py | 19 +++++++++++++++++++
 api/models.py                             |  4 ++++
 tests/test_import.py                      |  2 +-
 3 files changed, 24 insertions(+), 1 deletion(-)
 create mode 100644 api/migrations/0037_auto_20181031_1439.py

diff --git a/api/migrations/0037_auto_20181031_1439.py b/api/migrations/0037_auto_20181031_1439.py
new file mode 100644
index 0000000..ca75e37
--- /dev/null
+++ b/api/migrations/0037_auto_20181031_1439.py
@@ -0,0 +1,19 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.15 on 2018-10-31 14:39
+from __future__ import unicode_literals
+
+from django.db import migrations
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('api', '0036_populate_message_tags'),
+    ]
+
+    operations = [
+        migrations.AlterIndexTogether(
+            name='message',
+            index_together=set([('is_series_head', 'project', 'date'), ('is_series_head', 'date'), ('is_series_head', 'last_reply_date'), ('is_series_head', 'project', 'last_reply_date')]),
+        ),
+    ]
diff --git a/api/models.py b/api/models.py
index ab0bd06..09758f6 100644
--- a/api/models.py
+++ b/api/models.py
@@ -714,6 +714,10 @@ class Message(models.Model):
 
     class Meta:
         unique_together = ('project', 'message_id',)
+        index_together = [('is_series_head', 'project', 'last_reply_date'),
+                          ('is_series_head', 'project', 'date'),
+                          ('is_series_head', 'last_reply_date'),
+                          ('is_series_head', 'date')]
 
 class MessageResult(Result):
     message = models.ForeignKey(Message, related_name='results')
diff --git a/tests/test_import.py b/tests/test_import.py
index 5693d7e..b6e2d73 100755
--- a/tests/test_import.py
+++ b/tests/test_import.py
@@ -91,7 +91,7 @@ class ImportTest(PatchewTestCase):
         self.assertTrue(s.project.name, sp.name)
 
         self.cli_import("0020-libvirt.mbox.gz")
-        subj2 = subj + '\n[libvirt]  [PATCH v2] vcpupin: add clear feature'
+        subj2 = '[libvirt]  [PATCH v2] vcpupin: add clear feature\n' + subj
         self.check_cli(["search", "project:Libvirt"], stdout=subj2)
         self.check_cli(["search", "project:Libvirt-python"], stdout=subj)
 
-- 
2.17.1

_______________________________________________
Patchew-devel mailing list
Patchew-devel@redhat.com
https://www.redhat.com/mailman/listinfo/patchew-devel

On 31/10/2018 15:59, Paolo Bonzini wrote:
> Add indices to help the series list page.  The project is almost always
> used as a search key, either directly or through a parent project, so
> include both a variant with the project and one without.  The sorting
> keys are left last, while filter keys should come first.
> 
> This reduces the SELECT COUNT(*) query in the series list from 180 ms
> to 5 ms on a full dump from a few weeks ago.
> 
> Signed-off-by: Paolo Bonzini <pbonzini@redhat.com>
> ---
>  api/migrations/0037_auto_20181031_1439.py | 19 +++++++++++++++++++
>  api/models.py                             |  4 ++++
>  tests/test_import.py                      |  2 +-
>  3 files changed, 24 insertions(+), 1 deletion(-)
>  create mode 100644 api/migrations/0037_auto_20181031_1439.py
> 
> diff --git a/api/migrations/0037_auto_20181031_1439.py b/api/migrations/0037_auto_20181031_1439.py
> new file mode 100644
> index 0000000..ca75e37
> --- /dev/null
> +++ b/api/migrations/0037_auto_20181031_1439.py
> @@ -0,0 +1,19 @@
> +# -*- coding: utf-8 -*-
> +# Generated by Django 1.11.15 on 2018-10-31 14:39
> +from __future__ import unicode_literals
> +
> +from django.db import migrations
> +
> +
> +class Migration(migrations.Migration):
> +
> +    dependencies = [
> +        ('api', '0036_populate_message_tags'),
> +    ]
> +
> +    operations = [
> +        migrations.AlterIndexTogether(
> +            name='message',
> +            index_together=set([('is_series_head', 'project', 'date'), ('is_series_head', 'date'), ('is_series_head', 'last_reply_date'), ('is_series_head', 'project', 'last_reply_date')]),
> +        ),
> +    ]
> diff --git a/api/models.py b/api/models.py
> index ab0bd06..09758f6 100644
> --- a/api/models.py
> +++ b/api/models.py
> @@ -714,6 +714,10 @@ class Message(models.Model):
>  
>      class Meta:
>          unique_together = ('project', 'message_id',)
> +        index_together = [('is_series_head', 'project', 'last_reply_date'),
> +                          ('is_series_head', 'project', 'date'),
> +                          ('is_series_head', 'last_reply_date'),
> +                          ('is_series_head', 'date')]
>  
>  class MessageResult(Result):
>      message = models.ForeignKey(Message, related_name='results')
> diff --git a/tests/test_import.py b/tests/test_import.py
> index 5693d7e..b6e2d73 100755
> --- a/tests/test_import.py
> +++ b/tests/test_import.py
> @@ -91,7 +91,7 @@ class ImportTest(PatchewTestCase):
>          self.assertTrue(s.project.name, sp.name)
>  
>          self.cli_import("0020-libvirt.mbox.gz")
> -        subj2 = subj + '\n[libvirt]  [PATCH v2] vcpupin: add clear feature'
> +        subj2 = '[libvirt]  [PATCH v2] vcpupin: add clear feature\n' + subj
>          self.check_cli(["search", "project:Libvirt"], stdout=subj2)
>          self.check_cli(["search", "project:Libvirt-python"], stdout=subj)

Nope, either combination can happen depending on the hashing salt.  Will
send v3 when I have time, the solution is to get the stdout from
check_cli, sort the lines, and compare against those.

Paolo

_______________________________________________
Patchew-devel mailing list
Patchew-devel@redhat.com
https://www.redhat.com/mailman/listinfo/patchew-devel