Обсуждение: Split select completes, single select doesn't and becomes IO bound!

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.

On þri, 2006-05-30 at 10:26 +1000, Anthony Ransley wrote:
> 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;

first question: is f() relevant to your problem?

I mean do you see the same effect with:
  select 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;

or even:
  select q from times
     where '2006-03-01 00:00:00'<=q and q<'2006-03-08 00:00:00'
     order by q


if f() is needed to make this happen show us f()

if f() is not relevant, show us the simplest cases where
you see this. show us EXPLAIN on the query that does not
finish, show us EXPLAIN ANALYZE on the queries that do.

second question: what indexes exist on the table "times" ?

another question: how many rows in the table ?

next question: is the table newly ANALYZED?

finally: what version of postgresql are you using?


whithout more info , it is difficult to guess what
your problem is, but possibly you need to increase
the statistics target of column "q"

gnari