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. :) ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings