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

The following version is approved. Although there is no real functional difference, a little more metadata ends up stored in the database using the ALTER TABLE syntax for defining uniqueness.

SET client_min_messages=ERROR;

-- Indices for BugJob
ALTER TABLE BugJob ADD CONSTRAINT bugjob__job__key UNIQUE (job);
CREATE INDEX bugjob__bug__job_type__idx ON BugJob(bug, 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);

Your example query is better written as following BTW:

SELECT BugJob.*
FROM BugJob, Job
WHERE
    BugJob.bug = 1
    AND BugJob.job_type = 1
    AND BugJob.job = Job.id
    AND Job.status = 1
    AND (
        Job.lease_expires IS NULL
        OR Job.lease_expires < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'))
    AND (
        Job.scheduled_start IS NULL
        OR Job.scheduled_start <= (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'))
LIMIT 1;