From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on aws-us-west-2-korg-lkml-1.web.codeaurora.org X-Spam-Level: X-Spam-Status: No, score=-6.8 required=3.0 tests=BAYES_00,DKIM_INVALID, DKIM_SIGNED,HEADER_FROM_DIFFERENT_DOMAINS,MAILING_LIST_MULTI,SIGNED_OFF_BY, SPF_HELO_NONE,SPF_PASS,URIBL_BLOCKED autolearn=no autolearn_force=no version=3.4.0 Received: from mail.kernel.org (mail.kernel.org [198.145.29.99]) by smtp.lore.kernel.org (Postfix) with ESMTP id AE040C433DF for ; Fri, 31 Jul 2020 14:21:49 +0000 (UTC) Received: from lists.xenproject.org (lists.xenproject.org [192.237.175.120]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mail.kernel.org (Postfix) with ESMTPS id 7E23C208E4 for ; Fri, 31 Jul 2020 14:21:49 +0000 (UTC) Authentication-Results: mail.kernel.org; dkim=fail reason="signature verification failed" (1024-bit key) header.d=citrix.com header.i=@citrix.com header.b="hCDIny1m" DMARC-Filter: OpenDMARC Filter v1.3.2 mail.kernel.org 7E23C208E4 Authentication-Results: mail.kernel.org; dmarc=fail (p=none dis=none) header.from=citrix.com Authentication-Results: mail.kernel.org; spf=pass smtp.mailfrom=xen-devel-bounces@lists.xenproject.org Received: from localhost ([127.0.0.1] helo=lists.xenproject.org) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1k1Vub-0003qs-MN; Fri, 31 Jul 2020 14:21:29 +0000 Received: from us1-rack-iad1.inumbo.com ([172.99.69.81]) by lists.xenproject.org with esmtp (Exim 4.92) (envelope-from ) id 1k1Vua-0003qM-7L for xen-devel@lists.xenproject.org; Fri, 31 Jul 2020 14:21:28 +0000 X-Inumbo-ID: 1e777d76-d339-11ea-8e4a-bc764e2007e4 Received: from esa4.hc3370-68.iphmx.com (unknown [216.71.155.144]) by us1-rack-iad1.inumbo.com (Halon) with ESMTPS id 1e777d76-d339-11ea-8e4a-bc764e2007e4; Fri, 31 Jul 2020 14:21:27 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=citrix.com; s=securemail; t=1596205286; h=from:to:cc:subject:date:message-id:references: in-reply-to:content-id:content-transfer-encoding: mime-version; bh=SafftgoayHAnHb15nG5Edak7dqbBFqff/CHbv1T2iLs=; b=hCDIny1mvTK3tzkElUyNVk9K3iTlf0H7JIUT7zYwrUqSOw5/h29lPCjL ZgvEu8xnHbuehv0E47Xv5I5Dmq4wGuRSSzDEB36nAe32Br5fOXj1Diexz YsOiaSmXkzMDida4iSQ5nHxXEQrwg5Nq6F8WlvDHN6X2gCcnXLMvy7Klu 8=; Authentication-Results: esa4.hc3370-68.iphmx.com; dkim=none (message not signed) header.i=none IronPort-SDR: rsUxuyQPNnXVk7MgZUD3od0Dw/CdEDO2LVUhHiV7bWM5rSRasc2Dla3iAP83zsmiJcYV259hyb Yxm9HB0H1g0+n3f7n+YsGtshAJzzF8aVWFjAvz8CSjO2VK5+uhoJ26jQ+EcrZotbloX96zmemH /x+EKBEsb3qa3gcsiZtf+BEzkIf8eOL5CVVVG8jX9/XoTJurU+HKxCYfdJSNgR5jbrSj8cu5vc aDdWJEE3QYgbe/kG1TDAdH8npKlUbuR3CXwIwqpJ0bTMbRSreEHnGUebeYgi+5mlb+ijJ5bAxH My8= X-SBRS: 3.7 X-MesageID: 24508979 X-Ironport-Server: esa4.hc3370-68.iphmx.com X-Remote-IP: 162.221.158.21 X-Policy: $RELAYED X-IronPort-AV: E=Sophos;i="5.75,418,1589256000"; d="scan'208";a="24508979" From: George Dunlap To: Ian Jackson Subject: Re: [OSSTEST PATCH v2 07/41] schema: Provide indices for sg-report-flight Thread-Topic: [OSSTEST PATCH v2 07/41] schema: Provide indices for sg-report-flight Thread-Index: AQHWZy8fK8s8vZ3fmUmSnUsHUP7xn6khm9aA Date: Fri, 31 Jul 2020 14:21:23 +0000 Message-ID: <05461545-D39A-4B98-BC27-3560C367FE25@citrix.com> References: <20200731113820.5765-1-ian.jackson@eu.citrix.com> <20200731113820.5765-8-ian.jackson@eu.citrix.com> In-Reply-To: <20200731113820.5765-8-ian.jackson@eu.citrix.com> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-mailer: Apple Mail (2.3608.80.23.2.2) x-ms-exchange-messagesentrepresentingtype: 1 x-ms-exchange-transport-fromentityheader: Hosted Content-Type: text/plain; charset="us-ascii" Content-ID: <51CFC83B5620394FAEF50EE1006261FC@citrix.com> Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 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: "xen-devel@lists.xenproject.org" Errors-To: xen-devel-bounces@lists.xenproject.org Sender: "Xen-devel" > On Jul 31, 2020, at 12:37 PM, Ian Jackson wro= te: >=20 > These indexes allow very fast lookup of "relevant" flights eg when > trying to justify failures. >=20 > In my ad-hoc test case, these indices (along with the subsequent > changes to sg-report-flight and Executive.pm, reduce the runtime of > sg-report-flight from 2-3ks (unacceptably long!) to as little as > 5-7s seconds - a speedup of about 500x. >=20 > (Getting the database snapshot may take a while first, but deploying > this code should help with that too by reducing long-running > transactions. Quoted perf timings are from snapshot acquisition.) >=20 > Without these new indexes there may be a performance change from the > query changes. I haven't benchmarked this so I am setting the schema > updates to be Preparatory/Needed (ie, "Schema first" as > schema/README.updates has it), to say that the index should be created > before the new code is deployed. >=20 > Testing: I have tested this series by creating experimental indices > "trial_..." in the actual production instance. (Transactional DDL was > very helpful with this.) I have verified with \d that schema update > instructions in this commit generate indexes which are equivalent to > the trial indices. >=20 > Deployment: AFter these schema updates are applied, the trial indices > are redundant duplicates and should be deleted. >=20 > CC: George Dunlap > Signed-off-by: Ian Jackson I have no idea if building an index on a LIKE is a good idea or not, but it= certainly seems to be useful, so: Reviewed-by: George Dunlap