grahamc/hydra2 - Change 762ZKDT3MIPKV547IMOZHL75XDUNCVGSYSSHRXDLL4DWQCP4GKBAC

Fix some broken indices

These indices basically did nothing since they put "id" first.

In particular this makes /job/…/all much faster.

Created by Eelco Dolstra on October 28, 2020

762ZKDT3MIPKV547IMOZHL75XDUNCVGSYSSHRXDLL4DWQCP4GKBAC

Dependencies

In channels

main

Change contents

Replacement in src/sql/hydra.sql at line 604 [4.1]

B:BD[3.100] → [2.0:91]

∅:D[2.91] → [5.0:141]

B:BD[3.100] → [5.0:141]

create index IndexBuildsOnJobsetIdFinishedId on Builds(id DESC, finished, job, jobset_id);
create index IndexFinishedSuccessfulBuilds on Builds(id DESC, buildstatus, finished, job, jobset_id) where buildstatus = 0 and finished = 1;

[3.100]

[6.0]

create index IndexBuildsOnJobsetIdFinishedId on Builds(jobset_id, job, finished, id DESC);
create index IndexFinishedSuccessfulBuilds on Builds(jobset_id, job, finished, buildstatus, id DESC) where buildstatus = 0 and finished = 1;

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

[7.3004]

drop index IndexBuildsOnJobsetIdFinishedId;
create index IndexBuildsOnJobsetIdFinishedId on Builds(jobset_id, job, finished, id DESC);
drop index IndexFinishedSuccessfulBuilds;
create index IndexFinishedSuccessfulBuilds on Builds(jobset_id, job, finished, buildstatus, id DESC) where buildstatus = 0 and finished = 1;