From patchwork Thu Mar 8 15:54:37 2018 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Daniel Axtens X-Patchwork-Id: 883208 Return-Path: X-Original-To: incoming@patchwork.ozlabs.org Delivered-To: patchwork-incoming@bilbo.ozlabs.org Received: from lists.ozlabs.org (lists.ozlabs.org [103.22.144.68]) (using TLSv1.2 with cipher ADH-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by ozlabs.org (Postfix) with ESMTPS id 3zxwFS4HSGz9sf2 for ; Fri, 9 Mar 2018 02:59:56 +1100 (AEDT) Authentication-Results: ozlabs.org; dmarc=none (p=none dis=none) header.from=axtens.net Authentication-Results: ozlabs.org; dkim=fail reason="signature verification failed" (1024-bit key; unprotected) header.d=axtens.net header.i=@axtens.net header.b="q2KSPbTj"; dkim-atps=neutral Received: from bilbo.ozlabs.org (lists.ozlabs.org [IPv6:2401:3900:2:1::3]) by lists.ozlabs.org (Postfix) with ESMTP id 3zxwFS1xLZzF1v5 for ; Fri, 9 Mar 2018 02:59:56 +1100 (AEDT) Authentication-Results: lists.ozlabs.org; dmarc=none (p=none dis=none) header.from=axtens.net Authentication-Results: lists.ozlabs.org; dkim=fail reason="signature verification failed" (1024-bit key; unprotected) header.d=axtens.net header.i=@axtens.net header.b="q2KSPbTj"; dkim-atps=neutral X-Original-To: patchwork@lists.ozlabs.org Delivered-To: patchwork@lists.ozlabs.org Authentication-Results: lists.ozlabs.org; spf=pass (mailfrom) smtp.mailfrom=axtens.net (client-ip=2607:f8b0:400e:c05::22b; helo=mail-pg0-x22b.google.com; envelope-from=dja@axtens.net; receiver=) Authentication-Results: lists.ozlabs.org; dmarc=none (p=none dis=none) header.from=axtens.net Authentication-Results: lists.ozlabs.org; dkim=pass (1024-bit key; unprotected) header.d=axtens.net header.i=@axtens.net header.b="q2KSPbTj"; dkim-atps=neutral Received: from mail-pg0-x22b.google.com (mail-pg0-x22b.google.com [IPv6:2607:f8b0:400e:c05::22b]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by lists.ozlabs.org (Postfix) with ESMTPS id 3zxwFF5m48zF1tl for ; Fri, 9 Mar 2018 02:59:45 +1100 (AEDT) Received: by mail-pg0-x22b.google.com with SMTP id l24so2373945pgc.5 for ; Thu, 08 Mar 2018 07:59:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=axtens.net; s=google; h=from:to:cc:subject:date:message-id; bh=raUXcNMqFPmBZe2MWV+16DR+IP7UkFWXVFF4S3OjMKI=; b=q2KSPbTji4DPqVQxS77Gkj3hly2DjapmiS/dCNBDml8X4rS8ARrtd2VA6Z4G99RGTt cCnWxZhH6CpLGKcC3JWGudazJLpeVJPT+3GRHNvbvZui00MHYkxOm9Z2UOW/i+n619iv OmtwyvN85k770Y9RNjZj4hwjdHUSGGQWK0aoE= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:subject:date:message-id; bh=raUXcNMqFPmBZe2MWV+16DR+IP7UkFWXVFF4S3OjMKI=; b=t0DxgHKgmzBcVmDDGvI6vnmOYWoTetbAPAi888/tuJ36vs6YnSK7DAXaHFTa8MnNxv GFpCtZTpbXyuNwTmJPmniP96l9S1cEqPa/D1xRHbqJlMwX46Xuw32GYp8H7gArcgFZpf qyTwa7BUNkbe+cBqmFhFwPLglyr6qUSg35JRTSTJOO90k0Nkn+A1rWiGYRvsSfQNqtf9 91U2GHmQYxHBwwDP0WG5LdHRD2w/UiF+BXUhz7vcV2OCp66T7TQ0V8QeAq7zTEGfVIY1 LhNsMUQDM1vZDM05b67j5ujoQ8auuf2d7u177U2EbpSANUCO8iIC7okBN6DkgujXUW4k Alyw== X-Gm-Message-State: APf1xPDHEQW0rAKIjsMBWo9ccjOI/Jn8UFRcI+s5ZsbuHtJDLKtf8f7W mS5qqztqvLvb/ijm5N2qEMtmtd/V+iU= X-Google-Smtp-Source: AG47ELvI3TAscNPXgH9zxS09rDVMVWrDWM8jVCVM/MjGs6FXLfioqCUoGn4iZiv78qzSUj7hoaX0pQ== X-Received: by 10.99.97.86 with SMTP id v83mr21349078pgb.138.1520524782587; Thu, 08 Mar 2018 07:59:42 -0800 (PST) Received: from localhost.localdomain (124-171-105-210.dyn.iinet.net.au. [124.171.105.210]) by smtp.gmail.com with ESMTPSA id j19sm36275943pfh.26.2018.03.08.07.59.40 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 08 Mar 2018 07:59:41 -0800 (PST) From: Daniel Axtens To: patchwork@lists.ozlabs.org Subject: [PATCH] Fix slow Patch counting query Date: Fri, 9 Mar 2018 02:54:37 +1100 Message-Id: <20180308155437.8082-1-dja@axtens.net> X-Mailer: git-send-email 2.14.1 X-BeenThere: patchwork@lists.ozlabs.org X-Mailman-Version: 2.1.26 Precedence: list List-Id: Patchwork development List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: sfr@canb.auug.org.au MIME-Version: 1.0 Errors-To: patchwork-bounces+incoming=patchwork.ozlabs.org@lists.ozlabs.org Sender: "Patchwork" Stephen Rothwell noticed (way back in September - sorry Stephen!) that the following query is really slow on OzLabs: SELECT COUNT(*) AS "__count" FROM "patchwork_patch" INNER JOIN "patchwork_submission" ON ("patchwork_patch"."submission_ptr_id" = "patchwork_submission"."id") WHERE ("patchwork_submission"."project_id" = 14 AND "patchwork_patch"."state_id" IN (SELECT U0."id" AS Col1 FROM "patchwork_state" U0 WHERE U0."action_required" = true ORDER BY U0."ordering" ASC)); I think this is really slow because we have to join the patch and submission table to get the project id, which we need to filter the patches. Duplicate the project id in the patch table itself, which allows us to avoid the JOIN. The new query reads as: SELECT COUNT(*) AS "__count" FROM "patchwork_patch" WHERE ("patchwork_patch"."patch_project_id" = 1 AND "patchwork_patch"."state_id" IN (SELECT U0."id" AS Col1 FROM "patchwork_state" U0 WHERE U0."action_required" = true ORDER BY U0."ordering" ASC)); Very simple testing on a small, artifical Postgres instance (3 projects, 102711 patches), shows speed gains of ~1.5-5x for this query. Looking at Postgres' cost estimates (EXPLAIN) of the first query vs the second query, we see a ~1.75x improvement there too. I suspect the gains will be bigger on OzLabs. (It turns out all of this is all for the "| NN patches" counter we added to the filter bar!!) Reported-by: Stephen Rothwell Signed-off-by: Daniel Axtens --- This requires a migration, so I don't think we can feasibly do it as a stable update. I think we drop the patch counter for stable and try to get this and the event stuff merged to master promptly, and just tag 2.1. (To that end, I will re-read and finish reviewing the event stuff soon.) --- patchwork/migrations/0024_patch_patch_project.py | 39 ++++++++++++++++++++++++ patchwork/models.py | 4 +++ patchwork/parser.py | 1 + patchwork/views/__init__.py | 2 +- 4 files changed, 45 insertions(+), 1 deletion(-) create mode 100644 patchwork/migrations/0024_patch_patch_project.py diff --git a/patchwork/migrations/0024_patch_patch_project.py b/patchwork/migrations/0024_patch_patch_project.py new file mode 100644 index 000000000000..76d8f144c9dd --- /dev/null +++ b/patchwork/migrations/0024_patch_patch_project.py @@ -0,0 +1,39 @@ +# -*- coding: utf-8 -*- +# Generated by Django 1.11.10 on 2018-03-08 01:51 +from __future__ import unicode_literals + +from django.db import migrations, models +import django.db.models.deletion + + +class Migration(migrations.Migration): + # per migration 16, but note this seems to be going away + # in new PostgreSQLs (https://stackoverflow.com/questions/12838111/south-cannot-alter-table-because-it-has-pending-trigger-events#comment44629663_12838113) + atomic = False + + dependencies = [ + ('patchwork', '0023_timezone_unify'), + ] + + operations = [ + migrations.AddField( + model_name='patch', + name='patch_project', + field=models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.CASCADE, to='patchwork.Project'), + preserve_default=False, + ), + + # as with 10, this will break if you use non-default table names + migrations.RunSQL('''UPDATE patchwork_patch SET patch_project_id = + (SELECT project_id FROM patchwork_submission + WHERE patchwork_submission.id = + patchwork_patch.submission_ptr_id);''' + ), + + migrations.AlterField( + model_name='patch', + name='patch_project', + field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='patchwork.Project'), + ), + + ] diff --git a/patchwork/models.py b/patchwork/models.py index b2491752f04a..3b905c4cd75b 100644 --- a/patchwork/models.py +++ b/patchwork/models.py @@ -423,6 +423,10 @@ class Patch(SeriesMixin, Submission): archived = models.BooleanField(default=False) hash = HashField(null=True, blank=True) + # duplicate project from submission in subclass so we can count the + # patches in a project without needing to do a JOIN. + patch_project = models.ForeignKey(Project, on_delete=models.CASCADE) + objects = PatchManager() @staticmethod diff --git a/patchwork/parser.py b/patchwork/parser.py index 803e98592fa8..805037c72d73 100644 --- a/patchwork/parser.py +++ b/patchwork/parser.py @@ -1004,6 +1004,7 @@ def parse_mail(mail, list_id=None): patch = Patch.objects.create( msgid=msgid, project=project, + patch_project=project, name=name[:255], date=date, headers=headers, diff --git a/patchwork/views/__init__.py b/patchwork/views/__init__.py index 3baf2999a836..f8d23a388ac7 100644 --- a/patchwork/views/__init__.py +++ b/patchwork/views/__init__.py @@ -270,7 +270,7 @@ def generic_list(request, project, view, view_args=None, filter_settings=None, context['filters'].set_status(filterclass, setting) if patches is None: - patches = Patch.objects.filter(project=project) + patches = Patch.objects.filter(patch_project=project) # annotate with tag counts patches = patches.with_tag_counts(project)