Skip site navigation (1) Skip section navigation (2)

Re: Possible explanations for catastrophic performance deterioration?

From: "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
To: "Carlos Moreno" <moreno_pg(at)mochima(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Possible explanations for catastrophic performance deterioration?
Date: 2007-09-23 22:29:02
Message-ID: 36e682920709231529k76ce12a2q2f7ac90a05a1f2d2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 9/23/07, Carlos Moreno <moreno_pg(at)mochima(dot)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(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837                        | http://www.enterprisedb.com/

In response to

pgsql-performance by date

Next:From: Gregory StarkDate: 2007-09-23 23:05:31
Subject: Re: Possible explanations for catastrophic performance deterioration?
Previous:From: Carlos MorenoDate: 2007-09-23 21:55:49
Subject: Re: Possible explanations for catastrophic performance deterioration?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group