These indexes allow very fast lookup of "relevant" flights eg when
trying to justify failures.
In my ad-hoc test case, these indices (along with the subsequent
changes to sg-report-flight and Executive.pm, reduce the runtime of
sg-report-flight from 2-3ks (unacceptably long!) to as little as
5-7s seconds - a speedup of about 500x.
(Getting the database snapshot may take a while first, but deploying
this code should help with that too by reducing long-running
transactions. Quoted perf timings are from snapshot acquisition.)
Without these new indexes there may be a performance change from the
query changes. I haven't benchmarked this so I am setting the schema
updates to be Preparatory/Needed (ie, "Schema first" as
schema/README.updates has it), to say that the index should be created
before the new code is deployed.
Testing: I have tested this series by creating experimental indices
"trial_..." in the actual production instance. (Transactional DDL was
very helpful with this.) I have verified with \d that schema update
instructions in this commit generate indexes which are equivalent to
the trial indices.
Deployment: AFter these schema updates are applied, the trial indices
are redundant duplicates and should be deleted.
CC: George Dunlap <George.Dunlap@citrix.com>
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
---
v2: Use proper \ escaping for underscores in LIKE
---
schema/runvars-built-index.sql | 7 +++++++
schema/runvars-revision-index.sql | 7 +++++++
schema/steps-job-index.sql | 7 +++++++
3 files changed, 21 insertions(+)
create mode 100644 schema/runvars-built-index.sql
create mode 100644 schema/runvars-revision-index.sql
create mode 100644 schema/steps-job-index.sql
diff --git a/schema/runvars-built-index.sql b/schema/runvars-built-index.sql
new file mode 100644
index 00000000..7108e0af
--- /dev/null
+++ b/schema/runvars-built-index.sql
@@ -0,0 +1,7 @@
+-- ##OSSTEST## 007 Preparatory
+--
+-- This index helps sg-report-flight find relevant flights.
+
+CREATE INDEX runvars_built_revision_idx
+ ON runvars (val)
+ WHERE name LIKE 'built\_revision\_%';
diff --git a/schema/runvars-revision-index.sql b/schema/runvars-revision-index.sql
new file mode 100644
index 00000000..8871b528
--- /dev/null
+++ b/schema/runvars-revision-index.sql
@@ -0,0 +1,7 @@
+-- ##OSSTEST## 008 Preparatory
+--
+-- This index helps Executive::report__find_test find relevant flights.
+
+CREATE INDEX runvars_revision_idx
+ ON runvars (val)
+ WHERE name LIKE 'revision\_%';
diff --git a/schema/steps-job-index.sql b/schema/steps-job-index.sql
new file mode 100644
index 00000000..07dc5a30
--- /dev/null
+++ b/schema/steps-job-index.sql
@@ -0,0 +1,7 @@
+-- ##OSSTEST## 006 Preparatory
+--
+-- This index helps sg-report-flight find if a test ever passed.
+
+CREATE INDEX steps_job_testid_status_idx
+ ON steps (job, testid, status);
+
--
2.20.1
trying to justify failures.
In my ad-hoc test case, these indices (along with the subsequent
changes to sg-report-flight and Executive.pm, reduce the runtime of
sg-report-flight from 2-3ks (unacceptably long!) to as little as
5-7s seconds - a speedup of about 500x.
(Getting the database snapshot may take a while first, but deploying
this code should help with that too by reducing long-running
transactions. Quoted perf timings are from snapshot acquisition.)
Without these new indexes there may be a performance change from the
query changes. I haven't benchmarked this so I am setting the schema
updates to be Preparatory/Needed (ie, "Schema first" as
schema/README.updates has it), to say that the index should be created
before the new code is deployed.
Testing: I have tested this series by creating experimental indices
"trial_..." in the actual production instance. (Transactional DDL was
very helpful with this.) I have verified with \d that schema update
instructions in this commit generate indexes which are equivalent to
the trial indices.
Deployment: AFter these schema updates are applied, the trial indices
are redundant duplicates and should be deleted.
CC: George Dunlap <George.Dunlap@citrix.com>
Signed-off-by: Ian Jackson <ian.jackson@eu.citrix.com>
---
v2: Use proper \ escaping for underscores in LIKE
---
schema/runvars-built-index.sql | 7 +++++++
schema/runvars-revision-index.sql | 7 +++++++
schema/steps-job-index.sql | 7 +++++++
3 files changed, 21 insertions(+)
create mode 100644 schema/runvars-built-index.sql
create mode 100644 schema/runvars-revision-index.sql
create mode 100644 schema/steps-job-index.sql
diff --git a/schema/runvars-built-index.sql b/schema/runvars-built-index.sql
new file mode 100644
index 00000000..7108e0af
--- /dev/null
+++ b/schema/runvars-built-index.sql
@@ -0,0 +1,7 @@
+-- ##OSSTEST## 007 Preparatory
+--
+-- This index helps sg-report-flight find relevant flights.
+
+CREATE INDEX runvars_built_revision_idx
+ ON runvars (val)
+ WHERE name LIKE 'built\_revision\_%';
diff --git a/schema/runvars-revision-index.sql b/schema/runvars-revision-index.sql
new file mode 100644
index 00000000..8871b528
--- /dev/null
+++ b/schema/runvars-revision-index.sql
@@ -0,0 +1,7 @@
+-- ##OSSTEST## 008 Preparatory
+--
+-- This index helps Executive::report__find_test find relevant flights.
+
+CREATE INDEX runvars_revision_idx
+ ON runvars (val)
+ WHERE name LIKE 'revision\_%';
diff --git a/schema/steps-job-index.sql b/schema/steps-job-index.sql
new file mode 100644
index 00000000..07dc5a30
--- /dev/null
+++ b/schema/steps-job-index.sql
@@ -0,0 +1,7 @@
+-- ##OSSTEST## 006 Preparatory
+--
+-- This index helps sg-report-flight find if a test ever passed.
+
+CREATE INDEX steps_job_testid_status_idx
+ ON steps (job, testid, status);
+
--
2.20.1