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