From: Ian Jackson <firstname.lastname@example.org> To: George Dunlap <George.Dunlap@citrix.com> Cc: "email@example.com" <firstname.lastname@example.org> Subject: Re: [OSSTEST PATCH 14/14] duration_estimator: Move duration query loop into database Date: Fri, 31 Jul 2020 11:39:42 +0100 Message-ID: <email@example.com> (raw) In-Reply-To: <7A4B6786-4456-44E4-A85D-9CC83B522FBB@citrix.com> 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 <firstname.lastname@example.org> 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.
next prev parent reply index Thread overview: 26+ messages / expand[flat|nested] mbox.gz Atom feed top 2020-07-21 18:41 [OSSTEST PATCH 00/14] Flight report performance improvements Ian Jackson 2020-07-21 18:41 ` [OSSTEST PATCH 01/14] sg-report-flight: Add a comment re same-flight search narrowing Ian Jackson 2020-07-21 18:41 ` [OSSTEST PATCH 02/14] sg-report-flight: Sort failures by job name as last resort Ian Jackson 2020-07-21 18:41 ` [OSSTEST PATCH 03/14] schema: Provide indices for sg-report-flight Ian Jackson 2020-07-21 18:41 ` [OSSTEST PATCH 04/14] sg-report-flight: Ask the db for flights of interest Ian Jackson 2020-07-22 12:10 ` George Dunlap 2020-07-22 14:03 ` Ian Jackson 2020-07-21 18:41 ` [OSSTEST PATCH 05/14] sg-report-flight: Use WITH to use best index use for $flightsq Ian Jackson 2020-07-22 12:47 ` George Dunlap 2020-07-22 14:06 ` Ian Jackson 2020-07-21 18:41 ` [OSSTEST PATCH 06/14] sg-report-flight: Use WITH clause to use index for $anypassq Ian Jackson 2020-07-27 16:15 ` George Dunlap 2020-07-31 10:41 ` Ian Jackson 2020-07-21 18:41 ` [OSSTEST PATCH 07/14] sg-report-flight: Use the job row from the intitial query Ian Jackson 2020-07-21 18:41 ` [OSSTEST PATCH 08/14] Executive: Use index for report__find_test Ian Jackson 2020-07-22 11:33 ` George Dunlap 2020-07-22 13:49 ` Ian Jackson 2020-07-21 18:42 ` [OSSTEST PATCH 09/14] duration_estimator: Ignore truncated jobs unless we know the step Ian Jackson 2020-07-21 18:42 ` [OSSTEST PATCH 10/14] duration_estimator: Introduce some _qtxt variables Ian Jackson 2020-07-21 18:42 ` [OSSTEST PATCH 11/14] duration_estimator: Explicitly provide null in general host q Ian Jackson 2020-07-21 18:42 ` [OSSTEST PATCH 12/14] duration_estimator: Return job column in first query Ian Jackson 2020-07-21 18:42 ` [OSSTEST PATCH 13/14] duration_estimator: Move $uptincl_testid to separate @x_params Ian Jackson 2020-07-21 18:42 ` [OSSTEST PATCH 14/14] duration_estimator: Move duration query loop into database Ian Jackson 2020-07-27 17:43 ` George Dunlap 2020-07-31 10:39 ` Ian Jackson [this message] 2020-07-31 10:45 ` George Dunlap
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --email@example.com \ --firstname.lastname@example.org \ --cc=George.Dunlap@citrix.com \ --email@example.com \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
Xen-Devel Archive on lore.kernel.org Archives are clonable: git clone --mirror https://lore.kernel.org/xen-devel/0 xen-devel/git/0.git git clone --mirror https://lore.kernel.org/xen-devel/1 xen-devel/git/1.git # If you have public-inbox 1.1+ installed, you may # initialize and index your mirror using the following commands: public-inbox-init -V2 xen-devel xen-devel/ https://lore.kernel.org/xen-devel \ firstname.lastname@example.org email@example.com public-inbox-index xen-devel Example config snippet for mirrors Newsgroup available over NNTP: nntp://nntp.lore.kernel.org/org.xenproject.lists.xen-devel AGPL code for this site: git clone https://public-inbox.org/public-inbox.git