| Subject: | Using an ordering on the select. |
If possible, please use an ordering when selecting from the database. In
PostgreSQL this makes queries on large set enormously faster:
# explain analyze SELECT job.jobid, job.funcid, job.arg, job.uniqkey,
job.insert_time, job.run_after, job.grabbed_until, job.priority,
job.coalesce from workqueue.job where (job.funcid in (3)) and
(job.run_after <= 1207833398 ) and (job.grabbed_until <= 1207833398)
limit 50;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2.16 rows=50 width=88) (actual
time=1513.607..1513.745 rows=50 loops=1)
-> Seq Scan on job (cost=0.00..303726.95 rows=7035367 width=88)
(actual time=1513.603..1513.663 rows=50 loops=1)
Filter: ((funcid = 3) AND (run_after <= 1207833398) AND
(grabbed_until <= 1207833398))
Total runtime: 1513.863 ms
(4 rows)
# explain analyze SELECT job.jobid, job.funcid, job.arg, job.uniqkey,
job.insert_time, job.run_after, job.grabbed_until, job.priority,
job.coalesce from workqueue.job where (job.funcid in (3)) and
(job.run_after <= 1207833398 ) and (job.grabbed_until <= 1207833398)
order by run_after limit 50;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3.42 rows=50 width=88) (actual time=43.736..44.059
rows=50 loops=1)
-> Index Scan using func_id_run_after_grabbed_until on job
(cost=0.00..480655.21 rows=7035367 width=88) (actual time=43.732..43.982
rows=50 loops=1)
Index Cond: ((funcid = 3) AND (run_after <= 1207833398) AND
(grabbed_until <= 1207833398))
Total runtime: 44.173 ms
(4 rows)
The queue is currently around 7m.. I just have trouble tweaking
Data::ObjectDriver to accept ordering on the data.