delayed_job regularly executes this request:
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 rather large database computer report that it takes a quarter of a second to start. I could just add some indexes to all the columns that were selected, but I probably get more performance from an index with multiple columns.
What is the best index for multiple columns I can do 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")
from 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)
source share