Unexpected PostgreSQL performance degradation - Mailing list pgsql-novice

I cant seem to find an answer to this in the archives... I'm hoping someone has come across this problem before. Beware
thisis a long email... 

Ive got 2 servers one used for development and one used for production. Both servers are identical in every way except
DEVhas only 512MB of memory. Both run NetBSD 1.6, PostgreSQL 7.3.2 and PHP 4.3.2 

DEV: The DB gets restored (dropped then restored from dump file) from PROD's DB daily. Only the developers use this
server.

PROD: 300+ records get imported daily. Vacuum is run daily. 20+ users access this server daily.

Ive got a PHP script which retrieves data from 5 databases and compiles the data into a temporary table. This temporary
tableis then used to spit out a report for the user. The first part of the script (storing the data in the temp table)
normallytakes about 10-20 seconds. The second part (spitting out the report) takes another 15-30 seconds. Incase your
wondering,I track the time within the PHP script. 

Over a period of 3 or so months the performance of PostgreSQL on the PROD server seems to have degraded.

I run the same PHP script on DEV and PROD with the same data to get a rough indication. DEV finishes the first part
within20 seconds. PROD takes about 1 minute to finish. That is a massive 40 seconds difference! I know what your saying
"PRODhas more users". I lock the PROD server so no one except my IP address can access it. Destroy the DB session / PHP
sessionslinked to anyone logged on, run the test again. Same results! 

For days i was unable to come up with a logical explanation for the degradation in performance. Then my manager
suggestedthat the database was cluttered and that it needed to be "re-orged" (like how windows defrags a HD). This was
newsto me, however since i couldnt find a solution, i pg_dump-ed the DBs and restored them, ran the same test. Low and
beholdPROD finishes the first part of the script within 20 seconds!! 

Funny thing was, i was hounding the server admin saying there is something wrong with server (because there was only
5MBworth of free memory out of 1GB) and he kept on saying no its your script / PostgreSQL thats causing the problem.
Outof curiosity, I checked the memory usage after the DB restoration and the free memory was back up to 500MB! 

Now my questions are:
What could have caused PostgreSQL's performance degradation?
If this is a known problem, is restoring the DB the only way to rectify it?

Thanks in advance
Cody Phanekham


*************************************************************************************
This e-mail, including any attachments to it, may contain confidential and/or personal information.
If you have received this e-mail in error, you must not copy, distribute, or disclose it, use or take any action
based on the information contained within it.

Please notify the sender immediately by return e-mail of the error and then delete the original e-mail.

The information contained within this e-mail may be solely the opinion of the sender and may not necessarily
reflect the position, beliefs or opinions of Salmat on any issue.

This email has been swept for the presence of computer viruses known to Salmat's anti-virus systems.

For more information, visit our website at  www.salmat.com.au.
*************************************************************************************