Re: [OSSTEST PATCH 14/14] duration_estimator: Move duration query loop into database

[Ian Jackson <ian.jackson@xxxxxxxxxx>]

George Dunlap writes ("Re: [OSSTEST PATCH 14/14] duration_estimator: Move 
duration query loop into database"):
> > On Jul 21, 2020, at 7:42 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
...
> > Example queries before (from the debugging output):
> > 
> > Query A part I:
> > 
> >            SELECT f.flight AS flight,
> >                   j.job AS job,
> >                   f.started AS started,
> >                   j.status AS status
> >                     FROM flights f
> >                     JOIN jobs j USING (flight)
> >                     JOIN runvars r
> >                             ON  f.flight=r.flight
> >                            AND  r.name=?
> >                    WHERE  j.job=r.job
> 
> Did these last two get mixed up?  My limited experience w/ JOIN ON
> and WHERE would lead me to expect we’re joining on
> `f.flight=r.flight and r.job = j.job`, and having `r.name = ?` as
> part of the WHERE clause.  I see it’s the same in the combined query
> as well.

Well spotted.  However, actually, this makes no difference: with an
inner join, ON clauses are the same as WHERE clauses.  It does seem
stylistically poor though, so I will add a commit to change it.

> > Query common part II:
> > 
> >        WITH tsteps AS
> >        (
> >            SELECT *
> >              FROM steps
> >             WHERE flight=? AND job=?
> >        )
> >        , tsteps2 AS
> >        (
> >            SELECT *
> >              FROM tsteps
> >             WHERE finished <=
> >                     (SELECT finished
> >                        FROM tsteps
> >                       WHERE tsteps.testid = ?)
> >        )
> >        SELECT (
> >            SELECT max(finished)-min(started)
> >              FROM tsteps2
> >          ) - (
> >            SELECT sum(finished-started)
> >              FROM tsteps2
> >             WHERE step = 'ts-hosts-allocate'
> >          )
> >                AS duration
> 
> Er, wait — you were doing a separate `duration` query for each row of the 
> previous query?  Yeah, that sounds like it could be a lot of round trips. :-)

I was doing, yes.  This code was not really very optimised.

> I mean, in both queries (A and B), the transform should basically result in 
> the same thing happening, as far as I can tell.

Good, thanks.

> I can try to analyze the duration query and see if I can come up with any 
> suggestions, but that would be a different patch anyway.

It's fast enough now :-).

Thanks,
Ian.