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=JtWdHV/+Iv6kUZ42D5YHhoOI+931MLRcbuJvOAsp41e+W7EMvFxSxgM1dkBxhcAJaW1zy6OrGA6rjLrOdhUqjQLZj8oe/ZJ1nmTa5/f0jn/t8HiC1gRF9ClpJPAilnr4HL9ALnwZiIng2/ECtFSqdcUJ61X4my5w20H+gSo5BSs=
Message-ID: <36e682920709231529k76ce12a2q2f7ac90a05a1f2d2@mail.gmail.com>
Date: Sun, 23 Sep 2007 18:29:02 -0400
From: "Jonah H. Harris" <jonah.harris@gmail.com>
To: "Carlos Moreno" <moreno_pg@mochima.com>
Subject: Re: Possible explanations for catastrophic performance deterioration?
Cc: pgsql-performance@postgresql.org
In-Reply-To: <46F6E0E5.3080208@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>
	<36e682920709230956p1312745eif1c6ba7a342dae3d@mail.gmail.com>
	<46F6AD2D.4090300@mochima.com> <20070923182349.GC5679@alvh.no-ip.org>
	<46F6BA82.3080101@mochima.com>
	<36e682920709231224y3fb13ce3t9dd4217215c62bf7@mail.gmail.com>
	<46F6CD9A.2090600@mochima.com> <20070923205759.GD5679@alvh.no-ip.org>
	<46F6E0E5.3080208@mochima.com>

On 9/23/07, Carlos Moreno <moreno_pg@mochima.com> wrote:
> Yes, that part I understand --- I think I now know what the error is in
> my logic.  I was thinking as follows:  We read 2GB of which 1900MB are
> dead tuples.  But then, once they're read, the system will only keep
> in memory the 100MB that are valid tuples.

Yes, this is wrong.

> I'm now thinking that the problem with my logic is that the system does
> not keep anything in memory (or not all tuples, in any case), since it
> is only counting, so it does not *have to* keep them, and since the
> total amount of reading from the disk exceeds the amount of physical
> memory, then the valid tuples are "pushed out" of memory.

Yes, it does keep some in memory, but not all of it.

> So, the second time I execute the query, it will still need to scan the
> disk  (in my mind, the way I was seeing it, the second time I execute
> the "select count(*) from customer", the entire customer table would be
> in memory from the previous time, and that's why I was thinking that
> the bloating would not explain why the second time it is still slow).

Yes, it is still performing additional I/Os and additional CPU work to
read bloated data.

> Am I understanding it right?

Now, I think so.

-- 
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/