Speed up dashboard queries

[clarafu]

Summary

The job_inputs query that we use to grab all the inputs for the dashboard can end up getting slow with a really large deployment where lots of users are always keeping the dashboard open. I saw that for one deployment this query made up a few of the top queries that were taking up the most total time from the db.

concourse/atc/db/job_factory.go

Lines 437 to 451 in 752fd43

	rows, err := psql.Select("j.id", "i.name", "r.name", "array_agg(jp.name ORDER BY jp.id)", "i.trigger").
	From("job_inputs i").
	Join("jobs j ON j.id = i.job_id").
	Join("pipelines p ON p.id = j.pipeline_id").
	Join("teams tm ON tm.id = p.team_id").
	Join("resources r ON r.id = i.resource_id").
	LeftJoin("jobs jp ON jp.id = i.passed_job_id").
	Where(sq.Eq{
	"j.active": true,
	}).
	Where(d.pred).
	GroupBy("i.name, j.id, r.name, i.trigger").
	OrderBy("j.id").
	RunWith(d.tx).
	Query()

Looks like a lot of the time is being used by the group by which is needed because of the array_agg. Maybe we can not do the array_agg and just combine the passed_job_ids in the atc manually for each input?

Another query was also taking up a lot of time was

concourse/atc/db/job_factory.go

Lines 311 to 338 in 752fd43

	rows, err := psql.Select(
	"j.id",
	"j.name",
	"p.id",
	"p.name",
	"p.instance_vars",
	"j.paused",
	"j.has_new_inputs",
	"j.tags",
	"tm.name",
	"l.id", "l.name", "l.status", "l.start_time", "l.end_time",
	"n.id", "n.name", "n.status", "n.start_time", "n.end_time",
	"t.id", "t.name", "t.status", "t.start_time", "t.end_time",
	"j.paused_by",
	"j.paused_at").
	From("jobs j").
	Join("pipelines p ON j.pipeline_id = p.id").
	Join("teams tm ON p.team_id = tm.id").
	LeftJoin("builds l on j.latest_completed_build_id = l.id").
	LeftJoin("builds n on j.next_build_id = n.id").
	LeftJoin("builds t on j.transition_build_id = t.id").
	Where(sq.Eq{
	"j.active": true,
	}).
	Where(d.pred).
	OrderBy("j.id ASC").
	RunWith(d.tx).
	Query()

Context

• RFC: concourse/rfcs#
• Prior discussion: #
• Depends on #
• Part of #