### ModifyTable ![[assets/ex_modifytable.png|256]] ### The Explain Trace ```sql -- An UPDATE operation EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) UPDATE animals SET name = 'Renamed' WHERE id = 1; ``` ```text Update on public.animals (cost=1.29..9.30 rows=0 width=0) (actual time=1.813..1.813 rows=0 loops=1) Buffers: shared hit=23 read=3 dirtied=5 -> Index Scan using animals_pkey on public.animals (...) ``` ```sql -- An INSERT operation EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) INSERT INTO animals (name, species_id) VALUES ('New Animal', 1); ``` ```text Insert on public.animals (cost=1.00..1.01 rows=0 width=0) (actual time=1.863..1.864 rows=0 loops=1) Buffers: shared hit=72 read=4 dirtied=3 -> Result (...) ``` --- - **Description**: Handles insert, update, and delete operations. - **Performance**: High overhead due to physical I/O (WAL writing, index updates, and heap modification). Update and Delete are MVCC-compliant, meaning they mark old tuples as dead and (for Update) write new ones. - **Factors**: Number of rows modified, existence of indexes (which must be updated), and foreign key triggers. - **Cost**: `operation cost * number of rows` ![[assets/ex_insert.svg|256]] - **Operates on**: [[Structures/Tuple]] - **Workloads**: - [[Workloads/IO/DataFile/DataFileWrite|IO: DataFileWrite]] - [[Workloads/IO/DataFile/DataFileExtend|IO: DataFileExtend]] - [[Workloads/IO/DataFile/DataFileSync|IO: DataFileSync]] - [[Workloads/IO/WAL/WALWrite|IO: WALWrite]] - [[Workloads/Lock/Lock/Lock_Relation|Lock: Relation]] - [[Workloads/Lock/Lock/Lock_Tuple|Lock: Tuple]] - [[Workloads/Lock/Lock/Lock_TransactionId|Lock: TransactionId]] - [[Workloads/LWLock/WAL/WALInsert|LWLock: WALInsert]] - [[Workloads/LWLock/Buffers/BufferContent|LWLock: BufferContent]] - [[Workloads/LWLock/Buffers/BufferMapping|LWLock: BufferMapping]] - [[Workloads/IPC/Replication/SyncRep|IPC: SyncRep]]