From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query response time
Date: Tue, 15 Aug 2006 08:49:47 -0500
Message-ID: <1155649787.20252.175.camel@state.g2switchworks.com> (raw)
In-Reply-To: <746457F7E51F3B4BB53A8447D7204B78E463@MVGMAIL.mvg.local>
References: <746457F7E51F3B4BB53A8447D7204B78E463@MVGMAIL.mvg.local>
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. :)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, jonathan.sinclair@molevalleyfarmers.com
Subject: Re: Query response time
In-Reply-To: <1155649787.20252.175.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox