Changeset 56789 in vbox for trunk/src/VBox/ValidationKit

Timestamp:

Jul 3, 2015 3:46:07 PM (10 years ago)

Author:

vboxsync

Message:

testmanager: index tuning.

Location:

trunk/src/VBox/ValidationKit/testmanager/db

Files:

• TestManagerDatabaseInit.pgsql (modified) (8 diffs)
• tmdb-r15-index-sorting.pgsql (added)

trunk/src/VBox/ValidationKit/testmanager/db/TestManagerDatabaseInit.pgsql

@@ -128 +128 @@
     PRIMARY KEY (uid, tsExpire)
 );
-CREATE INDEX UsersLoginNameIdx ON Users (tsExpire, sLoginName);
+CREATE INDEX UsersLoginNameIdx ON Users (sLoginName, tsExpire DESC);
 
 
@@ -410 +410 @@
     PRIMARY KEY (idTestCase, tsExpire, sArgs)
 );
-CREATE INDEX TestCaseArgsLookupIdx ON TestCaseArgs (idTestCase, tsExpire, tsEffective);
+CREATE INDEX TestCaseArgsLookupIdx ON TestCaseArgs (idTestCase, tsExpire DESC, tsEffective ASC);
 
 
@@ -515 +515 @@
     PRIMARY KEY (idTestGroup, tsExpire)
 );
-CREATE INDEX TestGroups_id_index ON TestGroups (idTestGroup, tsExpire, tsEffective);
+CREATE INDEX TestGroups_id_index ON TestGroups (idTestGroup, tsExpire DESC, tsEffective ASC);
 
 
@@ -834 +834 @@
     CHECK (fCpuNestedPaging IS NULL OR (fCpuNestedPaging <> TRUE OR fCpuHwVirt = TRUE))
 );
-CREATE UNIQUE INDEX TestBoxesUuidIdx ON TestBoxes (uuidSystem, tsExpire);
+CREATE UNIQUE INDEX TestBoxesUuidIdx ON TestBoxes (uuidSystem, tsExpire DESC);
+CREATE INDEX TestBoxesExpireEffectiveIdx ON TestBoxes (tsExpire DESC, tsEffective ASC);
 
 
@@ -1046 +1047 @@
     PRIMARY KEY (idBlacklisting, tsExpire)
 );
+CREATE INDEX BuildBlacklistIdx ON BuildBlacklist (iLastRevision DESC, iFirstRevision ASC, sProduct, sBranch, 
+                                                  tsExpire DESC, tsEffective ASC);
 
 --- @table BuildCategories
@@ -1311 +1314 @@
 CREATE INDEX TestResultsSetIdx ON TestResults (idTestSet, idStrName, idTestResult);
 CREATE INDEX TestResultsParentIdx ON TestResults (idTestResultParent);
--- The TestResultsNameIdx is for speeding up the result graph & reporting code.
-CREATE INDEX TestResultsNameIdx ON TestResults (idStrName, idTestResult, tsCreated);
+-- The TestResultsNameIdx and TestResultsNameIdx2 are for speeding up the result graph & reporting code.
+CREATE INDEX TestResultsNameIdx ON TestResults (idStrName, tsCreated DESC);
+CREATE INDEX TestResultsNameIdx2 ON TestResults (idTestResult, idStrName);
 
 ALTER TABLE TestResultFailures
@@ -1546 +1550 @@
 CREATE INDEX TestSetsTestCaseIdx    ON TestSets (idTestCase, idTestResult);
 CREATE INDEX TestSetsTestVarIdx     ON TestSets (idTestCaseArgs, idTestResult);
---- The TestSetCreatedDone is for testbox results, graph options and such.
-CREATE INDEX TestSetsCreatedDoneIdx ON TestSets (tsCreated, tsDone);
+--- The TestSetsDoneCreatedBuildCatIdx is for testbox results, graph options and such.
+CREATE INDEX TestSetsDoneCreatedBuildCatIdx ON TestSets (tsDone DESC NULLS FIRST, tsCreated ASC, idBuildCategory);
 --- For graphs.
-CREATE INDEX TestSetsGraphBoxIdx    ON TestSets (idTestBox, tsCreated, tsDone, idBuildCategory, idTestCase);
+CREATE INDEX TestSetsGraphBoxIdx    ON TestSets (idTestBox, tsCreated DESC, tsDone ASC NULLS LAST, idBuildCategory, idTestCase);
 
 ALTER TABLE TestResults      ADD FOREIGN KEY (idTestSet) REFERENCES TestSets(idTestSet) MATCH FULL;
@@ -1756 +1760 @@
     PRIMARY KEY (idSchedGroup, idItem)
 );
-
+CREATE INDEX SchedQueuesItemIdx          ON SchedQueues(idItem);
+CREATE INDEX SchedQueuesSchedGroupIdx    ON SchedQueues(idSchedGroup);
+