[01/11] Improve patch listing performance (~3x)

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

Commit Message

Stewart Smith Aug. 10, 2018, 8 a.m.
There's two main bits that are really expensive when composing the list
of patches for a project: the query getting the list, and the query
finding the series for each patch.

If we look at the query getting the list, it gets a lot of unnesseccary
fields such as 'headers' and 'content', even though we tell Django not
to. It turns out that Django seems to ignore the Submission relationship
and I have no idea how to force it to ignore that thing (defer doesn't
work) but if we go only, then it works okay.

From my import of ~8000 messages for a few projects, my laptop query
time (MySQL, as setup by whatever the docker-compose things do) goes
from:

http://localhost:8000/project/linux-kernel/list/
FROM:
342ms SQL queries cold cache, 268ms warm cache
TO:
118ms SQL queries cold cache, 88ms warm cache

Which is... non trivial to say the least.

The big jump is the patches.only change, and the removal of ordering
on the patchseries takes a further 10ms off. For some strange reason, it
seems rather hard to tell Django that you don't care what order the
results come back in for that query (if we do, then the db server has to
do a sort rather than just return each row)

Signed-off-by: Stewart Smith <stewart@linux.ibm.com>
---
 patchwork/models.py         | 1 -
 patchwork/views/__init__.py | 2 ++
 2 files changed, 2 insertions(+), 1 deletion(-)

Comments

Daniel Axtens Aug. 10, 2018, 6:28 p.m. | #1
Stewart Smith <stewart@linux.ibm.com> writes:

> There's two main bits that are really expensive when composing the list
> of patches for a project: the query getting the list, and the query
> finding the series for each patch.
>
> If we look at the query getting the list, it gets a lot of unnesseccary
> fields such as 'headers' and 'content', even though we tell Django not
> to. It turns out that Django seems to ignore the Submission relationship
> and I have no idea how to force it to ignore that thing (defer doesn't
> work) but if we go only, then it works okay.
>
> From my import of ~8000 messages for a few projects, my laptop query
> time (MySQL, as setup by whatever the docker-compose things do) goes
> from:
>
> http://localhost:8000/project/linux-kernel/list/
> FROM:
> 342ms SQL queries cold cache, 268ms warm cache
> TO:
> 118ms SQL queries cold cache, 88ms warm cache
>
> Which is... non trivial to say the least.
>
> The big jump is the patches.only change, and the removal of ordering
> on the patchseries takes a further 10ms off. For some strange reason, it
> seems rather hard to tell Django that you don't care what order the
> results come back in for that query (if we do, then the db server has to
> do a sort rather than just return each row)

Thanks Stewart! It's great to get some real DB experience - feel free to
hang around! :)

So, further to our conversation with Konstantin, I tested this against
Django 2.0. It still saves us some time - it means we no longer load the
following fields:

`patchwork_submission`.`id`, `patchwork_submission`.`msgid`,
`patchwork_patch`.`commit_ref`, `patchwork_patch`.`pull_url`,
`patchwork_patch`.`archived`, `patchwork_patch`.`hash`,
`patchwork_patch`.`patch_project_id`, 

This obviously saves the db some work and communication overhead.

I'm a little nervous that this will slightly complicate some of the
further denormalisation but I think that's probably a price worth paying
unless Stephen objects.

I do still want to test the 'ordering' change a bit more before
committing it though.

Regards,
Daniel
>
> Signed-off-by: Stewart Smith <stewart@linux.ibm.com>
> ---
>  patchwork/models.py         | 1 -
>  patchwork/views/__init__.py | 2 ++
>  2 files changed, 2 insertions(+), 1 deletion(-)
>
> diff --git a/patchwork/models.py b/patchwork/models.py
> index 6268f5b72e3c..d2b88fc48c91 100644
> --- a/patchwork/models.py
> +++ b/patchwork/models.py
> @@ -747,7 +747,6 @@ class Series(FilenameMixin, models.Model):
>          return self.name if self.name else 'Untitled series #%d' % self.id
>  
>      class Meta:
> -        ordering = ('date',)
>          verbose_name_plural = 'Series'
>  
>  
> diff --git a/patchwork/views/__init__.py b/patchwork/views/__init__.py
> index f8d23a388ac7..96fd0798af5a 100644
> --- a/patchwork/views/__init__.py
> +++ b/patchwork/views/__init__.py
> @@ -287,6 +287,8 @@ def generic_list(request, project, view, view_args=None, filter_settings=None,
>      # rendering the list template
>      patches = patches.select_related('state', 'submitter', 'delegate')
>  
> +    patches = patches.only('state','submitter','delegate','project','name','date')
> +
>      # we also need checks and series
>      patches = patches.prefetch_related('check_set', 'series')
>  
> -- 
> 2.17.1
>
> _______________________________________________
> Patchwork mailing list
> Patchwork@lists.ozlabs.org
> https://lists.ozlabs.org/listinfo/patchwork
Stewart Smith Aug. 13, 2018, 2:06 a.m. | #2
Daniel Axtens <dja@axtens.net> writes:
> Stewart Smith <stewart@linux.ibm.com> writes:
>
>> There's two main bits that are really expensive when composing the list
>> of patches for a project: the query getting the list, and the query
>> finding the series for each patch.
>>
>> If we look at the query getting the list, it gets a lot of unnesseccary
>> fields such as 'headers' and 'content', even though we tell Django not
>> to. It turns out that Django seems to ignore the Submission relationship
>> and I have no idea how to force it to ignore that thing (defer doesn't
>> work) but if we go only, then it works okay.
>>
>> From my import of ~8000 messages for a few projects, my laptop query
>> time (MySQL, as setup by whatever the docker-compose things do) goes
>> from:
>>
>> http://localhost:8000/project/linux-kernel/list/
>> FROM:
>> 342ms SQL queries cold cache, 268ms warm cache
>> TO:
>> 118ms SQL queries cold cache, 88ms warm cache
>>
>> Which is... non trivial to say the least.
>>
>> The big jump is the patches.only change, and the removal of ordering
>> on the patchseries takes a further 10ms off. For some strange reason, it
>> seems rather hard to tell Django that you don't care what order the
>> results come back in for that query (if we do, then the db server has to
>> do a sort rather than just return each row)
>
> Thanks Stewart! It's great to get some real DB experience - feel free to
> hang around! :)

I'll try to :)

Or at least pop up to make things faster/nicer to the database.

> So, further to our conversation with Konstantin, I tested this against
> Django 2.0. It still saves us some time - it means we no longer load the
> following fields:
>
> `patchwork_submission`.`id`, `patchwork_submission`.`msgid`,
> `patchwork_patch`.`commit_ref`, `patchwork_patch`.`pull_url`,
> `patchwork_patch`.`archived`, `patchwork_patch`.`hash`,
> `patchwork_patch`.`patch_project_id`, 
>
> This obviously saves the db some work and communication overhead.

I found that 'headers' and 'content' from the patch were also being
fetched, and it saved fetching those (which are really quite expensive
to do, as blobs are typically mostly stored on a separate page than the
row itself).

It's the blobs that were the big expense, as they'd likely generate 2
disk seeks and reads per row.

> I'm a little nervous that this will slightly complicate some of the
> further denormalisation but I think that's probably a price worth paying
> unless Stephen objects.

I don't think it should really be too much of a problem, as any
denormalisation occurs, folk are going to have to look at the queries
being produced and executed anyway otherwise there's always a chance for
regressions.

> I do still want to test the 'ordering' change a bit more before
> committing it though.

Yeah, that struck me as a weird one, but it certainly did have an effect
on some things.
Stephen Finucane Aug. 29, 2018, 1:36 p.m. | #3
On Sat, 2018-08-11 at 04:28 +1000, Daniel Axtens wrote:
> Stewart Smith <stewart@linux.ibm.com> writes:
> 
> > There's two main bits that are really expensive when composing the list
> > of patches for a project: the query getting the list, and the query
> > finding the series for each patch.
> > 
> > If we look at the query getting the list, it gets a lot of unnesseccary
> > fields such as 'headers' and 'content', even though we tell Django not
> > to. It turns out that Django seems to ignore the Submission relationship
> > and I have no idea how to force it to ignore that thing (defer doesn't
> > work) but if we go only, then it works okay.
> > 
> > From my import of ~8000 messages for a few projects, my laptop query
> > time (MySQL, as setup by whatever the docker-compose things do) goes
> > from:
> > 
> > http://localhost:8000/project/linux-kernel/list/
> > FROM:
> > 342ms SQL queries cold cache, 268ms warm cache
> > TO:
> > 118ms SQL queries cold cache, 88ms warm cache
> > 
> > Which is... non trivial to say the least.
> > 
> > The big jump is the patches.only change, and the removal of ordering
> > on the patchseries takes a further 10ms off. For some strange reason, it
> > seems rather hard to tell Django that you don't care what order the
> > results come back in for that query (if we do, then the db server has to
> > do a sort rather than just return each row)
> 
> Thanks Stewart! It's great to get some real DB experience - feel free to
> hang around! :)

+1000

> So, further to our conversation with Konstantin, I tested this against
> Django 2.0. It still saves us some time - it means we no longer load the
> following fields:
> 
> `patchwork_submission`.`id`, `patchwork_submission`.`msgid`,
> `patchwork_patch`.`commit_ref`, `patchwork_patch`.`pull_url`,
> `patchwork_patch`.`archived`, `patchwork_patch`.`hash`,
> `patchwork_patch`.`patch_project_id`, 

Out of curiosity, does this allow us to drop the patch_project_id field
entirely or not (bear in mind, I haven't read through the rest of
this).

> This obviously saves the db some work and communication overhead.
> 
> I'm a little nervous that this will slightly complicate some of the
> further denormalisation but I think that's probably a price worth paying
> unless Stephen objects.

To be honest, it could be 6 months before we get any further on big
efforts like this. A 3x performance boost right now beats an unknown
boost N months down the line, IMO. My 2c anyway.

> I do still want to test the 'ordering' change a bit more before
> committing it though.

I'm going to attempt to do my own testing of this (with a much larger
dataset) some evening this week. I'll hold off applying anything
pending this though.

Stephen
Stewart Smith Aug. 30, 2018, 5:11 a.m. | #4
Stephen Finucane <stephen@that.guru> writes:
> On Sat, 2018-08-11 at 04:28 +1000, Daniel Axtens wrote:
>> Stewart Smith <stewart@linux.ibm.com> writes:
>> So, further to our conversation with Konstantin, I tested this against
>> Django 2.0. It still saves us some time - it means we no longer load the
>> following fields:
>> 
>> `patchwork_submission`.`id`, `patchwork_submission`.`msgid`,
>> `patchwork_patch`.`commit_ref`, `patchwork_patch`.`pull_url`,
>> `patchwork_patch`.`archived`, `patchwork_patch`.`hash`,
>> `patchwork_patch`.`patch_project_id`, 
>
> Out of curiosity, does this allow us to drop the patch_project_id field
> entirely or not (bear in mind, I haven't read through the rest of
> this).

I'm not too sure... Umm... maybe not as I think we can go from patch to
project when viewing a patch?

>> This obviously saves the db some work and communication overhead.
>> 
>> I'm a little nervous that this will slightly complicate some of the
>> further denormalisation but I think that's probably a price worth paying
>> unless Stephen objects.
>
> To be honest, it could be 6 months before we get any further on big
> efforts like this. A 3x performance boost right now beats an unknown
> boost N months down the line, IMO. My 2c anyway.

I'd tend to agree, we may as well get the maximum performance we can
today, and then if it's *really* needed later down the track, we can
change the schema a bunch to denormalise.

From my benchmarks, the biggest boost currently would be to precompute
and cache patch counts for most of the common views, as a *lot* of page
time is for that.

>> I do still want to test the 'ordering' change a bit more before
>> committing it though.
>
> I'm going to attempt to do my own testing of this (with a much larger
> dataset) some evening this week. I'll hold off applying anything
> pending this though.

Nice, let me know how it goes. I'll be off somewhere in a national park
for a bunch of next week, which means I won't look at email, so it's the
*perfect* time to merge and deploy all of my patches :)
Daniel Axtens Aug. 31, 2018, 4:57 a.m. | #5
Stephen Finucane <stephen@that.guru> writes:

> On Sat, 2018-08-11 at 04:28 +1000, Daniel Axtens wrote:
>> Stewart Smith <stewart@linux.ibm.com> writes:
>> 
>> > There's two main bits that are really expensive when composing the list
>> > of patches for a project: the query getting the list, and the query
>> > finding the series for each patch.
>> > 
>> > If we look at the query getting the list, it gets a lot of unnesseccary
>> > fields such as 'headers' and 'content', even though we tell Django not
>> > to. It turns out that Django seems to ignore the Submission relationship
>> > and I have no idea how to force it to ignore that thing (defer doesn't
>> > work) but if we go only, then it works okay.
>> > 
>> > From my import of ~8000 messages for a few projects, my laptop query
>> > time (MySQL, as setup by whatever the docker-compose things do) goes
>> > from:
>> > 
>> > http://localhost:8000/project/linux-kernel/list/
>> > FROM:
>> > 342ms SQL queries cold cache, 268ms warm cache
>> > TO:
>> > 118ms SQL queries cold cache, 88ms warm cache
>> > 
>> > Which is... non trivial to say the least.
>> > 
>> > The big jump is the patches.only change, and the removal of ordering
>> > on the patchseries takes a further 10ms off. For some strange reason, it
>> > seems rather hard to tell Django that you don't care what order the
>> > results come back in for that query (if we do, then the db server has to
>> > do a sort rather than just return each row)
>> 
>> Thanks Stewart! It's great to get some real DB experience - feel free to
>> hang around! :)
>
> +1000
>
>> So, further to our conversation with Konstantin, I tested this against
>> Django 2.0. It still saves us some time - it means we no longer load the
>> following fields:
>> 
>> `patchwork_submission`.`id`, `patchwork_submission`.`msgid`,
>> `patchwork_patch`.`commit_ref`, `patchwork_patch`.`pull_url`,
>> `patchwork_patch`.`archived`, `patchwork_patch`.`hash`,
>> `patchwork_patch`.`patch_project_id`, 
>
> Out of curiosity, does this allow us to drop the patch_project_id field
> entirely or not (bear in mind, I haven't read through the rest of
> this).
>
>> This obviously saves the db some work and communication overhead.
>> 
>> I'm a little nervous that this will slightly complicate some of the
>> further denormalisation but I think that's probably a price worth paying
>> unless Stephen objects.
>
> To be honest, it could be 6 months before we get any further on big
> efforts like this. A 3x performance boost right now beats an unknown
> boost N months down the line, IMO. My 2c anyway.

OK then, reaffirming my in-principle ack to merging these once Stephen
or I can test the things they touch.

Regards,
Daniel 

>
>> I do still want to test the 'ordering' change a bit more before
>> committing it though.
>
> I'm going to attempt to do my own testing of this (with a much larger
> dataset) some evening this week. I'll hold off applying anything
> pending this though.
>
> Stephen
Stephen Finucane Aug. 31, 2018, 1:57 p.m. | #6
On Fri, 2018-08-10 at 18:00 +1000, Stewart Smith wrote:
> There's two main bits that are really expensive when composing the list
> of patches for a project: the query getting the list, and the query
> finding the series for each patch.
> 
> If we look at the query getting the list, it gets a lot of unnesseccary
> fields such as 'headers' and 'content', even though we tell Django not
> to. It turns out that Django seems to ignore the Submission relationship
> and I have no idea how to force it to ignore that thing (defer doesn't
> work) but if we go only, then it works okay.
> 
> From my import of ~8000 messages for a few projects, my laptop query
> time (MySQL, as setup by whatever the docker-compose things do) goes
> from:
> 
> http://localhost:8000/project/linux-kernel/list/
> FROM:
> 342ms SQL queries cold cache, 268ms warm cache
> TO:
> 118ms SQL queries cold cache, 88ms warm cache
> 
> Which is... non trivial to say the least.
> 
> The big jump is the patches.only change, and the removal of ordering
> on the patchseries takes a further 10ms off. For some strange reason, it
> seems rather hard to tell Django that you don't care what order the
> results come back in for that query (if we do, then the db server has to
> do a sort rather than just return each row)
> 
> Signed-off-by: Stewart Smith <stewart@linux.ibm.com>

Tested this and it looks good to me. There's a migration missing (it's
instead squashed into the first "add index" patch) but I can fix that
at merge time.

I was concerned that the combination of '.only' followed immediately by
'.prefetch_related' (for checks and series) would be an issue and
indeed it seems that, at this patch set at least, we do generate a
query per check. However, this is no different to before and it
resolved in a later check. As such:

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

I'm not going to push this (or any of the series) yet in case Daniel
wants to weigh in but will do so next week if there are no complaints.

Stephen

> ---
>  patchwork/models.py         | 1 -
>  patchwork/views/__init__.py | 2 ++
>  2 files changed, 2 insertions(+), 1 deletion(-)
> 
> diff --git a/patchwork/models.py b/patchwork/models.py
> index 6268f5b72e3c..d2b88fc48c91 100644
> --- a/patchwork/models.py
> +++ b/patchwork/models.py
> @@ -747,7 +747,6 @@ class Series(FilenameMixin, models.Model):
>          return self.name if self.name else 'Untitled series #%d' % self.id
>  
>      class Meta:
> -        ordering = ('date',)
>          verbose_name_plural = 'Series'
>  
>  
> diff --git a/patchwork/views/__init__.py b/patchwork/views/__init__.py
> index f8d23a388ac7..96fd0798af5a 100644
> --- a/patchwork/views/__init__.py
> +++ b/patchwork/views/__init__.py
> @@ -287,6 +287,8 @@ def generic_list(request, project, view, view_args=None, filter_settings=None,
>      # rendering the list template
>      patches = patches.select_related('state', 'submitter', 'delegate')
>  
> +    patches = patches.only('state','submitter','delegate','project','name','date')
> +
>      # we also need checks and series
>      patches = patches.prefetch_related('check_set', 'series')
>
Stewart Smith Sept. 10, 2018, 2:23 a.m. | #7
Stephen Finucane <stephen@that.guru> writes:
> On Fri, 2018-08-10 at 18:00 +1000, Stewart Smith wrote:
>> There's two main bits that are really expensive when composing the list
>> of patches for a project: the query getting the list, and the query
>> finding the series for each patch.
>> 
>> If we look at the query getting the list, it gets a lot of unnesseccary
>> fields such as 'headers' and 'content', even though we tell Django not
>> to. It turns out that Django seems to ignore the Submission relationship
>> and I have no idea how to force it to ignore that thing (defer doesn't
>> work) but if we go only, then it works okay.
>> 
>> From my import of ~8000 messages for a few projects, my laptop query
>> time (MySQL, as setup by whatever the docker-compose things do) goes
>> from:
>> 
>> http://localhost:8000/project/linux-kernel/list/
>> FROM:
>> 342ms SQL queries cold cache, 268ms warm cache
>> TO:
>> 118ms SQL queries cold cache, 88ms warm cache
>> 
>> Which is... non trivial to say the least.
>> 
>> The big jump is the patches.only change, and the removal of ordering
>> on the patchseries takes a further 10ms off. For some strange reason, it
>> seems rather hard to tell Django that you don't care what order the
>> results come back in for that query (if we do, then the db server has to
>> do a sort rather than just return each row)
>> 
>> Signed-off-by: Stewart Smith <stewart@linux.ibm.com>
>
> Tested this and it looks good to me. There's a migration missing (it's
> instead squashed into the first "add index" patch) but I can fix that
> at merge time.

Ahh, sorry about that.

> I was concerned that the combination of '.only' followed immediately by
> '.prefetch_related' (for checks and series) would be an issue and
> indeed it seems that, at this patch set at least, we do generate a
> query per check. However, this is no different to before and it
> resolved in a later check. As such:
>
> Reviewed-by: Stephen Finucane <stephen@that.guru>
>
> I'm not going to push this (or any of the series) yet in case Daniel
> wants to weigh in but will do so next week if there are no complaints.

Thanks for testing it all, increases my confidence with the patches as
well :)
Daniel Axtens Sept. 10, 2018, 1:26 p.m. | #8
Stephen Finucane <stephen@that.guru> writes:

> On Fri, 2018-08-10 at 18:00 +1000, Stewart Smith wrote:
>> There's two main bits that are really expensive when composing the list
>> of patches for a project: the query getting the list, and the query
>> finding the series for each patch.
>> 
>> If we look at the query getting the list, it gets a lot of unnesseccary
>> fields such as 'headers' and 'content', even though we tell Django not
>> to. It turns out that Django seems to ignore the Submission relationship
>> and I have no idea how to force it to ignore that thing (defer doesn't
>> work) but if we go only, then it works okay.
>> 
>> From my import of ~8000 messages for a few projects, my laptop query
>> time (MySQL, as setup by whatever the docker-compose things do) goes
>> from:
>> 
>> http://localhost:8000/project/linux-kernel/list/
>> FROM:
>> 342ms SQL queries cold cache, 268ms warm cache
>> TO:
>> 118ms SQL queries cold cache, 88ms warm cache
>> 
>> Which is... non trivial to say the least.
>> 
>> The big jump is the patches.only change, and the removal of ordering
>> on the patchseries takes a further 10ms off. For some strange reason, it
>> seems rather hard to tell Django that you don't care what order the
>> results come back in for that query (if we do, then the db server has to
>> do a sort rather than just return each row)
>> 
>> Signed-off-by: Stewart Smith <stewart@linux.ibm.com>
>
> Tested this and it looks good to me. There's a migration missing (it's
> instead squashed into the first "add index" patch) but I can fix that
> at merge time.
>
> I was concerned that the combination of '.only' followed immediately by
> '.prefetch_related' (for checks and series) would be an issue and
> indeed it seems that, at this patch set at least, we do generate a
> query per check. However, this is no different to before and it
> resolved in a later check. As such:
>
> Reviewed-by: Stephen Finucane <stephen@that.guru>
>
> I'm not going to push this (or any of the series) yet in case Daniel
> wants to weigh in but will do so next week if there are no complaints.

I'm not likely to find the time to do so, so if you're happy I'm happy :)
Merge at will!

Regards,
Daniel

>
> Stephen
>
>> ---
>>  patchwork/models.py         | 1 -
>>  patchwork/views/__init__.py | 2 ++
>>  2 files changed, 2 insertions(+), 1 deletion(-)
>> 
>> diff --git a/patchwork/models.py b/patchwork/models.py
>> index 6268f5b72e3c..d2b88fc48c91 100644
>> --- a/patchwork/models.py
>> +++ b/patchwork/models.py
>> @@ -747,7 +747,6 @@ class Series(FilenameMixin, models.Model):
>>          return self.name if self.name else 'Untitled series #%d' % self.id
>>  
>>      class Meta:
>> -        ordering = ('date',)
>>          verbose_name_plural = 'Series'
>>  
>>  
>> diff --git a/patchwork/views/__init__.py b/patchwork/views/__init__.py
>> index f8d23a388ac7..96fd0798af5a 100644
>> --- a/patchwork/views/__init__.py
>> +++ b/patchwork/views/__init__.py
>> @@ -287,6 +287,8 @@ def generic_list(request, project, view, view_args=None, filter_settings=None,
>>      # rendering the list template
>>      patches = patches.select_related('state', 'submitter', 'delegate')
>>  
>> +    patches = patches.only('state','submitter','delegate','project','name','date')
>> +
>>      # we also need checks and series
>>      patches = patches.prefetch_related('check_set', 'series')
>>  
>
>
> _______________________________________________
> Patchwork mailing list
> Patchwork@lists.ozlabs.org
> https://lists.ozlabs.org/listinfo/patchwork
Stephen Finucane Sept. 10, 2018, 9:36 p.m. | #9
On Mon, 2018-09-10 at 23:26 +1000, Daniel Axtens wrote:
> Stephen Finucane <stephen@that.guru> writes:
> 
> > On Fri, 2018-08-10 at 18:00 +1000, Stewart Smith wrote:
> > > There's two main bits that are really expensive when composing the list
> > > of patches for a project: the query getting the list, and the query
> > > finding the series for each patch.
> > > 
> > > If we look at the query getting the list, it gets a lot of unnesseccary
> > > fields such as 'headers' and 'content', even though we tell Django not
> > > to. It turns out that Django seems to ignore the Submission relationship
> > > and I have no idea how to force it to ignore that thing (defer doesn't
> > > work) but if we go only, then it works okay.
> > > 
> > > From my import of ~8000 messages for a few projects, my laptop query
> > > time (MySQL, as setup by whatever the docker-compose things do) goes
> > > from:
> > > 
> > > http://localhost:8000/project/linux-kernel/list/
> > > FROM:
> > > 342ms SQL queries cold cache, 268ms warm cache
> > > TO:
> > > 118ms SQL queries cold cache, 88ms warm cache
> > > 
> > > Which is... non trivial to say the least.
> > > 
> > > The big jump is the patches.only change, and the removal of ordering
> > > on the patchseries takes a further 10ms off. For some strange reason, it
> > > seems rather hard to tell Django that you don't care what order the
> > > results come back in for that query (if we do, then the db server has to
> > > do a sort rather than just return each row)
> > > 
> > > Signed-off-by: Stewart Smith <stewart@linux.ibm.com>
> > 
> > Tested this and it looks good to me. There's a migration missing (it's
> > instead squashed into the first "add index" patch) but I can fix that
> > at merge time.
> > 
> > I was concerned that the combination of '.only' followed immediately by
> > '.prefetch_related' (for checks and series) would be an issue and
> > indeed it seems that, at this patch set at least, we do generate a
> > query per check. However, this is no different to before and it
> > resolved in a later check. As such:
> > 
> > Reviewed-by: Stephen Finucane <stephen@that.guru>
> > 
> > I'm not going to push this (or any of the series) yet in case Daniel
> > wants to weigh in but will do so next week if there are no complaints.
> 
> I'm not likely to find the time to do so, so if you're happy I'm happy :)
> Merge at will!

Sweet. Have pushed all of this now so.

Stephen

> Regards,
> Daniel
> 
> > 
> > Stephen
> > 
> > > ---
> > >  patchwork/models.py         | 1 -
> > >  patchwork/views/__init__.py | 2 ++
> > >  2 files changed, 2 insertions(+), 1 deletion(-)
> > > 
> > > diff --git a/patchwork/models.py b/patchwork/models.py
> > > index 6268f5b72e3c..d2b88fc48c91 100644
> > > --- a/patchwork/models.py
> > > +++ b/patchwork/models.py
> > > @@ -747,7 +747,6 @@ class Series(FilenameMixin, models.Model):
> > >          return self.name if self.name else 'Untitled series #%d' % self.id
> > >  
> > >      class Meta:
> > > -        ordering = ('date',)
> > >          verbose_name_plural = 'Series'
> > >  
> > >  
> > > diff --git a/patchwork/views/__init__.py b/patchwork/views/__init__.py
> > > index f8d23a388ac7..96fd0798af5a 100644
> > > --- a/patchwork/views/__init__.py
> > > +++ b/patchwork/views/__init__.py
> > > @@ -287,6 +287,8 @@ def generic_list(request, project, view, view_args=None, filter_settings=None,
> > >      # rendering the list template
> > >      patches = patches.select_related('state', 'submitter', 'delegate')
> > >  
> > > +    patches = patches.only('state','submitter','delegate','project','name','date')
> > > +
> > >      # we also need checks and series
> > >      patches = patches.prefetch_related('check_set', 'series')
> > >  
> > 
> > 
> > _______________________________________________
> > Patchwork mailing list
> > Patchwork@lists.ozlabs.org
> > https://lists.ozlabs.org/listinfo/patchwork

Patch

diff --git a/patchwork/models.py b/patchwork/models.py
index 6268f5b72e3c..d2b88fc48c91 100644
--- a/patchwork/models.py
+++ b/patchwork/models.py
@@ -747,7 +747,6 @@  class Series(FilenameMixin, models.Model):
         return self.name if self.name else 'Untitled series #%d' % self.id
 
     class Meta:
-        ordering = ('date',)
         verbose_name_plural = 'Series'
 
 
diff --git a/patchwork/views/__init__.py b/patchwork/views/__init__.py
index f8d23a388ac7..96fd0798af5a 100644
--- a/patchwork/views/__init__.py
+++ b/patchwork/views/__init__.py
@@ -287,6 +287,8 @@  def generic_list(request, project, view, view_args=None, filter_settings=None,
     # rendering the list template
     patches = patches.select_related('state', 'submitter', 'delegate')
 
+    patches = patches.only('state','submitter','delegate','project','name','date')
+
     # we also need checks and series
     patches = patches.prefetch_related('check_set', 'series')