X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0738E8BA27B for ; Tue, 15 Feb 2005 04:21:10 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35112-10 for ; Tue, 15 Feb 2005 04:21:07 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id A4B3E8BA24E for ; Tue, 15 Feb 2005 04:21:05 +0000 (GMT) Received: from dyn-69-234.tor.dsl.tht.net (dyn-69-234.tor.dsl.tht.net [134.22.69.234]) by tht.net (Postfix) with ESMTP id 026DF76A36; Mon, 14 Feb 2005 23:21:08 -0500 (EST) Subject: Re: seq scan cache vs. index cache smackdown From: Rod Taylor To: Iain Cc: Postgresql Performance , Mark Aufflick In-Reply-To: <00a001c51312$35fb5510$7201a8c0@mst1x5r347kymb> References: <00a001c51312$35fb5510$7201a8c0@mst1x5r347kymb> Content-Type: text/plain Date: Mon, 14 Feb 2005 23:20:51 -0500 Message-Id: <1108441251.67118.111.camel@home> Mime-Version: 1.0 X-Mailer: Evolution 2.0.3 FreeBSD GNOME Team Port Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.018 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/227 X-Sequence-Number: 10556 > My concern is that this kind of testing has very little relevance to the > real world of multiuser processing where contention for the cache becomes an > issue. It may be that, at least in the current situation, postgres is > giving too much weight to seq scans based on single user, straight line To be fair, a large index scan can easily throw the buffers out of whack as well. An index scan on 0.1% of a table with 1 billion tuples will have a similar impact to buffers as a sequential scan of a table with 1 million tuples. Any solution fixing buffers should probably not take into consideration the method being performed (do you really want to skip caching a sequential scan of a 2 tuple table because it didn't use an index) but the volume of data involved as compared to the size of the cache. I've often wondered if a single 1GB toasted tuple could wipe out the buffers. I would suppose that toast doesn't bypass them. -- Rod Taylor