Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id D5DA52E00A4 for ; Mon, 11 Feb 2008 10:44:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 73129-01 for ; Mon, 11 Feb 2008 10:43:45 -0400 (AST) Received: from lists.commandprompt.com (host-159.commandprompt.net [207.173.203.159]) by postgresql.org (Postfix) with ESMTP id E32CB2E008E for ; Mon, 11 Feb 2008 10:43:53 -0400 (AST) Received: from perhan.alvh.no-ip.org (200-126-103-57.bk7-dsl.surnet.cl [200.126.103.57]) (authenticated bits=0) by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id m1BEi2OJ018996 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 11 Feb 2008 06:44:05 -0800 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id EB37247BEB; Mon, 11 Feb 2008 11:43:42 -0300 (CLST) Date: Mon, 11 Feb 2008 11:43:42 -0300 From: Alvaro Herrera To: Chris Hoover Cc: "pgsql-admin@postgresql.org Admin" Subject: Re: Help calculating load values Message-ID: <20080211144342.GD7050@alvh.no-ip.org> References: <1d219a6f0802060944s55c3a5cbw61cf99010c2f863a@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <1d219a6f0802060944s55c3a5cbw61cf99010c2f863a@mail.gmail.com> User-Agent: Mutt/1.5.17+20080114 (2008-01-14) X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (lists.commandprompt.com [207.173.203.159]); Mon, 11 Feb 2008 06:44:06 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/130 X-Sequence-Number: 28204 Chris Hoover escribió: > When I do an "insert into test_a values (1,'a','test data');", does this > generate 3 writes (1 to table, 1 to index, and one to oid counter) and one > read (get oid from oid counter)? No -- OID is in shared memory and updated "in batches" (i.e. once in a while the server records a bunch of new numbers). So you have 1. a write to the heap (table) 2. a write to the index (which could cause page splits) 3. possibly a write to the toast table, if the row is large enough 4. if (3), then a write to the toast index In this case the tuples are short enough that the toast table is not going to be used. > What about when I do an "update test_a set col3='changed data' where col1 = > 1 and col2 = 'a';"? I am thinking 5 writes (1 to old table tuple, 1 to old > index tuple, 1 to oid counter, 1 to new table tuple, one to new index tuple) > with 1 read (get oid from oid counter)? (1) a write to the original heap tuple (2) the new heap tuple (3) the new index tuple Plus possible writes to TOAST. I don't think the old index tuple is touched. > Finally, what about the delete "delete from test_a where col1=1 and > col2='a';"? 2 writes (1 to table tuple, and 1 to index tuple)? The original heap tuple is touched. The index tuple is not touched. Index tuples are only removed by VACUUM when their heap tuples become dead. TOAST tuples are not touched on update either AFAIR, but I'm not really sure about that. Note that as of Pg 8.3, these no longer hold due to HOT. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support