pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Split select completes, single select doesn't and becomes IO bound!
2+ messages / 2 participants
[nested] [flat]

* Split select completes, single select doesn't and becomes IO bound!
@ 2006-05-30 00:26 Anthony Ransley <anthonyr@aurema.com>
  2006-05-30 21:53 ` Re: Split select completes, single select doesn't and Ragnar <gnari@hive.is>
  0 siblings, 1 reply; 2+ messages in thread

From: Anthony Ransley @ 2006-05-30 00:26 UTC (permalink / raw)
  To: pgsql-performance

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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Split select completes, single select doesn't and
  2006-05-30 00:26 Split select completes, single select doesn't and becomes IO bound! Anthony Ransley <anthonyr@aurema.com>
@ 2006-05-30 21:53 ` Ragnar <gnari@hive.is>
  0 siblings, 0 replies; 2+ messages in thread

From: Ragnar @ 2006-05-30 21:53 UTC (permalink / raw)
  To: Anthony Ransley <anthonyr@aurema.com>; +Cc: pgsql-performance

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-05-30 21:53 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-30 00:26 Split select completes, single select doesn't and becomes IO bound! Anthony Ransley <anthonyr@aurema.com>
2006-05-30 21:53 ` Re: Split select completes, single select doesn't and Ragnar <gnari@hive.is>

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