Re: maintaining a reference to a fetched row

On Nov 4, 2009, at 8:47 AM, Jeff Janes wrote:

>> Worker daemons do a bounded, ordered, limited SELECT to grab a row,
>> which
>> they lock by setting a value in the queue.status column.
>
> So you do a select, and then an update?

I do a select for update in a stored proc:

FOR queue_item IN
SELECT * FROM queue
WHERE status IS NULL AND id >= low_bound_id
ORDER BY id LIMIT batch_size
FOR UPDATE
LOOP
UPDATE queue_proc set status = 'proc' where id = queue_item.id ;

The daemons keep track of their last position in the queue with
low_bound_id. Also, as you probably notice, I also fetch a batch of
(100) items at a time. In practice, it's pretty fast. The job I'm
running now is showing an average fetch time of 30ms per 100 actions,
which ain't bad.

>> However, the writing of results back to the row takes ~5ms, which
>> is slower
>> than I'd like.
>
> It seems you have an select, and update, and another update. Where in
> this process do you commit? Are you using fsync=off or
> synchronous_commit=off?

First commit occurs after the stored proc to select/update a batch of
items is complete. Second commit occurs on the writing of results
back for each particular action. Two commits are required because the
time it takes to complete the intervening action can vary wildly:
anywhere between 20ms and 45min.

>> It seems that this is because I need to to do an index scan
>> on the queue table to find the row I just fetched.
>
> Why would the index scan take 1 ms two of the times it is done but 5ms
> the third time? Isn't it the same index scan each time? Or does the
> change in queue.status change the plan?

The final update is a different query -- just a plain old update by ID:

UPDATE queue_proc set status = 'proc' where id = %s ;

This update by ID takes ~2.5ms, which means it's where the framework
is spending most of its overhead.

Brian