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

[Ian Jackson <ian.jackson@xxxxxxxxxx>]

George Dunlap writes ("Re: [OSSTEST PATCH 04/14] sg-report-flight: Ask the db 
for flights of interest"):
> > On Jul 21, 2020, at 7:41 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
> > Example query before (from the Perl DBI trace):
> > 
> >      SELECT * FROM (
> >        SELECT flight, blessing FROM flights
...
> >              AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
...
> But why are we selecting ‘blessing’ from these, if we’ve specified that 
> blessing = “real”? Isn’t that redundant?

That condition is programmatically constructed.  Sometimes it will ask
for multiple different blessings and then it wants to know which.

> > After:
...
> 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.

Yes.

> 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.

That's my intent, ytes.

> 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?

Right.  The previous approach was "iterate over recent flights,
figure out precisely what they built, and decide if they meet the
(complex) requirements".

Now we only iterate over a subset of recent flights: those which have
at least one such runvar.  The big commennt is meant to be a
demonstration that the "(complex) requirements" are a narrower
condition than the new condition on the initial flights query.

So I think the result is that it will look deeper into history, and be
faster, but not otherwise change its beaviour.

Ian.