From patchwork Fri Aug 10 08:01:05 2018 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Stewart Smith X-Patchwork-Id: 956051 Return-Path: X-Original-To: incoming@patchwork.ozlabs.org Delivered-To: patchwork-incoming@bilbo.ozlabs.org Received: from lists.ozlabs.org (lists.ozlabs.org [IPv6:2401:3900:2:1::3]) (using TLSv1.2 with cipher ADH-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by ozlabs.org (Postfix) with ESMTPS id 41myMB4Lhwz9s5b for ; Fri, 10 Aug 2018 18:04:22 +1000 (AEST) Authentication-Results: ozlabs.org; dmarc=none (p=none dis=none) header.from=linux.ibm.com Received: from lists.ozlabs.org (lists.ozlabs.org [IPv6:2401:3900:2:1::3]) by lists.ozlabs.org (Postfix) with ESMTP id 41myMB32sjzF0Qs for ; Fri, 10 Aug 2018 18:04:22 +1000 (AEST) Authentication-Results: lists.ozlabs.org; dmarc=none (p=none dis=none) header.from=linux.ibm.com X-Original-To: patchwork@lists.ozlabs.org Delivered-To: patchwork@lists.ozlabs.org Authentication-Results: lists.ozlabs.org; spf=pass (mailfrom) smtp.mailfrom=linux.ibm.com (client-ip=148.163.156.1; helo=mx0a-001b2d01.pphosted.com; envelope-from=stewart@linux.ibm.com; receiver=) Authentication-Results: lists.ozlabs.org; dmarc=none (p=none dis=none) header.from=linux.ibm.com Received: from mx0a-001b2d01.pphosted.com (mx0a-001b2d01.pphosted.com [148.163.156.1]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by lists.ozlabs.org (Postfix) with ESMTPS id 41myHg6YnvzF0PJ for ; Fri, 10 Aug 2018 18:01:19 +1000 (AEST) Received: from pps.filterd (m0098409.ppops.net [127.0.0.1]) by mx0a-001b2d01.pphosted.com (8.16.0.22/8.16.0.22) with SMTP id w7A7wuAb048910 for ; Fri, 10 Aug 2018 04:01:17 -0400 Received: from e32.co.us.ibm.com (e32.co.us.ibm.com [32.97.110.150]) by mx0a-001b2d01.pphosted.com with ESMTP id 2ks5v6265u-1 (version=TLSv1.2 cipher=AES256-GCM-SHA384 bits=256 verify=NOT) for ; Fri, 10 Aug 2018 04:01:16 -0400 Received: from localhost by e32.co.us.ibm.com with IBM ESMTP SMTP Gateway: Authorized Use Only! Violators will be prosecuted for from ; Fri, 10 Aug 2018 02:01:15 -0600 Received: from b03cxnp07029.gho.boulder.ibm.com (9.17.130.16) by e32.co.us.ibm.com (192.168.1.132) with IBM ESMTP SMTP Gateway: Authorized Use Only! Violators will be prosecuted; (version=TLSv1/SSLv3 cipher=AES256-GCM-SHA384 bits=256/256) Fri, 10 Aug 2018 02:01:12 -0600 Received: from b03ledav002.gho.boulder.ibm.com (b03ledav002.gho.boulder.ibm.com [9.17.130.233]) by b03cxnp07029.gho.boulder.ibm.com (8.14.9/8.14.9/NCO v10.0) with ESMTP id w7A81BP366781392 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256 verify=FAIL) for ; Fri, 10 Aug 2018 01:01:11 -0700 Received: from b03ledav002.gho.boulder.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id C7A6013605E for ; Fri, 10 Aug 2018 02:01:11 -0600 (MDT) Received: from b03ledav002.gho.boulder.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id 905B813604F for ; Fri, 10 Aug 2018 02:01:11 -0600 (MDT) Received: from birb.localdomain (unknown [9.185.142.88]) by b03ledav002.gho.boulder.ibm.com (Postfix) with ESMTP for ; Fri, 10 Aug 2018 02:01:11 -0600 (MDT) Received: by birb.localdomain (Postfix, from userid 1000) id 5C65A4EC5E1; Fri, 10 Aug 2018 18:01:07 +1000 (AEST) From: Stewart Smith To: patchwork@lists.ozlabs.org Subject: [PATCH 10/11] Be sensible computing project patch counts Date: Fri, 10 Aug 2018 18:01:05 +1000 X-Mailer: git-send-email 2.17.1 In-Reply-To: <20180810080106.10714-1-stewart@linux.ibm.com> References: <20180810080106.10714-1-stewart@linux.ibm.com> X-TM-AS-GCONF: 00 x-cbid: 18081008-0004-0000-0000-00001473A025 X-IBM-SpamModules-Scores: X-IBM-SpamModules-Versions: BY=3.00009517; HX=3.00000242; KW=3.00000007; PH=3.00000004; SC=3.00000266; SDB=6.01071573; UDB=6.00551858; IPR=6.00851334; MB=3.00022630; MTD=3.00000008; XFM=3.00000015; UTC=2018-08-10 08:01:14 X-IBM-AV-DETECTION: SAVI=unused REMOTE=unused XFE=unused x-cbparentid: 18081008-0005-0000-0000-000088689507 Message-Id: <20180810080106.10714-11-stewart@linux.ibm.com> X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:, , definitions=2018-08-10_05:, , signatures=0 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 priorityscore=1501 malwarescore=0 suspectscore=1 phishscore=0 bulkscore=0 spamscore=0 clxscore=1015 lowpriorityscore=0 mlxscore=0 impostorscore=0 mlxlogscore=999 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1807170000 definitions=main-1808100087 X-BeenThere: patchwork@lists.ozlabs.org X-Mailman-Version: 2.1.27 Precedence: list List-Id: Patchwork development List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , MIME-Version: 1.0 Errors-To: patchwork-bounces+incoming=patchwork.ozlabs.org@lists.ozlabs.org Sender: "Patchwork" Django actively fights constructing a query that isn't insane. So, let's go and just execute a raw one. This is all very standard SQL so should execute everywhere without a problem. With the dataset of patchwork.ozlabs.org, looking at the /project/ page for qemu-devel would take 13 queries and 1500ms, with this patch it's down to 11 queries in ~250ms. For the dataset of the netdev list, it's down to 440ms from 1500ms. Signed-off-by: Stewart Smith Reviewed-by: Stephen Finucane --- patchwork/views/project.py | 23 ++++++++++++++++++++--- 1 file changed, 20 insertions(+), 3 deletions(-) diff --git a/patchwork/views/project.py b/patchwork/views/project.py index 484455c02d9d..2a75242a06af 100644 --- a/patchwork/views/project.py +++ b/patchwork/views/project.py @@ -27,6 +27,8 @@ from patchwork.compat import reverse from patchwork.models import Patch from patchwork.models import Project +from django.db import connection + def project_list(request): projects = Project.objects.all() @@ -44,14 +46,29 @@ def project_list(request): def project_detail(request, project_id): project = get_object_or_404(Project, linkname=project_id) - patches = Patch.objects.filter(project=project) + + # So, we revert to raw sql because if we do what you'd think would + # be the correct thing in Django-ese, it ends up doing a *pointless* + # join with patchwork_submissions that ends up ruining the query. + # So, we do not do this, as this is wrong: + #patches = Patch.objects.filter(patch_project_id=project.id).only('archived') + #patches = patches.annotate(c=Count('archived')) + # and instead do this, because it's simple and fast + + n_patches = {} + with connection.cursor() as cursor: + c = cursor.execute('SELECT archived,COUNT(submission_ptr_id) as c FROM patchwork_patch WHERE patch_project_id=%s GROUP BY archived', + [project.id]) + + for r in cursor: + n_patches[r[0]] = r[1] context = { 'project': project, 'maintainers': User.objects.filter( profile__maintainer_projects=project), - 'n_patches': patches.filter(archived=False).count(), - 'n_archived_patches': patches.filter(archived=True).count(), + 'n_patches': n_patches[False], + 'n_archived_patches': n_patches[True], 'enable_xmlrpc': settings.ENABLE_XMLRPC, } return render(request, 'patchwork/project.html', context)