[Xen-devel] [OSSTEST PATCH 2/6] sg-report-job-history: Avoid full runvars table scan (!)

[Ian Jackson <ian.jackson@xxxxxxxxxxxxx>]

sg-report-job-history wnats to know the potential names of runvars
relating to hosts.  To do this it tries to find a list of distinct
runvar names which exist in the flights it's processing.

However, it fails to limit the runvar query appropriately, and as a
result postgresql must scan almost the complete runvars table to
produce an answer.  This is very slow if the table is bigger than the
database server's RAM.

Fix this by limiting the runvars table query to relevant flights.

Specifically:

 * Break the `100' from the LIMIT clause on the flights search
   into a local variable $limit.
 * Break the bulk of the flights search sql statement text into
   a local variable $fromstuff.
 * In the runvars statement, add a condition on flights which uses
   LIMIT and OFFSET, based on results of the the flights query.

Signed-off-by: Ian Jackson <Ian.Jackson@xxxxxxxxxxxxx>
---
 sg-report-job-history |   17 +++++++++++++----
 1 file changed, 13 insertions(+), 4 deletions(-)

diff --git a/sg-report-job-history b/sg-report-job-history
index e7052a3..d59717f 100755
--- a/sg-report-job-history
+++ b/sg-report-job-history
@@ -163,12 +163,17 @@ sub processjobbranch ($$) {
 END
         push @params, $bra;
     }
-    my $flightsq= $dbh_tests->prepare(<<END);
-        SELECT *
+    my $limit= 100;
+    my $offset= $limit-1;
+
+    my $fromstuff= <<END;
           FROM jobs JOIN flights USING (flight)
          WHERE ($cond)
       ORDER BY flight DESC
-         LIMIT 100
+END
+    my $flightsq= $dbh_tests->prepare(<<END);
+        SELECT * $fromstuff
+         LIMIT $limit
 END
     $flightsq->execute(@params);
 
@@ -177,9 +182,13 @@ END
         FROM runvars
         JOIN flights USING (flight)
        WHERE ($cond)
+         AND flight >= (
+             SELECT flight $fromstuff
+             LIMIT 1 OFFSET $offset
+          )
      ORDER BY name;
 END
-    $hostsq->execute(@params);
+    $hostsq->execute(@params, @params);
     my @hostvarcols;
     while (my ($hostvar) = $hostsq->fetchrow_array()) {
        next unless $hostvar =~ m/(^|_)host$/;
-- 
1.7.10.4


_______________________________________________
Xen-devel mailing list
Xen-devel@xxxxxxxxxxxxx
http://lists.xen.org/xen-devel