Re: Query response time

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. :)