Code review comment for lp:~gmb/launchpad/bugjob-indices-bug-539382

I can't recall why I let this table get created without indexes. Possibly because I didn't think it should get as big as it has (2.5 million jobs, with nearly 1 million of them completed over a month ago that nobody has bothered to garbage collect).

Should BugJob.job be UNIQUE?

Should BugJob.bug be UNIQUE?

Should (BugJob.bug, BugJob.job) be UNIQUE?

To know if the indexes are going to be used, I'll need some more information about the queries being made. In particular, if we search for 'jobs for bug NNN with type ZZZ' we should create an index on (bug, job_type) rather than separate indexes on bug and job_type. If we are querying for 'All bug jobs of type ZZZ' then we should separate the indexes.

Allocated patch number is patch-2207-53-0.sql

Indexes should be named with a __idx suffix, as per the following. If we are creating UNIQUE constraints instead, we will create them with a __key suffix.

SET client_min_messages=ERROR;

-- Indices for BugJob
CREATE INDEX bugjob__job__idx ON BugJob(job);
CREATE INDEX bugjob__bug__idx ON BugJob(bug);
CREATE INDEX bugjob__job_type__idx ON BugJob(job_type);

-- Indices for Job
CREATE INDEX job__scheduled_start__idx ON Job(scheduled_start);
CREATE INDEX job__lease_expires__idx ON Job(lease_expires);

INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 53, 0);