From nobody Tue Apr 16 22:11:14 2024 Delivered-To: importer@patchew.org Received-SPF: pass (zohomail.com: domain of redhat.com designates 170.10.129.124 as permitted sender) client-ip=170.10.129.124; envelope-from=patchew-devel-bounces@redhat.com; helo=us-smtp-delivery-124.mimecast.com; Authentication-Results: mx.zohomail.com; dkim=pass; spf=pass (zohomail.com: domain of redhat.com designates 170.10.129.124 as permitted sender) smtp.mailfrom=patchew-devel-bounces@redhat.com; dmarc=pass(p=none dis=none) header.from=redhat.com ARC-Seal: i=1; a=rsa-sha256; t=1663592444; cv=none; d=zohomail.com; s=zohoarc; b=XxhvYi9orSl9Uvk+t0SuLT8vcBIdE+PLCo+Z4b+YvoRfhrKDnTKjKZRyT7hEPZre5DeiNXVlyefm/udMnMvcodGNb0eoufdPhGvdXp2i9x1Kj1ugIE8iksCf3TzbY57PF6lEaQFQSWAqBwkuS8740gmYDaJ4/ywjcbTwQwQqFxU= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1663592444; h=Content-Type:Content-Transfer-Encoding:Date:From:List-Subscribe:List-Post:List-Id:List-Archive:List-Help:List-Unsubscribe:MIME-Version:Message-ID:Sender:Subject:To; bh=JCHNpFVm+JaMQlDS1ZSy/nA9cfAD23Cu9QLHkVaw83E=; b=T4AmzJq6Yi6VKiuRt+WOOJt9nh+E2IQyoAjucWxjnyReFu/UlloMGoYJNs6L6OA15VPg/ryLI47VCAOWDZ7ujU/Rh73mtwA4QJizmpH+j2v2a93V69/ZwSxBHwJKKio48FKfWvcva3i1CIR5AnaM/FnmuoEBNVqy7Au24NvajFk= ARC-Authentication-Results: i=1; mx.zohomail.com; dkim=pass; spf=pass (zohomail.com: domain of redhat.com designates 170.10.129.124 as permitted sender) smtp.mailfrom=patchew-devel-bounces@redhat.com; dmarc=pass header.from= (p=none dis=none) Return-Path: Received: from us-smtp-delivery-124.mimecast.com (us-smtp-delivery-124.mimecast.com [170.10.129.124]) by mx.zohomail.com with SMTPS id 1663592444382147.74202082320426; Mon, 19 Sep 2022 06:00:44 -0700 (PDT) Received: from mimecast-mx02.redhat.com (mimecast-mx02.redhat.com [66.187.233.88]) by relay.mimecast.com with ESMTP with STARTTLS (version=TLSv1.2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id us-mta-329-QDKtbwTMM6WHZ7t03fjnxA-1; Mon, 19 Sep 2022 09:00:38 -0400 Received: from smtp.corp.redhat.com (int-mx08.intmail.prod.int.rdu2.redhat.com [10.11.54.8]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by mimecast-mx02.redhat.com (Postfix) with ESMTPS id 24C9A100F809; Mon, 19 Sep 2022 13:00:37 +0000 (UTC) Received: from mm-prod-listman-01.mail-001.prod.us-east-1.aws.redhat.com (unknown [10.30.29.100]) by smtp.corp.redhat.com (Postfix) with ESMTP id 1D92EC15BA5; Mon, 19 Sep 2022 13:00:37 +0000 (UTC) Received: from mm-prod-listman-01.mail-001.prod.us-east-1.aws.redhat.com (localhost [IPv6:::1]) by mm-prod-listman-01.mail-001.prod.us-east-1.aws.redhat.com (Postfix) with ESMTP id E19CA1946594; Mon, 19 Sep 2022 13:00:36 +0000 (UTC) Received: from smtp.corp.redhat.com (int-mx07.intmail.prod.int.rdu2.redhat.com [10.11.54.7]) by mm-prod-listman-01.mail-001.prod.us-east-1.aws.redhat.com (Postfix) with ESMTP id A02451946586 for ; Mon, 19 Sep 2022 13:00:35 +0000 (UTC) Received: by smtp.corp.redhat.com (Postfix) id 8DD991400C30; Mon, 19 Sep 2022 13:00:35 +0000 (UTC) Received: from mimecast-mx02.redhat.com (mimecast04.extmail.prod.ext.rdu2.redhat.com [10.11.55.20]) by smtp.corp.redhat.com (Postfix) with ESMTPS id 85EA2140EBF3 for ; Mon, 19 Sep 2022 13:00:35 +0000 (UTC) Received: from us-smtp-1.mimecast.com (us-smtp-delivery-1.mimecast.com [205.139.110.120]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mimecast-mx02.redhat.com (Postfix) with ESMTPS id 6A285100FCED for ; Mon, 19 Sep 2022 13:00:35 +0000 (UTC) Received: from mail-wm1-f72.google.com (mail-wm1-f72.google.com [209.85.128.72]) by relay.mimecast.com with ESMTP with STARTTLS (version=TLSv1.3, cipher=TLS_AES_128_GCM_SHA256) id us-mta-352-BvO3mwicM7Kfdt1Pzqre0w-1; Mon, 19 Sep 2022 09:00:33 -0400 Received: by mail-wm1-f72.google.com with SMTP id 185-20020a1c02c2000000b003b4be28d7e3so4006057wmc.0 for ; Mon, 19 Sep 2022 06:00:33 -0700 (PDT) Received: from goa-sendmail ([2001:b07:6468:f312:9af8:e5f5:7516:fa89]) by smtp.gmail.com with ESMTPSA id p19-20020a05600c1d9300b003b47ff3807fsm14138645wms.5.2022.09.19.06.00.31 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 19 Sep 2022 06:00:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=redhat.com; s=mimecast20190719; t=1663592441; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:mime-version:mime-version: content-type:content-type: content-transfer-encoding:content-transfer-encoding:list-id:list-help: list-unsubscribe:list-subscribe:list-post; bh=JCHNpFVm+JaMQlDS1ZSy/nA9cfAD23Cu9QLHkVaw83E=; b=Zq/HY4hrlTgGq6/N8H6CU/413Ct34LWlIzDVhD/k36MBy3yp0GBiaY+8l1wSu6hq8s4FCJ JGG4memYx7muSBcpRa7UY2Ou/R1JlrkwnYi/Lcesk5tdA+G0FJ4ws81zJt2UlbvAUDRQkH 5tH2amZRN+OQ4HU9on5oY9yoyDW1ppU= X-MC-Unique: QDKtbwTMM6WHZ7t03fjnxA-1 X-Original-To: patchew-devel@listman.corp.redhat.com X-MC-Unique: BvO3mwicM7Kfdt1Pzqre0w-1 X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:mime-version:message-id:date:subject:to :from:x-gm-message-state:from:to:cc:subject:date; bh=HSCknJkMI6lH7Z4QeJnk1z6arAoDLbX52OMxrYTpeNo=; b=4FM5GZCKUgsWEd6OMPKxcC+Bgbh6lEsbIe4t1S+bpbKQNml9uVt8JtNYypGIzmJseR e7Pa0jHIoGXQSxLTOxPoZUcPyriC3SOe3F0iQFcnlSDSQr7IJwIGTqTCgB8m2d26kOms L215F+91i8sNOek5fRT+6DxdlcWI9O7BsOMGLZltCq8+KkOwviHIfj53EeeaeDJybprH EOOFHHPLG4ze1Qm5lQnAO7AAREq91PypeiBKV1SH1q++EVAJ/pnU9mG0rtr2YVPN/IMD r44zTbg0E5s3qz0rpRFJVUXpBJvjqrVKVVQFhjHNaaPVwgYeEiBi8/8kyWJUOo6s7iNa T2eQ== X-Gm-Message-State: ACrzQf0RHF6qfaQdT/UAo0teIP4+HgkqEsq2IQ8Okbq7gf6tVriLmhrA vQZN9zTCvHJhc3uE6/lqeAZ4LuTV1II4wYj4fcthSbQ7UrXGhDJ+ySfQnGtIxVcw0QhQn/+5Jz7 FqUC8nFWaSex7caf6N2El7RvKHykjWIoBC2xp+ZCtxmC7QBOSabRkJkFW2EnexxCcwxIqIXO9yA == X-Received: by 2002:a05:600c:2212:b0:3b4:9ab8:b24e with SMTP id z18-20020a05600c221200b003b49ab8b24emr12057124wml.127.1663592432319; Mon, 19 Sep 2022 06:00:32 -0700 (PDT) X-Google-Smtp-Source: AMsMyM6MBJPb+vPTJQOXL2Khb0EciF/gHPKMafEMiPvvOYVpKKdr71y4mrdYq+joBo5kVXtpODO7zg== X-Received: by 2002:a05:600c:2212:b0:3b4:9ab8:b24e with SMTP id z18-20020a05600c221200b003b49ab8b24emr12057096wml.127.1663592431995; Mon, 19 Sep 2022 06:00:31 -0700 (PDT) From: Paolo Bonzini To: patchew-devel@redhat.com Date: Mon, 19 Sep 2022 15:00:29 +0200 Message-Id: <20220919130030.70997-1-pbonzini@redhat.com> MIME-Version: 1.0 X-Scanned-By: MIMEDefang 3.1 on 10.11.54.7 Subject: [Patchew-devel] [PATCH] models: use subqueries and IN to find message for a project and its parent X-BeenThere: patchew-devel@redhat.com X-Mailman-Version: 2.1.29 Precedence: list List-Id: Patchew development and discussion list List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: patchew-devel-bounces@redhat.com Sender: "Patchew-devel" X-Scanned-By: MIMEDefang 3.1 on 10.11.54.8 X-Mimecast-Spam-Score: 0 X-Mimecast-Originator: redhat.com Content-Transfer-Encoding: quoted-printable X-ZohoMail-DKIM: pass (identity @redhat.com) X-ZM-MESSAGEID: 1663592444901100001 Content-Type: text/plain; charset="utf-8" The database likes this a bit more. Before: SELECT COUNT(*) AS "__count" FROM "api_message" INNER JOIN "api_project" ON ("api_message"."project_id" =3D "api_projec= t"."id") WHERE (("api_message"."project_id" =3D 1 OR "api_project"."parent_proje= ct_id" =3D 1) AND "api_message"."topic_id" IS NOT NULL); with a cache-hot runtime of ~1 second and the following plan: Aggregate (cost=3D64143.99..64144.00 rows=3D1 width=3D8) -> Hash Join (cost=3D6.65..64030.37 rows=3D45449 width=3D0) Hash Cond: (api_message.project_id =3D api_project.id) Join Filter: ((api_message.project_id =3D 1) OR (api_project.paren= t_project_id =3D 1)) -> Index Only Scan using api_message_topic_id_project_id_last_rep= ly_date_871c0af8_idx on api_message (cost=3D0.43..63676.05 rows=3D104690 w= idth=3D4) Index Cond: (topic_id IS NOT NULL) -> Hash (cost=3D6.10..6.10 rows=3D10 width=3D8) -> Seq Scan on api_project (cost=3D0.00..6.10 rows=3D10 wi= dth=3D8) After, the subquery is indeed executed first, giving a cache-hot time of ~250 ms: SELECT COUNT(*) AS "__count" FROM "api_message" WHERE ("api_message"."project_id" IN (SELECT U0."id" FROM "api_project"= U0 LEFT OUTER JOIN "api_project" U1 ON (U0."parent_project_id" =3D U1."id"= ) WHERE (U0."name" =3D 'QEMU' OR U1."name" =3D 'QEMU'))) AND "api_message"."topic_id" IS NOT NULL; Aggregate (cost=3D19272.83..19272.84 rows=3D1 width=3D8) -> Nested Loop (cost=3D12.79..19220.47 rows=3D20945 width=3D0) -> Unique (cost=3D12.36..12.37 rows=3D2 width=3D4) -> Sort (cost=3D12.36..12.37 rows=3D2 width=3D4) Sort Key: u0.id -> Hash Left Join (cost=3D6.22..12.35 rows=3D2 width= =3D4) Hash Cond: (u0.parent_project_id =3D u1.id) Filter: (((u0.name)::text =3D 'QEMU'::text) OR (= (u1.name)::text =3D 'QEMU'::text)) -> Seq Scan on api_project u0 (cost=3D0.00..6.= 10 rows=3D10 width=3D14) -> Hash (cost=3D6.10..6.10 rows=3D10 width=3D1= 0) -> Seq Scan on api_project u1 (cost=3D0.= 00..6.10 rows=3D10 width=3D10) -> Index Only Scan using api_message_topic_id_project_id_last_rep= ly_date_871c0af8_idx on api_message (cost=3D0.43..9499.33 rows=3D10472 wid= th=3D4) Index Cond: ((topic_id IS NOT NULL) AND (project_id =3D u0.i= d)) Signed-off-by: Paolo Bonzini --- api/models.py | 12 +++++++++++- api/search.py | 6 +++--- 2 files changed, 14 insertions(+), 4 deletions(-) diff --git a/api/models.py b/api/models.py index b1b42c1..6bb9867 100644 --- a/api/models.py +++ b/api/models.py @@ -14,6 +14,7 @@ import re =20 from django.core import validators from django.db import models +from django.db.models import Q from django.contrib.auth.models import User from django.urls import reverse import jsonfield @@ -300,6 +301,14 @@ class Project(models.Model): def get_subprojects(self): return Project.objects.filter(parent_project=3Dself) =20 + @classmethod + def get_project_ids_by_id(cls, id): + return cls.objects.filter(Q(id=3Did) | Q(parent_project__id=3Did))= .values_list('id') + + @classmethod + def get_project_ids_by_name(cls, name): + return cls.objects.filter(Q(name=3Dname) | Q(parent_project__name= =3Dname)).values_list('id') + def get_project_head(self): return self.get_property("git.head") =20 @@ -411,8 +420,9 @@ class MessageManager(models.Manager): if po is None: return None =20 + ids =3D po.get_project_ids_by_id(po.id) q =3D self.get_queryset() - q =3D q.filter(project=3Dpo) | q.filter(project__parent_project=3D= po) + q =3D q.filter(project__pk__in=3Dids) return q =20 def series_heads(self, project=3DNone): diff --git a/api/search.py b/api/search.py index 573b1a2..cfd963b 100644 --- a/api/search.py +++ b/api/search.py @@ -8,7 +8,7 @@ # This work is licensed under the MIT License. Please see the LICENSE fil= e or # http://opensource.org/licenses/MIT. =20 -from .models import Message, MessageResult, Result, QueuedSeries +from .models import Message, MessageResult, Project, Result, QueuedSeries from collections import namedtuple from functools import reduce import operator @@ -269,10 +269,10 @@ def __parser(_Q): return q =20 def _make_filter_project(cond): + ids =3D Project.get_project_ids_by_name(cond) return SearchTerm( project=3Dcond, - query=3D_Q(project__name=3Dcond) | _Q(project__parent_project_= _name=3Dcond), - ) + query=3D_Q(project__pk__in=3Dids)) =20 def _make_filter_is(cond): if cond =3D=3D "complete": --=20 2.37.2 _______________________________________________ Patchew-devel mailing list Patchew-devel@redhat.com https://listman.redhat.com/mailman/listinfo/patchew-devel