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

[George Dunlap <George.Dunlap@xxxxxxxxxx>]

> On Jul 21, 2020, at 7:41 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
> 
> While we're here, convert this EXISTS subquery to a JOIN.
> 
> Perf: runtime of my test case now ~200-300s.
> 
> Example query before (from the Perl DBI trace):
> 
>      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
> 
> With bind variables:
> 
>     "test-armhf-armhf-libvirt"
>     'built_revision_xen'
>     '165f3afbfc3db70fcfdccad07085cde0a03c858b'
> 
> 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. :-)

Again, not sure what the “(1=1) AND” bit is for; something to poke the query 
planner somehow?

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.

 -George