Re: Performance problem with Sarge compared with Woody

[Jeff Davis <pgsql(at)j-davis(dot)com>]

On Mon, 2006-09-11 at 20:14 +0200, Piñeiro wrote:
> Hi,
> 
> a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
> 7.4.7). To migrate the database we use a dump, using pg_dump with this
> options:
> pg_dump -U <username> -c -F p -O -v -f <filename> <DBname>
> 
> We have a search, that using woody take about 1-2 minutes, but with
> sarge it is executing about 2 hours, and at least it crashes, with a
> message about a temporal file and no more disk space ( i have more than
> a GB of free disk space).
> 

It sounds to me like it's choosing a bad sort plan, and unable to write
enough temporary disk files.

A likely cause is that you did not "vacuum analyze" after you loaded the
data. Try running that command and see if it helps. If not, can you
provide the output of "explain" and "explain analyze" on both the old
database and the new?

Also, I suggest that you upgrade to 8.1. 7.4 is quite old, and many
improvements have been made since then.

Regards,
	Jeff Davis