Use partial indexes

[?]

Sep 29, 2014, 9:03 PM

YF3HTIPFEQKTVMGTU5JYB2VZDRHWFQJKRONR3EZJQBQXG3QOVUFQC

Dependencies

[2] 5L272QSC Speed up the /jobset overview pages a lot
[3] LGNML7VJ Don't use a prepared statement for the active build steps query
[4] PMNWRTGJ Add multiple output support
[5] P5VJCT6U fix wrong comment
[6] LTQMSMYO Get rid of some obsolete indexes
[7] 34DPX2OR * Create some more indices on tables that have a foreign key reference
[8] TFVGX4JS Add an index to speed up the /project/<name> pages
[9] 2I2ZX6JB * Make the "latest succeeded" query (used by the "latest" channel)
[10] 6QRHXIM3 * Speed up the jobset index page. Especially the query to get the
[11] RXVJFQ5A Evaluator cleanups
[12] E2TOU3L6 * More indices.
[13] 2QA4Y4G3 * hydra: added index, actual build time (buildstep with same outpath, so without deps) of the build
[*] N22GPKYT * Put info about logs / build products in the DB.
[*] D5QIOJGP * Move everything up one directory.

Change contents

replacement in src/sql/hydra.sql at line 544

[5.2524]→[3.371:427](∅→∅),[3.427]→[5.0:96](∅→∅),[5.2524]→[5.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

[5.158]→[5.2524:2580](∅→∅),[5.634]→[5.2524:2580](∅→∅),[4.20700]→[5.2524:2580](∅→∅),[5.2524]→[5.2524:2580](∅→∅),[5.2580]→[3.428:494](∅→∅),[3.494]→[5.2580:2638](∅→∅),[5.2580]→[5.2580:2638](∅→∅),[5.2638]→[5.0:81](∅→∅),[5.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

[5.3171]→[5.3171:3219](∅→∅)

create index IndexBuildsOnKeep on Builds(keep);

[5.3171]

[5.3337]

create index IndexBuildsOnKeep on Builds(keep) where keep = 1;
file addition: upgrade-30.sql (----------)

[16.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;