Content-class: urn:content-classes:message
Subject: Re: Query response time
MIME-Version: 1.0
Content-Type: text/plain;
	charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Date: Fri, 11 Aug 2006 14:58:47 +0100
Message-ID: <746457F7E51F3B4BB53A8447D7204B78E463@MVGMAIL.mvg.local>
Thread-Topic: [SQL] Query response time
thread-index: Aca65ca6B9TwJt0zTWqPoNoPs71RZwCZgZ+w
From: "Jonathan Sinclair" <jonathan.sinclair@molevalleyfarmers.com>
To: <pgsql-sql@postgresql.org>


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 =3D t2.col1
AND t1.col5 =3D 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)

A second statement I have prepared has the same problem but this time
works fine with a limit of 2 but breaks as soon as I set the limit to 3.
The offending statement is:

SELECT t1.col1, t1.col2, t2.col2, t3.col2
FROM table1 t1, table2 t2, table3 t3,
WHERE t1.date BETWEEN '01/10/2004' AND '01/10/2004'
AND t3.date =3D t1.date + 365
AND t2.date =3D t1.date + 14
AND t1.col1 =3D t2.col1
AND t1.col1 =3D t3.col1
And t1.col1 <> ''
AND t2.col2 < t1.col2;

Both queries work for limited limit sets. But break at different points
i.e. enter infinite loops?=0D
Does anyone have any ideas? Both queries process fine in Informix!

I have run vacuum analyze and full commands on both tables! This didn't
solve the problem.

Regards,

Jonathan

-----Original Message-----

Hi all,

I am using PostgresSQL 7.4 and having some serious performance issues.
Trawling through the archives and previous posts the only visible advice
I could see was either by running vacuum or setting the fsynch flag to
false.


I am using tables that only contain approx 2GB of data. However
performing a number of simple conditional select statements takes a
great deal of time. Putting limits on the data obviously reduces the
time, but there is still a delay. (Note: on one particular query I set
the limit to 538 and the query returns in under 2mins if the limit
becomes 539 the query loops indefinitely!)