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
Thanks Gabrielle, Randal, David and Mark for attending!
Next meeting: October 17, 2006 - Postgresql 8.2 (wahoo!) and MySQL
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.
pdxpug by date
|Next:||From: David E. Wheeler||Date: 2006-09-20 17:29:31|
|Subject: Re: PERFORMANCE meeting summary & next meeting topic!|
|Previous:||From: Selena Deckelmann||Date: 2006-09-18 21:37:12|
|Subject: TOMORROW: 9/19/06 - Performance|