Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0AD9F9F9A90 for ; Sun, 23 Sep 2007 20:05: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 41451-08 for ; Sun, 23 Sep 2007 20:05:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from oxford.xeocode.com (unknown [87.127.95.196]) by postgresql.org (Postfix) with ESMTP id 21A109F9A68 for ; Sun, 23 Sep 2007 20:05:35 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.67) (envelope-from ) id 1IZaW3-0006DQ-Dj; Mon, 24 Sep 2007 00:05:31 +0100 To: "Carlos Moreno" Cc: Subject: Re: Possible explanations for catastrophic performance deterioration? In-Reply-To: <46F6E0E5.3080208@mochima.com> (Carlos Moreno's message of "Sun\, 23 Sep 2007 17\:55\:49 -0400") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 2603) 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> From: Gregory Stark Organization: EnterpriseDB Date: Mon, 24 Sep 2007 00:05:31 +0100 Message-ID: <874phlugn8.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/410 X-Sequence-Number: 26955 "Carlos Moreno" writes: > 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 That's really not how it works. When Postgres talks to the OS they're just bits. There's no cache of rows or values or anything higher level than bits. Neither the OS's filesystem cache nor the Postgres shared memory knows the difference between live or dead rows or even pages that don't contain any rows. > 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. That's right. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com