pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Chris Hoover <revoohc@gmail.com>
Cc: pgsql-admin@postgresql.org Admin <pgsql-admin@postgresql.org>
Subject: Re: Help calculating load values
Date: Mon, 11 Feb 2008 11:43:42 -0300
Message-ID: <20080211144342.GD7050@alvh.no-ip.org> (raw)
In-Reply-To: <1d219a6f0802060944s55c3a5cbw61cf99010c2f863a@mail.gmail.com>
References: <1d219a6f0802060944s55c3a5cbw61cf99010c2f863a@mail.gmail.com>

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



view thread (2+ messages)

Message-ID: <20080211144342.GD7050@alvh.no-ip.org>
Permalink:  ../20080211144342.GD7050@alvh.no-ip.org/
Also on:    postgresql.org/message-id/20080211144342.GD7050@alvh.no-ip.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: alvherre@commandprompt.com, revoohc@gmail.com
  Subject: Re: Help calculating load values
  In-Reply-To: <20080211144342.GD7050@alvh.no-ip.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox