pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery response time
6+ messages / 5 participants
[nested] [flat]
* Query response time
@ 2006-08-08 12:25 Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
0 siblings, 3 replies; 6+ messages in thread
From: Jonathan Sinclair @ 2006-08-08 12:25 UTC (permalink / raw)
To: pgsql-sql
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!)
From previous experience I know these delays are longer than both
Informix and MySql. In some instances it takes so long I end up having
to kill the query.
The install was performed by yum onto a RAID server using Centos. I am
sure there is something fundamentally wrong for I can't believe that
postgres would have the reputation it has based on the statistics I'm
getting. Does anyone have any advice?
The data I am using was imported from an Informix system as part of a
migration strategy.
I know this is long shot but I hope someone can shed some light.
Regards,
Jonathan
This email may contain information which is privileged or confidential. This information is intended only for the named recipient. If you are not the intended recipient, please be aware that disclosure, copying, distribution or use of this information is prohibited. If you have received this email in error, we would be grateful if you would inform us as soon as possible by telephoning +44 (0) 1769 573431, or by email to admin@molevalleyfarmers.com and then delete this email. Views or opinions expressed in this email are those of the writer, and are not necessarily the views of Mole Valley Farmers Limited or its subsidiary companies. Unless specifically stated, this email does not constitute any part of an offer or contract.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query response time
@ 2006-08-08 12:48 Chris Mair <chrisnospam@1006.org>
parent: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
2 siblings, 0 replies; 6+ messages in thread
From: Chris Mair @ 2006-08-08 12:48 UTC (permalink / raw)
To: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>; +Cc: pgsql-sql
> 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!)
> >From previous experience I know these delays are longer than both
> Informix and MySql. In some instances it takes so long I end up having
> to kill the query.
Please run the command "analyze" on your database (you can do that with
the psql shell), then post to this list:
- your query
- the output of the command "explain + your query"
- the layout of the tables concerned by the query (
"\d tablename" from psql)
> The install was performed by yum onto a RAID server using Centos. I am
> sure there is something fundamentally wrong for I can't believe that
> postgres would have the reputation it has based on the statistics I'm
> getting. Does anyone have any advice?
> The data I am using was imported from an Informix system as part of a
> migration strategy.
> I know this is long shot but I hope someone can shed some light.
If you're on pgsql-performance@postgresql.org as well, we might continue
discussion there.
Bye :)
Chris.
--
Chris Mair
http://www.1006.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query response time
@ 2006-08-08 12:53 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
2 siblings, 0 replies; 6+ messages in thread
From: Richard Broersma Jr @ 2006-08-08 12:53 UTC (permalink / raw)
To: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>; pgsql-sql
> 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!)
> From previous experience I know these delays are longer than both
> Informix and MySql. In some instances it takes so long I end up having
> to kill the query.
>
> The install was performed by yum onto a RAID server using Centos. I am
> sure there is something fundamentally wrong for I can't believe that
> postgres would have the reputation it has based on the statistics I'm
> getting. Does anyone have any advice?
>
> The data I am using was imported from an Informix system as part of a
> migration strategy.
> I know this is long shot but I hope someone can shed some light.
Are the Update/Insert queries slow or is it the select queries that are taking awhile?
For select queries, an explain analyze of the offending query would be helpful. Also, in addition
to vacuuming you may want to reindex you db in order to clean all of the dead tuples from your
indexs.
For heavy insert/update queries check your postgres logs to see if any messages suggest increasing
your check-point-segments.
If this is the case, try increasing you check_point_segments and try moving your pg_xlog to a
different spindle.
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query response time
@ 2006-08-09 17:24 Roman Neuhauser <neuhauser@sigpipe.cz>
parent: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
2 siblings, 0 replies; 6+ messages in thread
From: Roman Neuhauser @ 2006-08-09 17:24 UTC (permalink / raw)
To: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>; +Cc: pgsql-sql
# jonathan.sinclair@molevalleyfarmers.com / 2006-08-08 13:25:47 +0100:
> I am using PostgresSQL 7.4 and having some serious performance issues.
> I am using tables that only contain approx 2GB of data.
> The install was performed by yum onto a RAID server using Centos. I am
> sure there is something fundamentally wrong for I can't believe that
> postgres would have the reputation it has based on the statistics I'm
> getting. Does anyone have any advice?
You'll need to configure PostrgreSQL, the default settings seem
to be tuned so that PostgreSQL starts even in very limited
environments.
http://www.postgresql.org/docs/8.1/static/runtime-config.html
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query response time
@ 2006-08-11 13:58 Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jonathan Sinclair @ 2006-08-11 13:58 UTC (permalink / raw)
To: pgsql-sql
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!)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query response time
@ 2006-08-15 13:49 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2006-08-15 13:49 UTC (permalink / raw)
To: Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>; +Cc: pgsql-sql
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. :)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-08-15 13:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-08 12:25 Query response time Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
2006-08-08 12:48 ` Chris Mair <chrisnospam@1006.org>
2006-08-08 12:53 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-08-09 17:24 ` Roman Neuhauser <neuhauser@sigpipe.cz>
2006-08-11 13:58 Re: Query response time Jonathan Sinclair <jonathan.sinclair@molevalleyfarmers.com>
2006-08-15 13:49 ` Scott Marlowe <smarlowe@g2switchworks.com>
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