> On Jul 21, 2020, at 7:41 PM, Ian Jackson <[email protected]> wrote:
>
> Perf: runtime of my test case now ~11s
>
> Example query before (from the Perl DBI trace):
>
> SELECT * FROM flights JOIN steps USING (flight)
> WHERE (branch='xen-unstable')
> AND job=? and testid=? and status='pass'
> AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
> LIMIT 1
>
> After:
>
> WITH s AS
> (
> SELECT * FROM steps
> WHERE job=? and testid=? and status='pass'
> )
> SELECT * FROM flights JOIN s USING (flight)
> WHERE (branch='xen-unstable')
> AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
> LIMIT 1
>
> In both cases with bind vars:
>
> "test-amd64-i386-xl-pvshim"
> "guest-start"
>
> Diff to the query:
>
> - SELECT * FROM flights JOIN steps USING (flight)
> + WITH s AS
> + (
> + SELECT * FROM steps
> + WHERE job=? and testid=? and status='pass'
> + )
> + SELECT * FROM flights JOIN s USING (flight)
> WHERE (branch='xen-unstable')
> - AND job=? and testid=? and status='pass'
> AND ( (TRUE AND flight <= 151903) AND (blessing='real') )
> LIMIT 1
>
> CC: George Dunlap <[email protected]>
> Signed-off-by: Ian Jackson <[email protected]>
> ---
> schema/steps-job-index.sql | 2 +-
> sg-report-flight | 14 ++++++++++++--
> 2 files changed, 13 insertions(+), 3 deletions(-)
>
> diff --git a/schema/steps-job-index.sql b/schema/steps-job-index.sql
> index 07dc5a30..2c33af72 100644
> --- a/schema/steps-job-index.sql
> +++ b/schema/steps-job-index.sql
> @@ -1,4 +1,4 @@
> --- ##OSSTEST## 006 Preparatory
> +-- ##OSSTEST## 006 Needed
> --
> -- This index helps sg-report-flight find if a test ever passed.
>
> diff --git a/sg-report-flight b/sg-report-flight
> index b5398573..b8d948da 100755
> --- a/sg-report-flight
> +++ b/sg-report-flight
> @@ -849,10 +849,20 @@ sub justifyfailures ($;$) {
>
> my @failures= values %{ $fi->{Failures} };
>
> + # In psql 9.6 this WITH clause makes postgresql do the steps query
> + # first. This is good because if this test never passed we can
> + # determine that really quickly using the new index, without
> + # having to scan the flights table. (If the test passed we will
> + # probably not have to look at many flights to find one, so in
> + # that case this is not much worse.)
Seems a bit weird, but OK. The SQL looks the same, so:
Reviewed-by: George Dunlap <[email protected]>
