X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 586F711F68E9 for ; Thu, 13 Apr 2006 10:08:18 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.174 X-Spam-Level: X-Spam-Status: No, score=0.174 required=5 tests=[AWL=-0.015, MISSING_HEADERS=0.189] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id IPzPl6WTfGsN for ; Thu, 13 Apr 2006 10:08:05 -0300 (ADT) X-Greylist: delayed 01:02:34.988375 by SQLgrey- Received: from fon.nation-net.com (fon.nation-net.com [194.24.251.1]) by postgresql.org (Postfix) with ESMTP id 0BEE711F6767 for ; Thu, 13 Apr 2006 10:08:08 -0300 (ADT) Received: from fon.nation-net.com (localhost [127.0.0.1]) by fon.nation-net.com (Postfix) with ESMTP id 90F0B136F9F for ; Thu, 13 Apr 2006 14:08:04 +0100 (BST) Received: from [194.24.251.10] (bum.net [194.24.251.10]) by fon.nation-net.com (Postfix) with ESMTP id 78922136D94 for ; Thu, 13 Apr 2006 14:08:04 +0100 (BST) Message-ID: <443E4C9D.3090301@laterooms.com> Date: Thu, 13 Apr 2006 14:05:33 +0100 From: Gavin Hamill User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013) X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: pgsql-performance@postgresql.org Subject: Re: Slow query - possible bug? References: <443E3DF5.4020207@laterooms.com> <3c1395330604130546g6b091f75x511955817fe65dcd@mail.gmail.com> In-Reply-To: <3c1395330604130546g6b091f75x511955817fe65dcd@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-AV-Checked: ClamAV using ClamSMTP X-Archive-Number: 200604/297 X-Sequence-Number: 18381 chris smith wrote: >1.6secs isn't too bad on 4.3mill rows... > >How many entries are there for that date range? > > 1.7 secs /is/ good - it typically takes 5 or 6 seconds, which isn't so good. My question is 'why does the planner choose such a bizarre range request when both elements of the 'between' are identical? :)' If I replace the (allocation0_."Date" between '2006-06-09 00:00:00.000000' and '2006-06-09 00:00:00.000000') with allocation0_."Date" ='2006-04-09 00:00:00.000000' then the query comes back in a few milliseconds (as I'd expect :) - and yup I've been using different dates for each test to avoid the query being cached. For ref, there are typically 35000 rows per date :) Cheers, Gavin.