From: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query response time
Date: Fri, 11 Aug 2006 14:58:47 +0100
Message-ID: <746457F7E51F3B4BB53A8447D7204B78E463@MVGMAIL.mvg.local> (raw)
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)
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 = t1.date + 365
AND t2.date = t1.date + 14
AND t1.col1 = t2.col1
AND t1.col1 = 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?
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!)
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: jonathan.sinclair@molevalleyfarmers.com
Subject: Re: Query response time
In-Reply-To: <746457F7E51F3B4BB53A8447D7204B78E463@MVGMAIL.mvg.local>
* 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