Postgres_Query_Optimize

Index

1: Muti Indexs

根据查找条件异或,bitmap映射到可使用的索引,快速定位所以在heap的位置

  1. index_1 0101

  2. index_2 0110

    e.g. 0101 & 0110 = 0100

根据bitmap(e.g. 0100) 映射到heap索引位置

To combine multiple indexes, the system scans each needed index and prepares a bitmap in memory giving 
the locations of table rows that are reported as matching that index's conditions. The bitmaps are then 
ANDed and ORed together as needed by the query. Finally, the actual table rows are visited and returned. 
The table rows are visited in physical order, because that is how the bitmap is laid out; this means that 
any ordering of the original indexes is lost, and so a separate sort step will be needed if the query has 
an ORDER BY clause. For this reason, and because each additional index scan adds extra time, the planner will 
sometimes choose to use a simple index scan even though additional indexes are available that could have been used as well.

2: Indexs Alg

1. Btree  一维查找,时间复杂度 O(logn)
2. Hash   时间复杂度O(1), 使用数据重复较少的字段:
3. Rtree  多维度查找, 

3:Db Table

pg_clog 保存事务的状态

MVCC

1. Transaction Isolation Levels

1. Levels

1. Read uncommited, 可以读取到其他事务未提交的变更
2. Read commited, 只能读取到其他事务提交的变更
3. Repeatable read (default), 一个事务中,读取到的数据不变,Read Snapshot, 只能读到该事务开始之前提交的数据
4. Serializable , 串行化读,效率低, 数据强一致

2. Features

transaction

2. Snapshot Ctrl Struct

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
   // file: vim src/include/utils/snapshot.h +142
typedef struct SnapshotData
{
SnapshotType snapshot_type; /* type of snapshot */

/*
* The remaining fields are used only for MVCC snapshots, and are normally
* just zeroes in special snapshots. (But xmin and xmax are used
* specially by HeapTupleSatisfiesDirty, and xmin is used specially by
* HeapTupleSatisfiesNonVacuumable.)
*
* An MVCC snapshot can never see the effects of XIDs >= xmax. It can see
* the effects of all older XIDs except those listed in the snapshot. xmin
* is stored as an optimization to avoid needing to search the XID arrays
* for most tuples.
*/
// 读快照数据,能保证可重复读
TransactionId xmin; /* all XID < xmin are visible to me, 最小still_running x_act_id,也就是说比起小的id的事务都已经执行完毕,对此snapshot数据,事务id小于其则数据是不可见 */
TransactionId xmax; /* all XID >= xmax are invisible to me , 最大已经执行完成的xact_id,所有比此id大的事务都没有执行完毕,对此snapshot数据,事务id大于其则数据是不可见*/

TransactionId *xip;
uint32 xcnt; /* # of xact ids in xip[] */
TransactionId *subxip;
int32 subxcnt; /* # of xact ids in subxip[] */
bool suboverflowed; /* has the subxip array overflowed? */
bool takenDuringRecovery; /* recovery-shaped snapshot? */
bool copied; /* false if it's a static snapshot */
CommandId curcid; /* in my xact, CID < curcid are visible */
uint32 speculativeToken;
uint32 active_count; /* refcount on ActiveSnapshot stack */
uint32 regd_count; /* refcount on RegisteredSnapshots */
pairingheap_node ph_node; /* link in the RegisteredSnapshots heap */

TimestampTz whenTaken; /* timestamp when snapshot was taken */
XLogRecPtr lsn; /* position in the WAL stream when taken */
} SnapshotData;

3. Lock Modes

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#define NoLock 0
#define AccessShareLock 1 ///* SELECT */, 共享锁, 可读,不改
#define RowShareLock 2 ///* SELECT FOR UPDATE/FOR SHARE */ 行共享锁,行可读,行不可修改
#define RowExclusiveLock 3 ///* INSERT, UPDATE, DELETE */ 行排他锁, 单行不读不写
#define ShareUpdateExclusiveLock 4 ///* VACUUM (non-FULL),ANALYZE, CREATE INDEX, 不读不更新
* CONCURRENTLY */ 排他并发改
#define ShareLock 5 ///* CREATE INDEX (WITHOUT CONCURRENTLY) */ 保护表并发改
#define ShareRowExclusiveLock 6 ///* like EXCLUSIVE MODE, but allows ROW
* SHARE */ 保护行并发改
#define ExclusiveLock 7 ///* blocks ROW SHARE/SELECT...FOR UPDATE */ 只允许并发 accessExclusive锁
#define AccessExclusiveLock 8 ///* ALTER TABLE, DROP TABLE, VACUUM FULL, 最高级别,
* and unqualified LOCK TABLE */
#define MaxLockMode 8