What is the most optimal index for this delayed_job query on postgres?

Question

delayed_job does a query like this regularly:

SELECT  "delayed_jobs".*
FROM "delayed_jobs"
WHERE ((run_at <= '2012-05-23 15:16:43.180810' AND (locked_at IS NULL OR locked_at < '2012-05-23 11:16:43.180841') OR locked_by = 'host:foo pid:1') AND failed_at IS NULL)
ORDER BY priority ASC, run_at ASC LIMIT 5

My logs on my pretty big DB machine report that it takes a quarter second to run. I could just throw some indexes on all the columns that are selected on, but I can probably get more performance out of a multi-column index.

What's the most optimal multi-column index I can make for this query? Are there any tools that can calculate this for me?

update

postgres version: 9.1.3

one existing index: priority, run_at (named "delayed_jobs_priority")

out of explain analyze:

Limit  (cost=0.00..219.65 rows=5 width=1154) (actual time=0.727..0.727 rows=0 loops=1)
   ->  Index Scan using delayed_jobs_priority on delayed_jobs  (cost=0.00..351.43 rows=8 width=1154) (actual time=0.725..0.725 rows=0 loops=1)
         Filter: ((failed_at IS NULL) AND (((run_at <= '2012-05-23 18:11:03.980113'::timestamp without time zone) AND ((locked_at IS NULL) OR (locked_at < '2012-05-23 14:11:03.98014'::timestamp without time zone))) OR ((locked_by)::text = 'host:foo pid:1'::text)))
 Total runtime: 0.754 ms
(4 rows)

Answer 1

Since you have a LIMIT clause, it's possible that you want an ordering index instead of a filtering one, on (priority, run_at).

What is the percentage of records in your table which satisfy the WHERE condition?