[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [OSSTEST PATCH 06/14] sg-report-flight: Use WITH clause to use index for $anypassq


  • To: Ian Jackson <Ian.Jackson@xxxxxxxxxx>
  • From: George Dunlap <George.Dunlap@xxxxxxxxxx>
  • Date: Mon, 27 Jul 2020 16:15:36 +0000
  • Accept-language: en-GB, en-US
  • Authentication-results: esa5.hc3370-68.iphmx.com; dkim=none (message not signed) header.i=none
  • Cc: "xen-devel@xxxxxxxxxxxxxxxxxxxx" <xen-devel@xxxxxxxxxxxxxxxxxxxx>
  • Delivery-date: Mon, 27 Jul 2020 16:15:46 +0000
  • Ironport-sdr: blYReVZ7jcnzuKuWDXWEqtriAH915lGvknKJaAFw2SYEgAHHaU9vrLn3JpnHPh1nWnF8SL+2gU yG87+gTaiEVUjfcBQMX0NZVWBuYM0vY4fYv/TodITVtcn+8RWZe2YWUF7nX3ghf6tToa5EawIa ZzERfsyABbIYxxYpouSgQCW9czUmi9o2IH7qIMHDQcZ55y0SUbt9irfzBYza0MkpgDSqxJb8mP ba7GuQFSSOj7y/6EX9nvgTZyybCm6TyTb17z1aaCxw1DEmzkcq9OUeBKfwrxqoboopkc84IIuR Iu4=
  • List-id: Xen developer discussion <xen-devel.lists.xenproject.org>
  • Thread-index: AQHWX46tFgmYVwZS+kmF2kdEj3oAf6kbga0A
  • Thread-topic: [OSSTEST PATCH 06/14] sg-report-flight: Use WITH clause to use index for $anypassq


> On Jul 21, 2020, at 7:41 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
> 
> 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='xen-unstable')
>              AND job=? and testid=? and status='pass'
>              AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
>            LIMIT 1
> 
> After:
> 
>        WITH s AS
>        (
>        SELECT * FROM steps
>         WHERE job=? and testid=? and status='pass'
>        )
>        SELECT * FROM flights JOIN s USING (flight)
>            WHERE (branch='xen-unstable')
>              AND ( (TRUE AND flight <= 151903) AND (blessing='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=? and testid=? and status='pass'
> +        )
> +        SELECT * FROM flights JOIN s USING (flight)
>             WHERE (branch='xen-unstable')
> -              AND job=? and testid=? and status='pass'
>               AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
>             LIMIT 1
> 
> CC: George Dunlap <George.Dunlap@xxxxxxxxxx>
> Signed-off-by: Ian Jackson <ian.jackson@xxxxxxxxxxxxx>
> ---
> 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.
> 
> diff --git a/sg-report-flight b/sg-report-flight
> index b5398573..b8d948da 100755
> --- a/sg-report-flight
> +++ b/sg-report-flight
> @@ -849,10 +849,20 @@ sub justifyfailures ($;$) {
> 
>     my @failures= values %{ $fi->{Failures} };
> 
> +    # 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.)

Seems a bit weird, but OK.  The SQL looks the same, so:

Reviewed-by: George Dunlap <george.dunlap@xxxxxxxxxx>




 


Rackspace

Lists.xenproject.org is hosted with RackSpace, monitoring our
servers 24x7x365 and backed by RackSpace's Fanatical Support®.