Re: [OSSTEST PATCH 05/14] sg-report-flight: Use WITH to use best index use for $flightsq

[Ian Jackson <ian.jackson@citrix.com>]

George Dunlap writes ("Re: [OSSTEST PATCH 05/14] sg-report-flight: Use WITH to use best index use for $flightsq"):
> On Jul 21, 2020, at 7:41 PM, Ian Jackson <ian.jackson@eu.citrix.com> wrote:
> > After:
> >      WITH sub AS (
> >        SELECT DISTINCT flight, blessing
> >             FROM flights
> >             JOIN runvars r1 USING (flight)
> > 
> >            WHERE (branch='xen-unstable')
> >              AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
> >              AND r1.name LIKE 'built_revision_%'
> >              AND r1.name = ?
> >              AND r1.val= ?
> > 
> >            ORDER BY flight DESC
> >            LIMIT 1000
> >      )
> >      SELECT *
> >        FROM sub
> >        JOIN jobs USING (flight)
> > 
> >       WHERE (1=1)
> >                  AND jobs.job = ?
> > 
> >      ORDER BY blessing ASC, flight DESC
> 
> I was wondering if it would be useful converting this to a join would be useful. :-)
...
> The main thing I see here is that there’s nothing *in the query*
> that guarantees you won’t get multiple flights if there are multiple
> jobs for that flight whose ‘job’ value; but given the naming scheme
> so far, I’m guessing job is unique…?  As long as there’s something
> else preventing duplication I think it’s fine.

(flight,job) is the primary key for the jobs table.

I can probably produce a schema dump if that would make reading this
stuff easier.

Ian.