From: Ian Jackson <email@example.com> To: George Dunlap <George.Dunlap@citrix.com> Cc: "firstname.lastname@example.org" <email@example.com> Subject: Re: [OSSTEST PATCH 08/14] Executive: Use index for report__find_test Date: Wed, 22 Jul 2020 14:49:51 +0100 Message-ID: <firstname.lastname@example.org> (raw) In-Reply-To: <3ACBEEA3-C17D-48AE-8AE5-52C9D92C8C46@citrix.com> George Dunlap writes ("Re: [OSSTEST PATCH 08/14] Executive: Use index for report__find_test"): > > On Jul 21, 2020, at 7:41 PM, Ian Jackson <email@example.com> wrote: > > Example query before (from the Perl DBI trace): ... > So this says: > > Get me all the columns > for the highest-numbered flight > Where: > There is at least one runvar for that flight has the specified $name and $value > And the job is *not* like build-%-prev or build-%-freebsd > The flight number (?) is <= 151903, and blessing = real > For the specified $branch Yes. > What’s the “TRUE and flight <= 151903” for? These queries are programmetically constructed. In this case, the flight condition is not always there. My test case had a --max-flight=151903 on the command line: this is a debugging option. It avoids newly added stuff in the db confusing me and generally disturbing things. This is implemented with a condition variable which contains either "" or "and flight <= 151903". Doing it this way simplifies the generation code. > And this says (effectively) > > Get me <flight, started, blessing, branch, intended> > From the highest-numbered flight > Where > That flight has a runvar with specified name and value > The job *doesn’t* look like “build-%-prev” or “build-%-freebsd” > flight & blessing as appropriate > branch as specified. I think so, yes. > Isn’t the r.flight = f.flight redundant if we’re joining on flight? Indeed it is. I guess I can add a patch at theend to delete that. > Also, in spite of the paragraph attempting to explain it, I’m afraid > I don’t understand what the “AS sub WHERE TRUE” is for. The reason for the subquery is not evident in the SQL. It's because of the Perl code which generates this query. The same code is used to generate queries that start with things like SELECT * ... SELECT COUNT(*) AS count ... The perl code gets told "*" or "COUNT(*) AS count". The call sites that pass "*" expect to see fields from flights. It would be possible to change "*" to the explicit field list everywhere, but it was much easier to do it this way. (The WHERE TRUE is another one of these stubs where a condition might appear.) > But it looks like the new query should do the same thing as the old > query, assuming that the columns from the subquery are all the > columns that you need in the correct order. The subquery columns are precisely the columns currently existing in he flights table. 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 [this message] 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 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 \ --firstname.lastname@example.org \ --email@example.com \ --cc=George.Dunlap@citrix.com \ --firstname.lastname@example.org \ /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 \ email@example.com firstname.lastname@example.org 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