Message-ID: <4808E600.3060108@postnewspapers.com.au>
Date: Sat, 19 Apr 2008 02:18:40 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: Jesper Krogh <jesper@krogh.cc>
Cc: pgsql-performance@postgresql.org
Subject: Re: Message queue table..
References: <4808DF33.8000008@krogh.cc>
In-Reply-To: <4808DF33.8000008@krogh.cc>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

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
> ;
> 
>    QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------- 
> 
>  Limit  (cost=0.00..0.09 rows=1 width=106) (actual time=245.273..245.274 
> rows=1 loops=1)
>    ->  Index Scan using workqueue_job_funcid_priority_idx on job 
> (cost=0.00..695291.80 rows=8049405 width=106) (actual 
> time=245.268..245.268 rows=1 loops=1)
>          Index Cond: (funcid = 4)
>          Filter: ((run_after <= 1208442668) AND (grabbed_until <= 
> 1208442668) AND ("coalesce" = 'Efam'::text))
>  Total runtime: 245.330 ms
> (5 rows)

Without seeing the schema and index definitions ... maybe you'd benefit 
from a multiple column index. I'd experiment with an index on 
(funcid,priority) first.

--
Craig Ringer