Re: 57 minute SELECT

Thanks, Claudio.

I'll have a look at the clustering.

We have also noticed that the same query with a datetime range of 3 hours (rather than 4 months) runs in just 30 seconds:

AND datetime <= '2013-10-03 10:03:49'
AND datetime >= '2013-10-03 07:03:49'

-----Original Message-----
From: Claudio Freire [mailto:klaussfreire(at)gmail(dot)com]
Sent: Thursday, 3 October 2013 1:44 PM
To: Samuel Stearns
Cc: David Johnston; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] 57 minute SELECT

On Wed, Oct 2, 2013 at 10:47 PM, Samuel Stearns <sstearns(at)staff(dot)iinet(dot)net(dot)au> wrote:
> Thanks, Claudio:
>
> http://explain.depesz.com/s/WJQx

If you have a test database, and if it doesn't hurt other queries of course, try clustering on the ip index.

I believe your problem is that the index isn't helping much, it's probably hurting you in fact. If you cluster over ip, however, the scan will go almost sequentially, and there will be no wasted bytes in the pages fetched, which will be much friendlier on your I/O subsystem.

If I were in your shoes, I'd cluster each of the monthly tables as they become inactive.