Subject: Re: Split select completes, single select doesn't and
From: Ragnar <gnari@hive.is>
To: Anthony Ransley <anthonyr@aurema.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <447B9143.6010407@aurema.com>
References: <447B9143.6010407@aurema.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Tue, 30 May 2006 21:53:20 +0000
Message-Id: <1149026000.8566.119.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

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