X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CC1169FB2EA for ; Tue, 15 Aug 2006 10:49:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 62265-08 for ; Tue, 15 Aug 2006 10:49:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 8D7CF9FB2DB for ; Tue, 15 Aug 2006 10:49:53 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Tue, 15 Aug 2006 13:49:47 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 15 Aug 2006 08:49:47 -0500 Subject: Re: Query response time From: Scott Marlowe To: Jonathan Sinclair Cc: pgsql-sql@postgresql.org In-Reply-To: <746457F7E51F3B4BB53A8447D7204B78E463@MVGMAIL.mvg.local> References: <746457F7E51F3B4BB53A8447D7204B78E463@MVGMAIL.mvg.local> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1155649787.20252.175.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 15 Aug 2006 08:49:47 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.089 tagged_above=0 required=5 tests=AWL, SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200608/107 X-Sequence-Number: 26065 On Fri, 2006-08-11 at 08:58, Jonathan Sinclair wrote: > Hi all. Thanks for your help so far. However after configuring my system > I am still getting major lag times with a couple of queries. The first, > which suffers from the '538/539'(see old email included below) bug, is > caused by running the following statement: > > SELECT t1.col1, SUM(test) test_col, SUM(col2) > FROM table1 tl, table2 t2 > WHERE t1.date BETWEEN '01/10/2004' AND '01/10/2004' > AND t1.col3 = t2.col1 > AND t1.col5 = t2.col2 > AND t2.col3 BETWEEN 50.00 AND 200.00 > GROUP BY t1.col1 > HAVING SUM(test) BETWEEN 95.00 AND 100.00 > ORDER BY 2 DESC, t1.col1; > > I would like to know if anyone has any ideas why this problem arises. > (It's not due to the date format being ambiguous; I have set it to > European standard) Have you looked at the plan for this query? explain select... If I remember correctly, the planner has a bug where if you do a between with the same date, it does a seq scan instead of using an index. But my memory on that's a bit rough. How does it work if you change the where clause to be "t1.date = '01/10/2004'??? P.s. this is more of a performance than a SQL question. Not that that's a big deal or anything, the SQL list is kinda slow and can likely use the traffic. :)