grahamc/hydra2 - Change 2I2ZX6JBPIM5D57G52MGHKUD66CJMOYAYNYN6CSHNUHAFA46A5YQC

* Make the "latest succeeded" query (used by the "latest" channel)

faster, from about 4.5s to 1.0s for the global "latest" channel. Note that the query is only fast if the "IndexBuildsOnJob" and "IndexBuildsOnJobAndIsCurrent" indices are dropped - if they exist, PostgreSQL will use those instead of the more efficient "IndexBuildsOnJobFinishedId" index. Looks like a bug in the planner to me…

Created by Eelco Dolstra on February 12, 2010

2I2ZX6JBPIM5D57G52MGHKUD66CJMOYAYNYN6CSHNUHAFA46A5YQC

Dependencies

In channels

main

Change contents

Replacement in src/lib/Hydra/Helper/CatalystUtils.pm at line 68 [4.3469]

B:BD[3.2049] → [3.2049:2158]

        ->search_literal("exists (select 1 from buildproducts where build = me.id and type = 'nix-build')");

[3.2049]

[5.673]

        ->search_literal("exists (select 1 from buildproducts where build = resultInfo.id and type = 'nix-build')");

Replacement in src/lib/Hydra/Schema/Builds.pm at line 292 [7.5202]

B:BD[6.1238] → [6.1238:1302]

            (select project, jobset, job, system, max(id) as id

[6.1238]

[8.895]

            (select  
               (select max(id) from builds b
                where 
                  project = activeJobs.project and jobset = activeJobs.jobset 
                  and job = activeJobs.job and system = activeJobs.system 
                  and finished = 1
                  and exists (select 1 from buildresultinfo where id = b.id and buildstatus = 0)
               ) as id

Deletion in src/lib/Hydra/Schema/Builds.pm at line 301 [7.5202]

B:BD[8.939] → [8.939:1066]

∅:D[8.1066] → [6.1420:1471]

B:BD[6.1420] → [6.1420:1471]

             natural join Builds
             natural join BuildResultInfo
             where finished = 1 and buildStatus = 0
             group by project, jobset, job, system

Replacement in src/lib/Hydra/Schema/Builds.pm at line 302 [7.5202]
B:BD[6.1495] → [6.1495:1525]
```
          natural join Builds
```
[6.1495]
[6.1525]
```
          join Builds using (id)
```

Replacement in src/sql/hydra.sql at line 437 [10.1]

B:BD[9.2638] → [9.2638:2787]

create index IndexBuildsOnJob on Builds(project, jobset, job);
create index IndexBuildsOnJobAndIsCurrent on Builds(project, jobset, job, isCurrent);

[9.2638]

[9.2787]

#create index IndexBuildsOnJob on Builds(project, jobset, job);
#create index IndexBuildsOnJobAndIsCurrent on Builds(project, jobset, job, isCurrent);

Insertion in src/sql/hydra.sql at line 443 [10.1]

[9.3040]

[2.159]

create index IndexBuildsOnJobFinishedId on builds(project, jobset, job, system, finished, id DESC);