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