grahamc/hydra - Change YF3HTIPFEQKTVMGTU5JYB2VZDRHWFQJKRONR3EZJQBQXG3QOVUFQC

Use partial indexes

There is no point in indexing rows with common column values like "finished = 1", since those are the majority of the table. Only the exceptions ("finished = 0") are interesting. Having smaller tables should make updates/insertions faster.

Created by Eelco Dolstra on September 29, 2014

YF3HTIPFEQKTVMGTU5JYB2VZDRHWFQJKRONR3EZJQBQXG3QOVUFQC

Dependencies

In channels

upstream

main

Change contents

Replacement in src/sql/hydra.sql at line 544 [6.1]

B:BD[5.2524] → [3.371:427]

∅:D[3.427] → [7.0:96]

B:BD[5.2524] → [7.0:96]

create index IndexBuildStepsOnBusy on BuildSteps(busy);
create index IndexBuildStepsOnDrvpathTypeBusyStatus on BuildSteps(drvpath, type, busy, status);

[5.2384]

[4.20632]

create index IndexBuildStepsOnBusy on BuildSteps(busy) where busy = 1;
create index IndexBuildStepsOnDrvPath on BuildSteps(drvpath);

Replacement in src/sql/hydra.sql at line 547 [6.1]

∅:D[7.158] → [5.2524:2580]

∅:D[8.634] → [5.2524:2580]

∅:D[4.20700] → [5.2524:2580]

B:BD[5.2524] → [5.2524:2580]

B:BD[5.2580] → [3.428:494]

∅:D[3.494] → [5.2580:2638]

B:BD[5.2580] → [5.2580:2638]

B:BD[5.2638] → [9.0:81]

B:BD[9.81] → [2.0:83]

create index IndexBuildsOnFinished on Builds(finished);
create index IndexBuildsOnFinishedBusy on Builds(finished, busy);
create index IndexBuildsOnIsCurrent on Builds(isCurrent);
create index IndexBuildsOnJobsetIsCurrent on Builds(project, jobset, isCurrent);
create index IndexBuildsOnJobIsCurrent on Builds(project, jobset, job, isCurrent);

[4.20700]

[5.2867]

create index IndexBuildsOnFinished on Builds(finished) where finished = 0;
create index IndexBuildsOnFinishedBusy on Builds(finished, busy) where finished = 0;
create index IndexBuildsOnIsCurrent on Builds(isCurrent) where isCurrent = 1;
create index IndexBuildsOnJobsetIsCurrent on Builds(project, jobset, isCurrent) where isCurrent = 1;
create index IndexBuildsOnJobIsCurrent on Builds(project, jobset, job, isCurrent) where isCurrent = 1;

Replacement in src/sql/hydra.sql at line 570 [6.1]

B:BD[10.3171] → [10.3171:3219]

create index IndexBuildsOnKeep on Builds(keep);

[10.3171]

[10.3337]

create index IndexBuildsOnKeep on Builds(keep) where keep = 1;

File addition: upgrade-30.sql (----------)

[11.3004]

drop index IndexBuildStepsOnBusy;
drop index IndexBuildStepsOnDrvpathTypeBusyStatus;
drop index IndexBuildsOnFinished;
drop index IndexBuildsOnFinishedBusy;
drop index IndexBuildsOnIsCurrent;
drop index IndexBuildsOnJobsetIsCurrent;
drop index IndexBuildsOnJobIsCurrent;
drop index IndexBuildsOnKeep;
create index IndexBuildStepsOnBusy on BuildSteps(busy) where busy = 1;
create index IndexBuildStepsOnDrvPath on BuildSteps(drvpath);
create index IndexBuildsOnFinished on Builds(finished) where finished = 0;
create index IndexBuildsOnFinishedBusy on Builds(finished, busy) where finished = 0;
create index IndexBuildsOnIsCurrent on Builds(isCurrent) where isCurrent = 1;
create index IndexBuildsOnJobsetIsCurrent on Builds(project, jobset, isCurrent) where isCurrent = 1;
create index IndexBuildsOnJobIsCurrent on Builds(project, jobset, job, isCurrent) where isCurrent = 1;
create index IndexBuildsOnKeep on Builds(keep) where keep = 1;