From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: REPOST: Performance improves only after repeated VACUUM/ANALYZE
Date: Thu, 20 Sep 2007 10:59:54 -0400
Organization: Hub.Org Networking Services
Lines: 34
Message-ID: <fcu1r4$2cj4$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
To: pgsql-performance@postgresql.org

(SORRY FOR THE REPOST, I DON'T SEE MY ORIGINAL QUESTION OR ANY ANSWERS HERE)

My client "publishes" an "edition" of their DB from his production site to 
his hosted web/db server. This is done by FTPing a backup of the DB to his 
hosting provider.

Immediately after a "publication" (restore to web/db server) we immediately 
run VACUUM ANALYZE to make sure the statistics and row estimates are 
correct.

The problem is, after this initial VACUUM ANALYZE, the row estimates in 
query plans are off by several orders of magnitude. For example, a 
disastrous plan was created because the planner estimated 4K rows when in 
fact it returned 980K rows.

Sometimes - a day or two later - the plans return to "normal" and row 
estimates are closer to realistic values. Guessing that there may be 
background events that are correcting the row estimates over time, I ran an 
ANALYZE on the DB - and sure enough - the row estimates corrected 
themselves. The puzzling thing is, there have been no writes of any sort to 
the data - there is no reason for the stats to have changed.

I believe that a VACUUM may not be necessary for a newly restored DB, but I 
assumed that VACUUM ANALYZE and ANALYZE have the same net result. Am I 
wrong?

If I am not wrong (i.e. VACUUM ANALYZE and ANALYZE should produce the same 
results) why would the performance improve on a DB that has seen no 
transactional activity only after the SECOND try?

PG 8.2.4 on RH LINUX 1GB RAM SCSI RAID 1

Carlo