> On Jul 31, 2020, at 12:37 PM, Ian Jackson <ian.jack...@eu.citrix.com> wrote: > > Specifically, we narrow the initial query to flights which have at > least some job with the built_revision_foo we are looking for. > > This condition is strictly broader than that implemented inside the > flight search loop, so there is no functional change.
Assuming this is true, that job / runvar is filtered after extracting this information, then... > > Perf: runtime of my test case now ~300s-500s. > > Example query before (from the Perl DBI trace): > > SELECT * FROM ( > SELECT flight, blessing FROM flights > WHERE (branch='xen-unstable') > AND EXISTS (SELECT 1 > FROM jobs > WHERE jobs.flight = flights.flight > AND jobs.job = ?) > > AND ( (TRUE AND flight <= 151903) AND (blessing='real') ) > ORDER BY flight DESC > LIMIT 1000 > ) AS sub > ORDER BY blessing ASC, flight DESC > > With these bind variables: > > "test-armhf-armhf-libvirt" > > After: > > 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 …I agree that this shoud introduce no other changes. Reviewed-by: George Dunlap <george.dun...@citrix.com>
