Обсуждение: Help calculating load values

I need some help calculating how much activity a query causes.

If I have a table defined as:
create table test_a (
  col1 integer,
  col2 char(1),
  col3 varchar(35),
  primary key (col1,col2)
) with oids;

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)? 

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)?

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)?

I am just trying to get a grasp on how much traffic I am creating for each major dml statement we issue so that I can work with my sysadmin on some i/o issues.

Thanks,

Chris

PG 8.1
RH 4

--
Come see how to SAVE money on fuel, decrease harmful emissions, and even make MONEY.  Visit http://colafuelguy.mybpi.com and join the revolution!

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