Craig Ringer wrote:
Jesper Krogh wrote:
Hi.
I have this "message queue" table.. currently with 8m+ records.
Picking the top priority messages seem to take quite long.. it is just
a matter of searching the index.. (just as explain analyze tells me it
does).
Can anyone digest further optimizations out of this output? (All
records have funcid=4)
You mean all records of interest, right, not all records in the table?
Actually all the records.. since all the other virtual queues currently
are empty.
What indexes do you have in place? What's the schema? Can you post a "\d
tablename" from psql?
# 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 = 4) AND
(job.run_after <= 1208442668) AND (job.grabbed_until <= 1208442668)
AND (job.coalesce = 'Efam') ORDER BY funcid, priority ASC LIMIT 1
I found that removing the funcid from the order by made it use a better
index. (priority, run_after, grabbed_until) that probably makes sense
since the funcid doesnt give any value in the index at all.
thanks for leading me back on track.
Jesper
--
Jesper
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
