Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 829AF9F93A2 for ; Sun, 23 Sep 2007 13:56:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 77945-01-4 for ; Sun, 23 Sep 2007 13:56:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.185]) by postgresql.org (Postfix) with ESMTP id 5321A9F9275 for ; Sun, 23 Sep 2007 13:56:36 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so1073749rvb for ; Sun, 23 Sep 2007 09:56:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=i4dvzSecze8EB1jkGSxxZgz9EMiiHxizCBrzwlekazE=; b=e5oLrfVv9U7KYL+TIwAt4PRXk5BzuZ9dDQyJWyks5Fq7nRFlBOyG4NQ/FFaFh0pQYRyPhK0Fa2ByoUqkVvXpPVNuh+9q+L+wZPBBisuEGjrEzKV8RETN9zF27Nsn2aLC0DSFp9sciJ2caca29KzcFvmMaYAYzemDtaaVB5LyTBQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=LSVFxK1tOB5Ctj7Zp3h0DT/hjkxI9fnc6SFZRN6/NDzKrngAqr7TSGvz7yIcKKuhNfyk3C8A1IAaFV8GD5G0qMmpwSpul5ecAW/q+3fjZ5G1x6B8Ms3KehxOB5avnlUx1l6yl/bDHteutFCjvjEJw26wPXcvBmpOlMtAGXFI9gQ= Received: by 10.142.153.8 with SMTP id a8mr818998wfe.1190566595279; Sun, 23 Sep 2007 09:56:35 -0700 (PDT) Received: by 10.143.19.7 with HTTP; Sun, 23 Sep 2007 09:56:35 -0700 (PDT) Message-ID: <36e682920709230956p1312745eif1c6ba7a342dae3d@mail.gmail.com> Date: Sun, 23 Sep 2007 12:56:35 -0400 From: "Jonah H. Harris" To: "Carlos Moreno" Subject: Re: Possible explanations for catastrophic performace deterioration? Cc: pgsql-performance@postgresql.org In-Reply-To: <46F6981B.1010600@mochima.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <46F6981B.1010600@mochima.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/399 X-Sequence-Number: 26944 You didn't specify the database size, but my guess is that the total data size about enough to fit in shared_buffers or kernel cache. On the new system (or dropped/recreated database), it would've all or mostly fit in memory which would make things like count(*) work quickly. On the old database, you probably had a lot of fragmentation which would've caused significantly more I/O to be performed thereby causing a slowdown. You could compare relation sizes to check easily. My guess is that a vacuum full would've brought the other database back up to speed. In the future, you probably want to set fillfactor to a reasonable amount to account for updates-to-blocks-between-vacuum to try and capture as few row-migrations as possible. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/