[PATCH 00/11] Performance for ALL THE THINGS!

[Stewart Smith]

This is an updated and extended patch series of the few patches I've
sent out over the past few days.

I've had a pretty major attack at performance of as many patchwork pages
and bits of functionality that I could find.

I've been using a mixture of test environments for this, initially a
small dataset in MySQL, and later on, the data that's in
patchwork.ozlabs.org in PostgreSQL.

I've done some testing with a warm cache and cold cache (standard drop
caches trick) to benchmark what the difference could be for things that
aren't too commonly viewed.

As of now, everything except adding patches to a bundle seems to be
fewer than 20 SQL queries, and worst case for a cold cache seems to be a
total of about a second in the database.

I think the next big wins are going to be:
- Adding a 'date' column to patchwork_patch so we don't have to go to
  patchwork_submission for it.
  (and then bending Django to produce a decent query that doesn't go
  there anyway)
- Computing/caching patch counts for common queries.
  e.g. right now, just viewing the /list/ page for qemu-devel takes 26ms
  for the query to display the first page of patches, but 226ms to do the
  COUNT(). Even on high page numbers, where the query to generate the
  list could be 700ms on a cold cache, 226ms is a lot.
  i.e. a cache of the matrix of action_required/archived would give us a
  huge boost in page generation.
- Redoing the create/add/remove from bundle logic to not do everything
  patch-at-a-time.
- Tackling the efficiency of the python code itself.

With this series, I'm struggling to find any query or page that takes
more than a second of database time on my laptop, with the worst cases
being around 400ms, and most being <40ms.

Let me know if there's still anything slow with this patch set :)

This series replaces:
https://patchwork.ozlabs.org/patch/954921/
https://patchwork.ozlabs.org/patch/955321/
https://patchwork.ozlabs.org/patch/955322/
https://patchwork.ozlabs.org/patch/955342/
https://patchwork.ozlabs.org/patch/955347/
https://patchwork.ozlabs.org/patch/955392/

Stewart Smith (11):
  Improve patch listing performance (~3x)
  4x performance improvement for viewing patch with many comments
  Add index for patchwork_comment (submission_id,date)
  Fetch all series for patch/cover viewing
  Add covering index for /list/ query
  check distinct(user) based on just user_id
  Be particular over check_set and series prefetch for /list/
  Add covering index to patchwork_submissions for /list/ queries
  Optimise fetching checks when displaying a patch
  Be sensible computing project patch counts
  Fetch maintainer information in one query

 .../migrations/0027_add_comment_date_index.py | 23 +++++++++++++++++
 .../0028_add_list_covering_index.py           | 19 ++++++++++++++
 .../0029_add_submission_covering_index.py     | 19 ++++++++++++++
 patchwork/models.py                           | 21 ++++++++++++++--
 patchwork/templates/patchwork/submission.html | 16 ++++++------
 patchwork/views/__init__.py                   |  8 +++++-
 patchwork/views/cover.py                      |  5 ++++
 patchwork/views/patch.py                      |  7 ++++++
 patchwork/views/project.py                    | 25 ++++++++++++++++---
 9 files changed, 128 insertions(+), 15 deletions(-)
 create mode 100644 patchwork/migrations/0027_add_comment_date_index.py
 create mode 100644 patchwork/migrations/0028_add_list_covering_index.py
 create mode 100644 patchwork/migrations/0029_add_submission_covering_index.py

-- 
2.17.1