To: Jesper Krogh <jesper@krogh.cc>
cc: pgsql-performance@postgresql.org
Subject: Re: Message queue table.. 
In-reply-to: <4808DF33.8000008@krogh.cc> 
References: <4808DF33.8000008@krogh.cc>
Comments: In-reply-to Jesper Krogh <jesper@krogh.cc>
	message dated "Fri, 18 Apr 2008 19:49:39 +0200"
Date: Fri, 18 Apr 2008 15:27:02 -0400
Message-ID: <7590.1208546822@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jesper Krogh <jesper@krogh.cc> writes:
> 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).

>   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

Well, what that's doing in English is: scan all the rows with funcid =
4, in priority order, until we hit the first one satisfying the filter
conditions.  Apparently there are a lot of low-priority rows that have
funcid = 4 but not the other conditions.

If it's the "coalesce" condition that's the problem, an index on
(funcid, coalesce, priority) --- or (coalesce, funcid, priority) ---
would probably help.  I'm not sure there's a simple fix if it's
the other conditions that are really selective.

			regards, tom lane