Skip site navigation (1) Skip section navigation (2)

Re: Split select completes, single select doesn't and

From: Ragnar <gnari(at)hive(dot)is>
To: Anthony Ransley <anthonyr(at)aurema(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Split select completes, single select doesn't and
Date: 2006-05-30 21:53:20
Message-ID: 1149026000.8566.119.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: 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




In response to

pgsql-performance by date

Next:From: Daniel J. LukeDate: 2006-05-30 22:03:11
Subject: Re: Getting even more insert performance (250m+rows/day)
Previous:From: Jonah H. HarrisDate: 2006-05-30 21:05:17
Subject: Re: INSERT OU UPDATE WITHOUT SELECT?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group