Be Inclusive: Welcome Non-key Columns in B-Tree Indexes - PowerPoint PPT Presentation


 B-tree Index: INCLUDE (since 11) } CREATE INDEX … K K K K K K K K … ON … E E E E E E E E Not so deep Y Y Y Y Y Y Y Y ( k ) INCLUDE (data) K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E VM Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) CREATE INDEX … K K K K K K K K … ON … E E E E E E E E Y Y Y Y Y Y Y Y ( k ) INCLUDE (data) K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E VM Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) SELECT data CREATE INDEX … K K K K K K K K … FROM … ON … E E E E E E E E Y Y Y Y Y Y Y Y WHERE k = $1 ( k ) INCLUDE (data) K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E VM Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) SELECT data CREATE INDEX … K K K K K K K K … FROM … ON … E E E E E E E E Y Y Y Y Y Y Y Y WHERE k = $1 ( k ) INCLUDE (data) K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E VM Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) SELECT data CREATE INDEX … K K K K K K K K … FROM … ON … E E E E E E E E Y Y Y Y Y Y Y Y WHERE k = $1 ( k ) INCLUDE (data) K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E VM Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) SELECT data CREATE INDEX … K K K K K K K K … FROM … ON … E E E E E E E E Y Y Y Y Y Y Y Y WHERE k = $1 ( k ) INCLUDE (data) K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E VM Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) SELECT data CREATE INDEX … K K K K K K K K … FROM … ON … E E E E E E E E Y Y Y Y Y Y Y Y WHERE k = $1 ( k ) INCLUDE (data) K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E VM Table 
 (Heap)

INCLUDE — Pro and Con vs. Key-Columns

INCLUDE — Pro and Con vs. Key-Columns Advantages:

INCLUDE — Pro and Con vs. Key-Columns Advantages: ‣ Shallower: < ~40%

INCLUDE — Pro and Con vs. Key-Columns Advantages: ‣ Shallower: < ~40% ‣ Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns Advantages: Disadvantages: ‣ Shallower: < ~40% ‣ … ‣ Slightly smaller: < ~3%

INCLUDE — Disadvantages: WHERE SELECT * FROM … WHERE k = $1 AND data = $2

INCLUDE — Disadvantages: WHERE SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … ON … ( k, data ) QUERY PLAN -------------------------------------------------- Index Scan using … on … (actual rows=1) Index Cond: ((key = $1) AND (data = $2) Bu ff ers: shared hit=5

INCLUDE — Disadvantages: WHERE SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … CREATE INDEX … ON … ON … ( k ) ( k, data ) INCLUDE ( data ) QUERY PLAN QUERY PLAN ----------------------------------------------------------- -------------------------------------------------- Bitmap Heap Scan on … (actual rows=1) Index Scan using … on … (actual rows=1) Recheck Cond: (key = 123) Index Cond: ((key = $1) AND (data = $2) Filter: (data = $2) Bu ff ers: shared hit=5 Rows Removed by Filter: 9999 Heap Blocks: exact=10000 Bu ff ers: shared hit=2186 read=7867 -> Bitmap Index Scan on … (actual rows=10000 ) Index Cond: (key = 123) Buffers: shared read=53

INCLUDE — Disadvantages: WHERE SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … CREATE INDEX … ON … ON … ( k ) ( k, data ) INCLUDE ( data ) QUERY PLAN QUERY PLAN ----------------------------------------------------------- -------------------------------------------------- Bitmap Heap Scan on … (actual rows=1) Index Scan using … on … (actual rows=1) Recheck Cond: (key = 123) Index Cond: ((key = $1) AND (data = $2) Filter: (data = $2) Bu ff ers: shared hit=5 Rows Removed by Filter: 9999 Doesn’t Heap Blocks: exact=10000 Filter on INCLUDE 
 Bu ff ers: shared hit=2186 read=7867 column -> Bitmap Index Scan on … (actual rows=10000 ) Index Cond: (key = 123) Buffers: shared read=53


 B-tree Index: INCLUDE (since 11) CREATE INDEX … SELECT * K K K K K K K K … ON … E E E E E E E E FROM … Y Y Y Y Y Y Y Y ( k ) WHERE k = $1 INCLUDE (data) AND data = $2 K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) CREATE INDEX … SELECT * K K K K K K K K … ON … E E E E E E E E FROM … Y Y Y Y Y Y Y Y ( k ) WHERE k = $1 INCLUDE (data) AND data = $2 K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) CREATE INDEX … SELECT * K K K K K K K K … ON … E E E E E E E E FROM … Y Y Y Y Y Y Y Y ( k ) WHERE k = $1 INCLUDE (data) AND data = $2 K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) CREATE INDEX … SELECT * K K K K K K K K … ON … E E E E E E E E FROM … Y Y Y Y Y Y Y Y ( k ) WHERE k = $1 INCLUDE (data) AND data = $2 K K K K K K K K K K K K K K K E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) CREATE INDEX … SELECT * K K K K K K K K … ON … E E E E E E E E FROM … Y Y Y Y Y Y Y Y ( k ) WHERE k = $1 INCLUDE (data) AND data = $2 K K K K K K K K K K K K K K K Ignored E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y (unknown if visible) I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C L L L L L L L L L L L L L L U U U U U U U U U U U U U U D D D D D D D D D D D D D D E E E E E E E E E E E E E E Table 
 (Heap)


 B-tree Index: INCLUDE (since 11) CREATE INDEX … SELECT * K K K K K K K K … ON … E E E E E E E E FROM … Y Y Y Y Y Y Y Y ( k ) WHERE k = $1 INCLUDE (data) AND data = $2 K K K K K K K K K K K K K K K Ignored E E E E E E E E E E E E E E E Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y (unknown if visible) I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 I 
 … … … N N N N N N N N N N N N N N N C C C C C C C C C C C C C C C Table access 
 L L L L L L L L L L L L L L U U U U U U U U U U U U U U not reduced D D D D D D D D D D D D D D E E E E E E E E E E E E E E Table 
 (Heap)

B-tree Index: INCLUDE (since 11) 1. Use tree 2. Use doubly linked list 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s 1. Use tree 2. Use doubly linked list 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 (in b-tree key: <=, <, =, >, >=) 1. Use tree 2. Use doubly linked list 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 Other 
 (in b-tree key: <=, <, =, >, >=) 1. Use tree 2. Use doubly linked list 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 Other 
 (in b-tree key: <=, <, =, >, >=) 1. Use tree 2. Use doubly linked list C o l u m n s 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 Other 
 (in b-tree key: <=, <, =, >, >=) 1. Use tree 2. Use doubly linked list C o l u m n s Key 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 Other 
 (in b-tree key: <=, <, =, >, >=) ✓ 1. Use tree 2. Use doubly linked list C o l u m n s Key 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 Other 
 (in b-tree key: <=, <, =, >, >=) ✓ 1. Use tree ✗ 2. Use doubly linked list C o l u m n s Key 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 Other 
 (in b-tree key: <=, <, =, >, >=) ✓ 1. Use tree ✗ 2. Use doubly linked list C o l u m n s Key 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 INCLUDE (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 Other 
 (in b-tree key: <=, <, =, >, >=) ✓ 1. Use tree ✗ 2. Use doubly linked list C o l u m n s Key 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 INCLUDE ✗ (in VM and/or in table) 5. Apply remaining filters

B-tree Index: INCLUDE (since 11) O p e r a t i o n s Safe 
 Other 
 (in b-tree key: <=, <, =, >, >=) ✓ 1. Use tree ✗ 2. Use doubly linked list C o l u m n s Key 3. Apply “safe” filters (based on the operator class) 4. Check visibility 
 INCLUDE ✗ (in VM and/or in table) Not applicable: 
 INCLUDE columns 
 5. Apply remaining filters have no op class

INCLUDE — Disadvantages: WHERE SELECT data FROM … WHERE k = $1 AND data = $2

INCLUDE — Disadvantages: WHERE SELECT data FROM … WHERE k = $1 AND data = $2 Allow Index Only Scan

INCLUDE — Disadvantages: WHERE SELECT data FROM … WHERE k = $1 AND data = $2 Safe in B-Tree Key

INCLUDE — Disadvantages: WHERE SELECT data FROM … WHERE k = $1 AND data = $2 Safe in B-Tree Key CREATE INDEX … ON … ( k, data ) QUERY PLAN ------------------------------------------------------- Index Only Scan using … on … (actual rows=1) Index Cond: (key = $1) AND ( data =$2 ) Heap Fetches: 0 Bu ff ers: shared hit=5

INCLUDE — Disadvantages: WHERE SELECT data FROM … WHERE k = $1 AND data = $2 CREATE INDEX … CREATE INDEX … ON … ON … ( k ) ( k, data ) INCLUDE ( data ) QUERY PLAN QUERY PLAN --------------------------------------------------------- ------------------------------------------------------- Index Only Scan using … on … (actual rows=1) Index Only Scan using … on … (actual rows=1) Index Cond: (key = $1) Index Cond: (key = $1) AND ( data =$2 ) Filter: (data = $2) Heap Fetches: 0 Rows Removed by Filter: 9999 Bu ff ers: shared hit=5 Heap Fetches: 0 Bu ff ers: shared hit=56

INCLUDE — Pro and Con vs. Key-Columns Advantages: Disadvantages: ‣ Shallower: < ~40% ‣ Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns Advantages: Disadvantages: ‣ Shallower: < ~40% ‣ No safe WHERE conditions 
 (visibility always checked first) ‣ Slightly smaller: < ~3%

INCLUDE — Disadvantages: ORDER BY SELECT * FROM … WHERE k = $1 ORDER BY data LIMIT 1

INCLUDE — Disadvantages: ORDER BY SELECT * FROM … WHERE k = $1 ORDER BY data LIMIT 1 CREATE INDEX … ON … ( k, data ) QUERY PLAN ------------------------------------------------------- Limit (actual rows=1) Buffers: shared hit=5 -> Index Scan using … (actual rows=1) Index Cond: (key = 123) Heap Fetches: 0 Bu ff ers: shared hit=5

INCLUDE — Disadvantages: ORDER BY SELECT * FROM … WHERE k = $1 ORDER BY data LIMIT 1 CREATE INDEX … ON … ( k, data ) QUERY PLAN ------------------------------------------------------- No Sort Limit (actual rows=1) Buffers: shared hit=5 -> Index Scan using … (actual rows=1) Index Cond: (key = 123) Heap Fetches: 0 Bu ff ers: shared hit=5

INCLUDE — Disadvantages: ORDER BY SELECT * FROM … WHERE k = $1 ORDER BY data LIMIT 1 CREATE INDEX … CREATE INDEX … ON … ON … ( k ) ( k, data ) INCLUDE ( data ) QUERY PLAN QUERY PLAN ------------------------------------------------------------ ------------------------------------------------------- No Sort Limit (actual rows=1) Limit (actual rows=1) -> Sort (actual rows=1) Buffers: shared hit=5 -> Bitmap Heap Scan on…(actual rows=10000 ) -> Index Scan using … (actual rows=1) Recheck Cond: (key = 123) Index Cond: (key = 123) Heap Blocks: exact=10000 Heap Fetches: 0 Bu ff ers: shared hit=10053 Bu ff ers: shared hit=5 -> Bitmap Index Scan on…(act rows=10000 ) Index Cond: (key = 123) Buffers: shared hit=53

INCLUDE — Pro and Con vs. Key-Columns Advantages: Disadvantages: ‣ Shallower: < ~40% ‣ Doesn’t help WHERE 
 (except Index Only Scan ) ‣ Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns Advantages: Disadvantages: ‣ Shallower: < ~40% ‣ Doesn’t help WHERE 
 (except Index Only Scan ) ‣ Slightly smaller: < ~3% ‣ Cannot replace sorting 
 ( ORDER BY )

INCLUDE — Differences: Constraints

INCLUDE — Differences: Constraints ALTER TABLE … ADD PRIMARY KEY|UNIQUE 
 (key, data)

INCLUDE — Differences: Constraints ALTER TABLE … INSERT INTO … , (1, 2) ✓ ADD PRIMARY KEY|UNIQUE 
 VALUES (1, 1) (key, data)

INCLUDE — Differences: Constraints ALTER TABLE … INSERT INTO … , (1, 2) ✓ ADD PRIMARY KEY|UNIQUE 
 VALUES (1, 1) (key, data) ALTER TABLE … ADD PRIMARY KEY|UNIQUE 
 (key) INCLUDE(data)

INCLUDE — Differences: Constraints ALTER TABLE … INSERT INTO … ✓ ADD PRIMARY KEY|UNIQUE 
 VALUES (1, 1) (key, data) , (1, 2) ALTER TABLE … INSERT INTO … ✗ ADD PRIMARY KEY|UNIQUE 
 VALUES (1, 1) (key) , (1, 2) INCLUDE(data)

INCLUDE — Pro and Con vs. Key-Columns Advantages: Disadvantages: ‣ Shallower: < ~40% ‣ Doesn’t help WHERE 
 (except Index Only Scan ) ‣ Slightly smaller: < ~3% ‣ Cannot replace sorting 
 ( ORDER BY ) Differences: PRIMARY KEY / UNIQUE don’t take INCLUDE columns into account

INCLUDE and the Three Powers Of B-tree Indexes https://use-the-index-luke.com/

INCLUDE and the Three Powers Of B-tree Indexes ‣ Finding data quickly https://use-the-index-luke.com/