Re: [OSSTEST PATCH 08/14] Executive: Use index for report__find_test

[George Dunlap <George.Dunlap@xxxxxxxxxx>]

> On Jul 21, 2020, at 7:41 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
> 
> After we refactor this query then we can enable the index use.
> (Both of these things together in this commit because I haven't perf
> tested the version with just the refactoring.)
> 
> (We have provided an index that can answer this question really
> quickly if a version is specified.  But the query planner couldn't see
> that because it works without seeing the bind variables, so doesn't
> know that the value of name is going to be suitable for this index.)
> 
> * Convert the two EXISTS subqueries into JOIN/AND with a DISTINCT
>  clause naming the fields on flights, so as to replicate the previous
>  result rows.  Then do $selection field last.  The subquery is a
>  convenient way to let this do the previous thing for all the values
>  of $selection (including, notably, *).
> 
> * Add the additional AND clause for r.name, which has no logical
>  effect given the actual values of name, enabling the query planner
>  to use this index.
> 
> Perf: In my test case the sg-report-flight runtime is now ~8s.  I am
> reasonably confident that this will not make other use cases of this
> code worse.
> 
> Perf: runtime of my test case now ~11s
> 
> Example query before (from the Perl DBI trace):
> 
>        SELECT *
>         FROM flights f
>        WHERE
>                EXISTS (
>                   SELECT 1
>                    FROM runvars r
>                   WHERE name=?
>                     AND val=?
>                     AND r.flight=f.flight
>                     AND (      (CASE
>       WHEN (r.job) LIKE 'build-%-prev' THEN 'xprev'
>       WHEN ((r.job) LIKE 'build-%-freebsd'
>             AND 'x' = 'freebsdbuildjob') THEN 'DISCARD'
>       ELSE                                      ''
>       END)
> = '')
>                 )
>          AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
>          AND (branch=?)
>        ORDER BY flight DESC
>        LIMIT 1

So this says:

Get me all the columns
for the highest-numbered flight
Where:
  There is at least one runvar for that flight has the specified $name and 
$value
  And the job is *not* like build-%-prev or build-%-freebsd
  The flight number (?) is <= 151903, and blessing = real
  For the specified $branch

What’s the “TRUE and flight <= 151903” for?

> 
> After:
> 
>        SELECT *
>          FROM ( SELECT DISTINCT
>                      flight, started, blessing, branch, intended
>                 FROM flights f
>                    JOIN runvars r USING (flight)
>                   WHERE name=?
>                     AND name LIKE 'revision_%'
>                     AND val=?
>                     AND r.flight=f.flight
>                     AND (      (CASE
>       WHEN (r.job) LIKE 'build-%-prev' THEN 'xprev'
>       WHEN ((r.job) LIKE 'build-%-freebsd'
>             AND 'x' = 'freebsdbuildjob') THEN 'DISCARD'
>       ELSE                                      ''
>       END)
> = '')
>          AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
>          AND (branch=?)
> ) AS sub WHERE TRUE
>        ORDER BY flight DESC
>        LIMIT 1

And this says (effectively)

Get me <flight, started, blessing, branch, intended>
From the highest-numbered flight
Where
  That flight has a runvar with specified name and value
  The job *doesn’t* look like “build-%-prev” or “build-%-freebsd”
  flight & blessing as appropriate
  branch as specified.
  
Isn’t the r.flight = f.flight redundant if we’re joining on flight?

Also, in spite of the paragraph attempting to explain it, I’m afraid I don’t 
understand what the “AS sub WHERE TRUE” is for.

But it looks like the new query should do the same thing as the old query, 
assuming that the columns from the subquery are all the columns that you need 
in the correct order.

 -George