From: Anthony Ransley <anthonyr@aurema.com>
To: pgsql-performance@postgresql.org
Subject: Split select completes, single select doesn't and becomes IO bound!
Date: Tue, 30 May 2006 10:26:43 +1000
Message-ID: <447B9143.6010407@aurema.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: anthonyr@aurema.com
Subject: Re: Split select completes, single select doesn't and becomes IO bound!
In-Reply-To: <447B9143.6010407@aurema.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox