Message-ID: <447B9143.6010407@aurema.com>
Date: Tue, 30 May 2006 10:26:43 +1000
From: Anthony Ransley <anthonyr@aurema.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Split select completes, single select doesn't and becomes IO bound!
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Can any one explain why the following query

select f(q) from
(
    select * from times
    where '2006-03-01 00:00:00'<=q and q<'2006-03-08 00:00:00'
    order by q
) v;

never completes, but splitting up the time span into single days does work.

select f(q) from
(
    select * from times
    where '2006-03-01 00:00:00'<=q and q<'2006-03-02 00:00:00'
    order by q
) v;
select f(q) from
(
    select * from times
    where '2006-03-02 00:00:00'<=q and q<'2006-03-03 00:00:00'
    order by q
) v;
...
select f(q) from
(
    select * from times
    where '2006-03-07 00:00:00'<=q and q<'2006-03-08 00:00:00'
    order by q
) v;

The stored procedure f(q) take a timestamp and does a select and a 
calculation and then an update of a results table. The times table 
containes only a 100 rows per day. It is also observed that the cpu 
starts the query with 100% usage and then the slowly swings up and down 
from 100% to 20% over the first half hour, and then by the following 
morning the query is still running and the cpu usage is 3-5%. IO bound 
i'm guessing as the hdd is in constant use at 5 to 15 MB per second usage.
In contrast the query that is split up into days has a 100% cpu usage 
all the way through to its completion, which only takes twenty minutes 
each. The computer is not being used for anything else, and is a dual 
core Athlon 4400+ with 4GB of ram.

Thanks for any information you can give on this.