From: Jesper Krogh <jesper@krogh.cc>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Message queue table..
Date: Fri, 18 Apr 2008 21:23:09 +0200
Message-ID: <4808F51D.40102@krogh.cc> (raw)
In-Reply-To: <4808E600.3060108@postnewspapers.com.au>
References: <4808DF33.8000008@krogh.cc>
<4808E600.3060108@postnewspapers.com.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jesper@krogh.cc, craig@postnewspapers.com.au
Subject: Re: Message queue table..
In-Reply-To: <4808F51D.40102@krogh.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox