PostgreSQL

1. SQL Optimization - PostgreSQL explain.md 2. Transactions - PostgreSQL Isolation Levels.md - PostgreSQL MVCC.md

1. Implementation PostgreSQL implements different database isolation levels according to when snapshots are obtained (corresponding to the GetTransactionSnapshot function in the code): - Read Uncommitted/Read Committed: each query obtains the latest snapshot CurrentSnapshotData - Repeatable Read: all queries obtain the same snapshot, which is the snapshot FirstXactSnapshot obtained by the first query - Serializable: implemented using the lock system

1. General Rules - In PostgreSQL, every transaction gets a transaction ID called XID - It can be queried with select cast(txid_current() as text) - The transactions mentioned here are not only groups of statements wrapped by BEGIN - COMMIT, but also individual insert, update, or delete statements - When a transaction begins, PostgreSQL increments XID and assigns it to the transaction

1. Without WHERE 1.1. Ordinary explain - Result 1.1.1. Analysis - Read method - Sequential scan, reading block by block - Statistics - cost - Time to obtain the first row - Time to obtain all rows - The unit is a planner cost unit, not milliseconds - rows - Number of rows scanned - width - Average length of all rows