Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VRiTl-0002AE-AY for pgsql-performance@arkaria.postgresql.org; Thu, 03 Oct 2013 12:58:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VRiTk-00039a-FG for pgsql-performance@arkaria.postgresql.org; Thu, 03 Oct 2013 12:58:04 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VRiTj-00039T-8m for pgsql-performance@postgresql.org; Thu, 03 Oct 2013 12:58:03 +0000 Received: from proofpoint2.mail.rice.edu ([128.42.201.101] helo=pp2.rice.edu) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VRiTg-0001qh-2m for pgsql-performance@postgresql.org; Thu, 03 Oct 2013 12:58:02 +0000 Received: from pps.filterd (pp2.rice.edu [127.0.0.1]) by pp2.rice.edu (8.14.5/8.14.5) with SMTP id r93CvMhG031005; Thu, 3 Oct 2013 07:57:58 -0500 Received: from mh3.mail.rice.edu (mh3.mail.rice.edu [128.42.199.10]) by pp2.rice.edu with ESMTP id 1f91vw02y5-1; Thu, 03 Oct 2013 07:57:57 -0500 X-Virus-Scanned: by amavis-2.7.0 at mh3.mail.rice.edu, auth channel X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.rice.edu (aart.rice.edu [168.7.56.48]) by mh3.mail.rice.edu (Postfix) with ESMTP id B3D9B4016F; Thu, 3 Oct 2013 07:57:57 -0500 (CDT) Received: by aart.rice.edu (Postfix, from userid 18612) id A6067100242; Thu, 3 Oct 2013 07:59:18 -0500 (CDT) Date: Thu, 3 Oct 2013 07:59:18 -0500 From: "ktm@rice.edu" To: Samuel Stearns Cc: Claudio Freire , David Johnston , "pgsql-performance@postgresql.org" Subject: Re: 57 minute SELECT Message-ID: <20131003125918.GG16128@aart.rice.edu> References: <1380763034169-5773174.post@n5.nabble.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-12-10) X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 kscore.is_bulkscore=1.0547118733939e-15 kscore.compositescore=0 circleOfTrustscore=0 compositescore=0.984621178355377 urlsuspect_oldscore=0.984621178355377 suspectscore=0 recipient_domain_to_sender_totalscore=0 phishscore=0 bulkscore=0 kscore.is_spamscore=0 recipient_to_sender_totalscore=0 recipient_domain_to_sender_domain_totalscore=423 rbsscore=0.984621178355377 spamscore=0 recipient_to_sender_domain_totalscore=0 urlsuspectscore=0.9 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=7.0.1-1305240000 definitions=main-1310030044 X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Thu, Oct 03, 2013 at 04:19:29AM +0000, Samuel Stearns wrote: > 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' > Hi Samuel, That is even worse performance relatively. 30s for a 3 hour range equals 28800s for a 4 month (2880 hours) range, or 8 hours. I definitely would consider clustering. Regards, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance