From patchwork Fri Aug 10 08:00:56 2018 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Stewart Smith X-Patchwork-Id: 956046 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 41myKf5MxRz9s5b for ; Fri, 10 Aug 2018 18:03:02 +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 41myKf446hzF0Qs for ; Fri, 10 Aug 2018 18:03:02 +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 41myHg5WTzzDrbj for ; Fri, 10 Aug 2018 18:01:18 +1000 (AEST) Received: from pps.filterd (m0098394.ppops.net [127.0.0.1]) by mx0a-001b2d01.pphosted.com (8.16.0.22/8.16.0.22) with SMTP id w7A7womj057330 for ; Fri, 10 Aug 2018 04:01:15 -0400 Received: from e14.ny.us.ibm.com (e14.ny.us.ibm.com [129.33.205.204]) by mx0a-001b2d01.pphosted.com with ESMTP id 2ks5uvaa4e-1 (version=TLSv1.2 cipher=AES256-GCM-SHA384 bits=256 verify=NOT) for ; Fri, 10 Aug 2018 04:01:14 -0400 Received: from localhost by e14.ny.us.ibm.com with IBM ESMTP SMTP Gateway: Authorized Use Only! Violators will be prosecuted for from ; Fri, 10 Aug 2018 04:01:13 -0400 Received: from b01cxnp22034.gho.pok.ibm.com (9.57.198.24) by e14.ny.us.ibm.com (146.89.104.201) 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 04:01:11 -0400 Received: from b01ledav006.gho.pok.ibm.com (b01ledav006.gho.pok.ibm.com [9.57.199.111]) by b01cxnp22034.gho.pok.ibm.com (8.14.9/8.14.9/NCO v10.0) with ESMTP id w7A81AGm14811148 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256 verify=FAIL) for ; Fri, 10 Aug 2018 08:01:10 GMT Received: from b01ledav006.gho.pok.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id D4D0FAC05F for ; Fri, 10 Aug 2018 04:01:37 -0400 (EDT) Received: from b01ledav006.gho.pok.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id 94604AC066 for ; Fri, 10 Aug 2018 04:01:37 -0400 (EDT) Received: from birb.localdomain (unknown [9.185.142.88]) by b01ledav006.gho.pok.ibm.com (Postfix) with ESMTP for ; Fri, 10 Aug 2018 04:01:37 -0400 (EDT) Received: by birb.localdomain (Postfix, from userid 1000) id 29DF04EC6A0; Fri, 10 Aug 2018 18:01:07 +1000 (AEST) From: Stewart Smith To: patchwork@lists.ozlabs.org Subject: [PATCH 01/11] Improve patch listing performance (~3x) Date: Fri, 10 Aug 2018 18:00:56 +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-0052-0000-0000-0000031C9E57 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:12 X-IBM-AV-DETECTION: SAVI=unused REMOTE=unused XFE=unused x-cbparentid: 18081008-0053-0000-0000-00005DAD77C1 Message-Id: <20180810080106.10714-2-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=974 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" 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 Reviewed-by: Stephen Finucane --- 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') From patchwork Fri Aug 10 08:00:57 2018 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Stewart Smith X-Patchwork-Id: 956040 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 41myJH0RSxz9s5b for ; Fri, 10 Aug 2018 18:01:51 +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 41myJG6N7NzDrpW for ; Fri, 10 Aug 2018 18:01:50 +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.158.5; 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 (mx0b-001b2d01.pphosted.com [148.163.158.5]) (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 41myHg4qXczDrYj for ; Fri, 10 Aug 2018 18:01:18 +1000 (AEST) Received: from pps.filterd (m0098417.ppops.net [127.0.0.1]) by mx0a-001b2d01.pphosted.com (8.16.0.22/8.16.0.22) with SMTP id w7A7wnTk108007 for ; Fri, 10 Aug 2018 04:01:14 -0400 Received: from e14.ny.us.ibm.com (e14.ny.us.ibm.com [129.33.205.204]) by mx0a-001b2d01.pphosted.com with ESMTP id 2ks5e9ub25-1 (version=TLSv1.2 cipher=AES256-GCM-SHA384 bits=256 verify=NOT) for ; Fri, 10 Aug 2018 04:01:14 -0400 Received: from localhost by e14.ny.us.ibm.com with IBM ESMTP SMTP Gateway: Authorized Use Only! Violators will be prosecuted for from ; Fri, 10 Aug 2018 04:01:13 -0400 Received: from b01cxnp22035.gho.pok.ibm.com (9.57.198.25) by e14.ny.us.ibm.com (146.89.104.201) 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 04:01:11 -0400 Received: from b01ledav003.gho.pok.ibm.com (b01ledav003.gho.pok.ibm.com [9.57.199.108]) by b01cxnp22035.gho.pok.ibm.com (8.14.9/8.14.9/NCO v10.0) with ESMTP id w7A81A6e10551916 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256 verify=FAIL) for ; Fri, 10 Aug 2018 08:01:10 GMT Received: from b01ledav003.gho.pok.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id 1B308B206B for ; Fri, 10 Aug 2018 04:00:32 -0400 (EDT) Received: from b01ledav003.gho.pok.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id CE6EDB205F for ; Fri, 10 Aug 2018 04:00:31 -0400 (EDT) Received: from birb.localdomain (unknown [9.185.142.88]) by b01ledav003.gho.pok.ibm.com (Postfix) with ESMTP for ; Fri, 10 Aug 2018 04:00:31 -0400 (EDT) Received: by birb.localdomain (Postfix, from userid 1000) id 2EEC04EC6A5; Fri, 10 Aug 2018 18:01:07 +1000 (AEST) From: Stewart Smith To: patchwork@lists.ozlabs.org Subject: [PATCH 02/11] 4x performance improvement for viewing patch with many comments Date: Fri, 10 Aug 2018 18:00:57 +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-0052-0000-0000-0000031C9E58 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:12 X-IBM-AV-DETECTION: SAVI=unused REMOTE=unused XFE=unused x-cbparentid: 18081008-0053-0000-0000-00005DAD77C0 Message-Id: <20180810080106.10714-3-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=832 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" Using the example of id:20180720035941.6844-1-khandual@linux.vnet.ibm.com with my test dataset of a chunk of a variety of mailing lists, has this cover letter have 67 comments from a variety of people. Thus, it's on the larger side of things. Originally, displaying the /patch/550/ for this (redirected to /cover) would take 81 SQL queries in ~60ms on my laptop. After this optimisation, it's down to 14 queries in 14ms. When the cache is cold, it's down to 32ms from 83ms. The effect of this patch is to execute a join in the database to get the submitter information for each comment at the same time as getting all the comments rather than doing a one-by-one lookup after the fact. Signed-off-by: Stewart Smith Reviewed-by: Stephen Finucane --- patchwork/templates/patchwork/submission.html | 2 +- patchwork/views/cover.py | 5 +++++ patchwork/views/patch.py | 5 +++++ 3 files changed, 11 insertions(+), 1 deletion(-) diff --git a/patchwork/templates/patchwork/submission.html b/patchwork/templates/patchwork/submission.html index e817713f7079..2f69735d6925 100644 --- a/patchwork/templates/patchwork/submission.html +++ b/patchwork/templates/patchwork/submission.html @@ -262,7 +262,7 @@ function toggle_div(link_id, headers_id) -{% for item in submission.comments.all %} +{% for item in comments %} {% if forloop.first %}

Comments

{% endif %} diff --git a/patchwork/views/cover.py b/patchwork/views/cover.py index 73f83cb99b99..edad90bc694d 100644 --- a/patchwork/views/cover.py +++ b/patchwork/views/cover.py @@ -45,6 +45,11 @@ def cover_detail(request, cover_id): 'project': cover.project, } + comments = cover.comments.all() + comments = comments.select_related('submitter') + comments = comments.only('submitter','date','id','content','submission') + context['comments'] = comments + return render_to_response('patchwork/submission.html', context) diff --git a/patchwork/views/patch.py b/patchwork/views/patch.py index cbd4ec395d99..f43fbecd9a4d 100644 --- a/patchwork/views/patch.py +++ b/patchwork/views/patch.py @@ -114,6 +114,11 @@ def patch_detail(request, patch_id): if is_authenticated(request.user): context['bundles'] = request.user.bundles.all() + comments = patch.comments.all() + comments = comments.select_related('submitter') + comments = comments.only('submitter','date','id','content','submission') + + context['comments'] = comments context['submission'] = patch context['patchform'] = form context['createbundleform'] = createbundleform From patchwork Fri Aug 10 08:00:58 2018 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Stewart Smith X-Patchwork-Id: 956050 Return-Path: X-Original-To: incoming@patchwork.ozlabs.org Delivered-To: patchwork-incoming@bilbo.ozlabs.org Received: from lists.ozlabs.org (lists.ozlabs.org [203.11.71.2]) (using TLSv1.2 with cipher ADH-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by ozlabs.org (Postfix) with ESMTPS id 41myLs74nLz9s5b for ; Fri, 10 Aug 2018 18:04:05 +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 41myLs5rLlzF1R0 for ; Fri, 10 Aug 2018 18:04:05 +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 41myHg5mhWzF06S for ; Fri, 10 Aug 2018 18:01:18 +1000 (AEST) Received: from pps.filterd (m0098393.ppops.net [127.0.0.1]) by mx0a-001b2d01.pphosted.com (8.16.0.22/8.16.0.22) with SMTP id w7A7womk127730 for ; Fri, 10 Aug 2018 04:01:15 -0400 Received: from e33.co.us.ibm.com (e33.co.us.ibm.com [32.97.110.151]) by mx0a-001b2d01.pphosted.com with ESMTP id 2ks6h7rng0-1 (version=TLSv1.2 cipher=AES256-GCM-SHA384 bits=256 verify=NOT) for ; Fri, 10 Aug 2018 04:01:14 -0400 Received: from localhost by e33.co.us.ibm.com with IBM ESMTP SMTP Gateway: Authorized Use Only! Violators will be prosecuted for from ; Fri, 10 Aug 2018 02:01:14 -0600 Received: from b03cxnp08025.gho.boulder.ibm.com (9.17.130.17) by e33.co.us.ibm.com (192.168.1.133) 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:11 -0600 Received: from b03ledav006.gho.boulder.ibm.com (b03ledav006.gho.boulder.ibm.com [9.17.130.237]) by b03cxnp08025.gho.boulder.ibm.com (8.14.9/8.14.9/NCO v10.0) with ESMTP id w7A81AQD10813754 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256 verify=FAIL) for ; Fri, 10 Aug 2018 01:01:10 -0700 Received: from b03ledav006.gho.boulder.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id 3DD47C605F for ; Fri, 10 Aug 2018 02:01:10 -0600 (MDT) Received: from b03ledav006.gho.boulder.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id 053D1C6059 for ; Fri, 10 Aug 2018 02:01:09 -0600 (MDT) Received: from birb.localdomain (unknown [9.185.142.88]) by b03ledav006.gho.boulder.ibm.com (Postfix) with ESMTP for ; Fri, 10 Aug 2018 02:01:09 -0600 (MDT) Received: by birb.localdomain (Postfix, from userid 1000) id 34AE74EC6B7; Fri, 10 Aug 2018 18:01:07 +1000 (AEST) From: Stewart Smith To: patchwork@lists.ozlabs.org Subject: [PATCH 03/11] Add index for patchwork_comment (submission_id,date) Date: Fri, 10 Aug 2018 18:00:58 +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-0036-0000-0000-00000A1F9F82 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:12 X-IBM-AV-DETECTION: SAVI=unused REMOTE=unused XFE=unused x-cbparentid: 18081008-0037-0000-0000-00004887A756 Message-Id: <20180810080106.10714-4-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" This (at least theoretically) should speed up displaying comments on patches/cover letters. It's an index that will return rows in-order for the query that we always do ("give me the comments on this submission in date order"), rather than having to have the database server do a sort for us. I haven't been able to benchmark something locally that shows this is an actual improvement, but I don't have as large data set as various production instances. The query plan does look a bit nicer though. Although the benefit of index maintenance versus how long it takes to sort things is a good question. Signed-off-by: Stewart Smith Reviewed-by: Stephen Finucane --- .../migrations/0027_add_comment_date_index.py | 23 +++++++++++++++++++ patchwork/models.py | 4 ++++ 2 files changed, 27 insertions(+) create mode 100644 patchwork/migrations/0027_add_comment_date_index.py diff --git a/patchwork/migrations/0027_add_comment_date_index.py b/patchwork/migrations/0027_add_comment_date_index.py new file mode 100644 index 000000000000..0a57a9c3b212 --- /dev/null +++ b/patchwork/migrations/0027_add_comment_date_index.py @@ -0,0 +1,23 @@ +# -*- coding: utf-8 -*- +# Generated by Django 1.11.15 on 2018-08-09 14:03 +from __future__ import unicode_literals + +from django.db import migrations, models + + +class Migration(migrations.Migration): + + dependencies = [ + ('patchwork', '0026_add_user_bundles_backref'), + ] + + operations = [ + migrations.AlterModelOptions( + name='series', + options={'verbose_name_plural': 'Series'}, + ), + migrations.AddIndex( + model_name='comment', + index=models.Index(fields=['submission', 'date'], name='submission_date_idx'), + ), + ] diff --git a/patchwork/models.py b/patchwork/models.py index d2b88fc48c91..d2389cfdad29 100644 --- a/patchwork/models.py +++ b/patchwork/models.py @@ -625,6 +625,10 @@ class Comment(EmailMixin, models.Model): class Meta: ordering = ['date'] unique_together = [('msgid', 'submission')] + indexes = [ + models.Index(name='submission_date_idx', + fields=['submission','date']) + ] @python_2_unicode_compatible From patchwork Fri Aug 10 08:00:59 2018 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Stewart Smith X-Patchwork-Id: 956048 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 41myLF6PlTz9s5b for ; Fri, 10 Aug 2018 18:03:33 +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 41myLF51yMzF0q0 for ; Fri, 10 Aug 2018 18:03:33 +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.158.5; 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 (mx0b-001b2d01.pphosted.com [148.163.158.5]) (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 41myHg49njzDqww for ; Fri, 10 Aug 2018 18:01:18 +1000 (AEST) Received: from pps.filterd (m0098416.ppops.net [127.0.0.1]) by mx0b-001b2d01.pphosted.com (8.16.0.22/8.16.0.22) with SMTP id w7A7wpAl153389 for ; Fri, 10 Aug 2018 04:01:14 -0400 Received: from e12.ny.us.ibm.com (e12.ny.us.ibm.com [129.33.205.202]) by mx0b-001b2d01.pphosted.com with ESMTP id 2ks6h6rphp-1 (version=TLSv1.2 cipher=AES256-GCM-SHA384 bits=256 verify=NOT) for ; Fri, 10 Aug 2018 04:01:14 -0400 Received: from localhost by e12.ny.us.ibm.com with IBM ESMTP SMTP Gateway: Authorized Use Only! Violators will be prosecuted for from ; Fri, 10 Aug 2018 04:01:13 -0400 Received: from b01cxnp22034.gho.pok.ibm.com (9.57.198.24) by e12.ny.us.ibm.com (146.89.104.199) 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 04:01:11 -0400 Received: from b01ledav006.gho.pok.ibm.com (b01ledav006.gho.pok.ibm.com [9.57.199.111]) by b01cxnp22034.gho.pok.ibm.com (8.14.9/8.14.9/NCO v10.0) with ESMTP id w7A81Au015597736 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256 verify=FAIL) for ; Fri, 10 Aug 2018 08:01:10 GMT Received: from b01ledav006.gho.pok.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id DFB5AAC060 for ; Fri, 10 Aug 2018 04:01:37 -0400 (EDT) Received: from b01ledav006.gho.pok.ibm.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id 9F32EAC05E for ; Fri, 10 Aug 2018 04:01:37 -0400 (EDT) Received: from birb.localdomain (unknown [9.185.142.88]) by b01ledav006.gho.pok.ibm.com (Postfix) with ESMTP for ; Fri, 10 Aug 2018 04:01:37 -0400 (EDT) Received: by birb.localdomain (Postfix, from userid 1000) id 3A9784EC6B9; Fri, 10 Aug 2018 18:01:07 +1000 (AEST) From: Stewart Smith To: patchwork@lists.ozlabs.org Subject: [PATCH 04/11] Fetch all series for patch/cover viewing Date: Fri, 10 Aug 2018 18:00:59 +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-0060-0000-0000-0000029B93F8 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:12 X-IBM-AV-DETECTION: SAVI=unused REMOTE=unused XFE=unused x-cbparentid: 18081008-0061-0000-0000-0000461E79CD Message-Id: <20180810080106.10714-5-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" e.g. a 10 comment patch goes from 26 queries in 17-20ms down to 20 queries in 12ms. A 67 comment cover letter goes from 14 queries in 16ms down to 8 queries in 8ms. So, effectively, a near 2x perf improvement. Previously, at several points we were asking for the latest series and then asking for all the series. Since there just usually aren't *that* many series, fetch them all and take the first one if we need to. Signed-off-by: Stewart Smith Reviewed-by: Stephen Finucane --- patchwork/templates/patchwork/submission.html | 10 +++++----- patchwork/views/cover.py | 2 +- patchwork/views/patch.py | 1 + 3 files changed, 7 insertions(+), 6 deletions(-) diff --git a/patchwork/templates/patchwork/submission.html b/patchwork/templates/patchwork/submission.html index 2f69735d6925..3b6f9fbe909e 100644 --- a/patchwork/templates/patchwork/submission.html +++ b/patchwork/templates/patchwork/submission.html @@ -64,15 +64,15 @@ function toggle_div(link_id, headers_id) -{% if submission.latest_series %} +{% if submission.all_series %} Series
    - {% for series in submission.series.all %} + {% for series in all_series %}
  • - {% if series == submission.latest_series %} + {% if forloop.first %} {{ series }} {% else %} @@ -93,7 +93,7 @@ function toggle_div(link_id, headers_id) >show