From nobody Mon Feb 9 14:37:49 2026 Delivered-To: importer@patchew.org Received-SPF: pass (zohomail.com: domain of lists.xenproject.org designates 192.237.175.120 as permitted sender) client-ip=192.237.175.120; envelope-from=xen-devel-bounces@lists.xenproject.org; helo=lists.xenproject.org; Authentication-Results: mx.zohomail.com; spf=pass (zohomail.com: domain of lists.xenproject.org designates 192.237.175.120 as permitted sender) smtp.mailfrom=xen-devel-bounces@lists.xenproject.org; dmarc=fail(p=none dis=none) header.from=eu.citrix.com ARC-Seal: i=1; a=rsa-sha256; t=1596197586; cv=none; d=zohomail.com; s=zohoarc; b=TQ8nwIG0bSzwZe3urmgdRNUBselENBNY0b36sdJz18B18TNlI7AWgfwQ63Do6S24RJYwYr81jzz0yGcff/8wTUKchv4Xrgz0jLUJ7GbzdpEcXq9LaNKlMNX7YCaMZQUaTXvLXwlMjG+1NZr/czhi9ViP4jkucHoNZzWH25XiMh4= ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=zohomail.com; s=zohoarc; t=1596197586; h=Content-Transfer-Encoding:Cc:Date:From:In-Reply-To:List-Subscribe:List-Post:List-Id:List-Help:List-Unsubscribe:MIME-Version:Message-ID:References:Sender:Subject:To; bh=+kttsVmDuhymp4hMjzvAO2S1n/FOyNQj1mRFZwJUAM8=; b=HF9g6F3fTFqAG2Bee/OQfUyiR3qWdPfL+JSCMF0+3wwFPLid49q1lOM7hvbY4udliWwxDGph1Q2hE5YaT7LrSo1ZvdfViZ6NNokJt8Drymp61u9E9ntCz2JrMXzjJwyM1l3RbMPrqM/kTRhiVQFo/dxvwIc9fnmPJbyHQqpQ9ME= ARC-Authentication-Results: i=1; mx.zohomail.com; spf=pass (zohomail.com: domain of lists.xenproject.org designates 192.237.175.120 as permitted sender) smtp.mailfrom=xen-devel-bounces@lists.xenproject.org; dmarc=fail header.from= (p=none dis=none) header.from= Return-Path: Received: from lists.xenproject.org (lists.xenproject.org [192.237.175.120]) by mx.zohomail.com with SMTPS id 1596197586121865.2847382877674; Fri, 31 Jul 2020 05:13:06 -0700 (PDT) Received: from localhost ([127.0.0.1] helo=lists.xenproject.org) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1k1Tu9-00049E-IC; Fri, 31 Jul 2020 12:12:53 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1k1Tu8-000489-8T for xen-devel@lists.xenproject.org; Fri, 31 Jul 2020 12:12:52 +0000 Received: from chiark.greenend.org.uk (unknown [2001:ba8:1e3::]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 27b4b37a-d327-11ea-8e2c-bc764e2007e4; Fri, 31 Jul 2020 12:12:51 +0000 (UTC) Received: from [172.18.45.5] (helo=zealot.relativity.greenend.org.uk) by chiark.greenend.org.uk (Debian Exim 4.84_2 #1) with esmtp (return-path ijackson@chiark.greenend.org.uk) id 1k1TMr-0001W4-UZ; Fri, 31 Jul 2020 12:38:30 +0100 X-Inumbo-ID: 27b4b37a-d327-11ea-8e2c-bc764e2007e4 From: Ian Jackson To: xen-devel@lists.xenproject.org Subject: [OSSTEST PATCH v2 10/41] sg-report-flight: Use WITH clause to use index for $anypassq Date: Fri, 31 Jul 2020 12:37:49 +0100 Message-Id: <20200731113820.5765-11-ian.jackson@eu.citrix.com> X-Mailer: git-send-email 2.20.1 In-Reply-To: <20200731113820.5765-1-ian.jackson@eu.citrix.com> References: <20200731113820.5765-1-ian.jackson@eu.citrix.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable X-BeenThere: xen-devel@lists.xenproject.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: Xen developer discussion List-Unsubscribe: , List-Post: List-Help: List-Subscribe: , Cc: Ian Jackson , George Dunlap Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" Content-Type: text/plain; charset="utf-8" Perf: runtime of my test case now ~11s Example query before (from the Perl DBI trace): SELECT * FROM flights JOIN steps USING (flight) WHERE (branch=3D'xen-unstable') AND job=3D? and testid=3D? and status=3D'pass' AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) LIMIT 1 After: WITH s AS ( SELECT * FROM steps WHERE job=3D? and testid=3D? and status=3D'pass' ) SELECT * FROM flights JOIN s USING (flight) WHERE (branch=3D'xen-unstable') AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) LIMIT 1 In both cases with bind vars: "test-amd64-i386-xl-pvshim" "guest-start" Diff to the query: - SELECT * FROM flights JOIN steps USING (flight) + WITH s AS + ( + SELECT * FROM steps + WHERE job=3D? and testid=3D? and status=3D'pass' + ) + SELECT * FROM flights JOIN s USING (flight) WHERE (branch=3D'xen-unstable') - AND job=3D? and testid=3D? and status=3D'pass' AND ( (TRUE AND flight <=3D 151903) AND (blessing=3D'real') ) LIMIT 1 Signed-off-by: Ian Jackson Reviewed-by: George Dunlap --- schema/steps-job-index.sql | 2 +- sg-report-flight | 14 ++++++++++++-- 2 files changed, 13 insertions(+), 3 deletions(-) diff --git a/schema/steps-job-index.sql b/schema/steps-job-index.sql index 07dc5a30..2c33af72 100644 --- a/schema/steps-job-index.sql +++ b/schema/steps-job-index.sql @@ -1,4 +1,4 @@ --- ##OSSTEST## 006 Preparatory +-- ##OSSTEST## 006 Needed -- -- This index helps sg-report-flight find if a test ever passed. =20 diff --git a/sg-report-flight b/sg-report-flight index d06be292..d218b24e 100755 --- a/sg-report-flight +++ b/sg-report-flight @@ -849,10 +849,20 @@ sub justifyfailures ($;$) { =20 my @failures=3D values %{ $fi->{Failures} }; =20 + # In psql 9.6 this WITH clause makes postgresql do the steps query + # first. This is good because if this test never passed we can + # determine that really quickly using the new index, without + # having to scan the flights table. (If the test passed we will + # probably not have to look at many flights to find one, so in + # that case this is not much worse.) my $anypassq=3D <