With the help of was able to optimize the query further
@@ -1513,23 +1513,17 @@ func (p *pipeline) getLatestModifiedTime() (time.Time, error) {
WHEN bi_max > vr_max THEN bi_max
ELSE vr_max
END
- FROM
- (
+ FROM (
SELECT
COALESCE(MAX(bo.modified_time), 'epoch') as bo_max,
- COALESCE(MAX(bi.modified_time), 'epoch') as bi_max
- FROM build_outputs bo
- LEFT OUTER JOIN build_inputs bi ON bi.versioned_resource_id = bo.versioned_resource_id
- LEFT OUTER JOIN versioned_resources v ON v.id = bo.versioned_resource_id
- LEFT OUTER JOIN resources r ON r.id = v.resource_id
- WHERE r.pipeline_id = $1
- ) bobi,
- (
- SELECT COALESCE(MAX(vr.modified_time), 'epoch') as vr_max
- FROM versioned_resources vr
- LEFT OUTER JOIN resources r ON r.id = vr.resource_id
+ COALESCE(MAX(bi.modified_time), 'epoch') as bi_max,
+ COALESCE(MAX(vr.modified_time), 'epoch') as vr_max
+ FROM resources r
+ INNER JOIN versioned_resources vr ON r.id = vr.resource_id
+ LEFT OUTER JOIN build_inputs bi ON vr.id = bi.versioned_resource_id
+ LEFT OUTER JOIN build_outputs bo ON vr.id = bo.versioned_resource_id
WHERE r.pipeline_id = $1
- ) vr
+ ) x
`, p.id).Scan(&maxModifiedTime)
return maxModifiedTime, err
