Skip site navigation (1) Skip section navigation (2)

PERFORMANCE meeting summary & next meeting topic!

From: Selena Deckelmann <selena(at)chrisking(dot)com>
To: pdxpug(at)postgresql(dot)org
Subject: PERFORMANCE meeting summary & next meeting topic!
Date: 2006-09-20 16:03:16
Message-ID: d8594af3cf2f60c607ab3de140fef00b@chrisking.com (view raw or flat)
Thread:
Lists: pdxpug
Hello!

Last night we had our presentation on performance issues.  I showed an 
example where I used PL/PgSQL to mimic the behavior of a GUI 
application, and the small code change that reduced the number of calls 
to a particularly hairy date-range calculation function.  When I first 
looked at it, I thought that a SUM() call was the performance-killer, 
but after examining the pg_stat & pg_statio tables, it was clear that 
the calendar function was the culprit.  I showed the code I used to 
demonstrate the problem, and the before/after EXPLAIN output.

For Gab's part, she brought in her schema and we had a look at the 
query that was running so slow.  She explained how some optimizations 
we'd tried actually made the query slower.  The test database we used 
in the meeting was running on version 8.1, Gab's is running 7.4.  The 
query was about 400% faster in the newer version.  The general 
consensus was that she should upgrade.  We didn't have a clear answer 
to why the query was so slow in the older version, but it was clear 
that it was faster in the newer version of postgresql.

We also chatted about the planner and optimizing queries.  The 
importance of knowing what the planner is doing was emphasized, but 
that for most general cases, the planner will do the right thing.  For 
example, the order of joins should probably not matter because the 
planner will most likely pick the best one.

Then we had beer at the Lucky Lab!  Two pitchers of porter were 
consumed.

Thanks Gabrielle, Randal, David and Mark for attending!

Next meeting: October 17, 2006 - Postgresql 8.2 (wahoo!) and MySQL 
caveats.

David Wheeler offered to give a rundown of 8.2 and to talk about some 
recent bugaboos he's run into with MySQL.  Other MySQL horror stories, 
or PostgreSQL success stories welcome.

Look how organized we are.

-selena


Responses

pdxpug by date

Next:From: David E. WheelerDate: 2006-09-20 17:29:31
Subject: Re: PERFORMANCE meeting summary & next meeting topic!
Previous:From: Selena DeckelmannDate: 2006-09-18 21:37:12
Subject: TOMORROW: 9/19/06 - Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group