Re: [OSSTEST PATCH 04/14] sg-report-flight: Ask the db for flights of interest

[George Dunlap]

> On Jul 21, 2020, at 7:41 PM, Ian Jackson <ian.jack...@eu.citrix.com> wrote:
> 
> Specifically, we narrow the initial query to flights which have at
> least some job with the built_revision_foo we are looking for.
> 
> This condition is strictly broader than that implemented inside the
> flight search loop, so there is no functional change.
> 
> Perf: runtime of my test case now ~300s-500s.
> 
> Example query before (from the Perl DBI trace):
> 
>      SELECT * FROM (
>        SELECT flight, blessing FROM flights
>            WHERE (branch='xen-unstable')
>              AND                   EXISTS (SELECT 1
>                            FROM jobs
>                           WHERE jobs.flight = flights.flight
>                             AND jobs.job = ?)
> 
>              AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
>            ORDER BY flight DESC
>            LIMIT 1000
>      ) AS sub
>      ORDER BY blessing ASC, flight DESC

This one says:

Find the 1000 most recent flights
Where 
  branch is "xen-unstable”
  one of its jobs is $job
  And blessing is “real”

But why are we selecting ‘blessing’ from these, if we’ve specified that 
blessing = “real”? Isn’t that redundant?

> 
> With these bind variables:
> 
>    "test-armhf-armhf-libvirt"
> 
> After:
> 
>      SELECT * FROM (
>        SELECT DISTINCT flight, blessing
>             FROM flights
>             JOIN runvars r1 USING (flight)
> 
>            WHERE (branch='xen-unstable')
>              AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
>                  AND EXISTS (SELECT 1
>                            FROM jobs
>                           WHERE jobs.flight = flights.flight
>                             AND jobs.job = ?)
> 
>              AND r1.name LIKE 'built_revision_%'
>              AND r1.name = ?
>              AND r1.val= ?
> 
>            ORDER BY flight DESC
>            LIMIT 1000
>      ) AS sub
>      ORDER BY blessing ASC, flight DESC

So this says:

Find me the most 1000 recent flights
Where:
  branch is “xen-unstable”
  flight <= 15903
  blessing is “real”
  One of its jobs is $job
  It has a runvar matching given $name and $val

And of course it uses the ’name LIKE ‘built_revision_%’ index.

Still don’t understand the ’TRUE AND’ and ‘AS sub’ bits, but it looks to me 
like it’s substantially the same query, with additional $name = $val runvar 
restriction.

And given that you say, "This condition is strictly broader than that 
implemented inside the flight search loop”, I take it that it’s again mainly to 
take advantage of the new index?

 -George