grahamc/hydra2 - Change XKJOP4WX7J2UUXLZ2EEGRCUX6U5WD6YQBAMFIE266JBVQYC3VEIAC

SQL: create better indexes for builds based on the jobset id

These are primarily used by the jobsetOverview renders.

Created by Graham Christensen on June 1, 2021

XKJOP4WX7J2UUXLZ2EEGRCUX6U5WD6YQBAMFIE266JBVQYC3VEIAC

Dependencies

In channels

main

Change contents

Insertion in src/sql/hydra.sql at line 615 [4.1]

[2.576]

[3.2867]

create index IndexBuildsJobsetIdCurrentUnfinished on Builds(jobset_id) where isCurrent = 1 and finished = 0;
create index IndexBuildsJobsetIdCurrentFinishedStatus on Builds(jobset_id, buildstatus) where isCurrent = 1 and finished = 1;
create index IndexBuildsJobsetIdCurrent on Builds(jobset_id) where isCurrent = 1;

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

[5.3004]

-- These take about 9 minutes in total on a replica of hydra.nixos.org
create index IndexBuildsJobsetIdCurrentUnfinished on Builds(jobset_id) where isCurrent = 1 and finished = 0;
create index IndexBuildsJobsetIdCurrentFinishedStatus on Builds(jobset_id, buildstatus) where isCurrent = 1 and finished = 1;
create index IndexBuildsJobsetIdCurrent on Builds(jobset_id) where isCurrent = 1;