Skip site navigation (1) Skip section navigation (2)

Re: Message queue table..

From: Jesper Krogh <jesper(at)krogh(dot)cc>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Message queue table..
Date: 2008-04-18 19:23:09
Message-ID: 4808F51D.40102@krogh.cc (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-04-18 19:27:02
Subject: Re: Message queue table..
Previous:From: Tom LaneDate: 2008-04-18 19:09:08
Subject: Re: 3-days-long vacuum of 20GB table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group