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

▶
be inclusive welcome non key columns in b tree indexes
SMART_READER_LITE
LIVE PREVIEW

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

May 04, 2023 346 likes •1.59k views

Be Inclusive: Welcome Non-key Columns in B-Tree Indexes @MarkusWinand @SQLPerfTips @ModernSQL Safe Harbour Statement Instead of a Safe Harbour Statement Instead of a Safe Harbour Statement Take this Safe the Planet

slide-1
SLIDE 1

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

@MarkusWinand • @SQLPerfTips • @ModernSQL

slide-2
SLIDE 2

Safe Harbour Statement …

slide-3
SLIDE 3

Safe Harbour Statement … Instead of a

slide-4
SLIDE 4

Safe Harbour Statement … Take this Safe the Planet Statement:

I’m traveling a lot for business and always aim for the most environmental friendly way to do so. If I cannot avoid flying, I offset its climate impact at a transparent climate protection company. (currently atmosfair.de)

Picture: https://upload.wikimedia.org/wikipedia/commons/2/2c/North_America_from_low_orbiting_satellite_Suomi_NPP .jpg

Instead of a

slide-5
SLIDE 5

B-tree Index: A Doubly-Linked List…

Table
 (Heap)

slide-6
SLIDE 6

B-tree Index: A Doubly-Linked List…

K E Y Table
 (Heap)

slide-7
SLIDE 7

B-tree Index: A Doubly-Linked List…

K E Y Table
 (Heap)

slide-8
SLIDE 8

B-tree Index: A Doubly-Linked List…

K E Y K E Y Table
 (Heap)

slide-9
SLIDE 9

B-tree Index: A Doubly-Linked List…

K E Y K E Y K E Y K E Y

…

Table
 (Heap)

slide-10
SLIDE 10

B-tree Index: A Doubly-Linked List…

K E Y K E Y K E Y K E Y

…

Table
 (Heap)

Page

slide-11
SLIDE 11

B-tree Index: A Doubly-Linked List…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

Table
 (Heap)

Page

slide-12
SLIDE 12

B-tree Index: A Doubly-Linked List…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

Table
 (Heap)

Page

slide-13
SLIDE 13

B-tree Index: A Doubly-Linked List…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

Table
 (Heap)

Page

K E Y K E Y

slide-14
SLIDE 14

B-tree Index: A Doubly-Linked List…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

Table
 (Heap)

Page

A Z

Logically ordered by KEY

K E Y K E Y

slide-15
SLIDE 15

B-tree Index: … and a Tree

K E K E K E K E

…

K E K E K E K E

…

K E K E K E K E

… Page

K E K E

slide-16
SLIDE 16

B-tree Index: … and a Tree

K E K E K E K E

…

K E K E K E K E

…

K E K E K E K E

… Page

K E K E K E Y K E Y K E Y K E Y

…

slide-17
SLIDE 17

B-tree Index: … and a Tree

K E K E K E K E

…

K E K E K E K E

…

K E K E K E K E

… Page

K E K E K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

slide-18
SLIDE 18

B-tree Index: … and a Tree

K E K E K E K E

…

K E K E K E K E

…

K E K E K E K E

… Page

K E K E K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

slide-19
SLIDE 19

B-tree Index: … and a Tree

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

Table
 (Heap)

Page

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

slide-20
SLIDE 20

B-tree Index: … and a Tree

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

Table
 (Heap)

Page

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

K E Y K E Y K E Y K E Y

…

Root Node

slide-21
SLIDE 21

B-tree Index: How it is used

CREATE INDEX … ON … ( k )

slide-22
SLIDE 22

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-23
SLIDE 23

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-24
SLIDE 24

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-25
SLIDE 25

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-26
SLIDE 26

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-27
SLIDE 27

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-28
SLIDE 28

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-29
SLIDE 29

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-30
SLIDE 30

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-31
SLIDE 31

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

slide-32
SLIDE 32

B-tree Index: How it is used

SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k )

  • 1. Use tree
  • 2. Use doubly linked list
  • 3. Access Table
slide-33
SLIDE 33

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

SELECT data FROM … WHERE k = $1

  • 1. Use tree
  • 2. Use doubly linked list
  • 3. Access Table
slide-34
SLIDE 34

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

CREATE INDEX … ON … ( k, data ) SELECT data FROM … WHERE k = $1

  • 1. Use tree
  • 2. Use doubly linked list
  • 3. Access Table
slide-35
SLIDE 35

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

No Heap Access


(if visibility map is clear)

CREATE INDEX … ON … ( k, data ) SELECT data FROM … WHERE k = $1

  • 1. Use tree
  • 2. Use doubly linked list
  • 3. Access Table
slide-36
SLIDE 36

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

No Heap Access


(if visibility map is clear)

CREATE INDEX … ON … ( k, data ) SELECT data FROM … WHERE k = $1

  • 1. Use tree
  • 2. Use doubly linked list
  • 3. Access Table
slide-37
SLIDE 37

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

CREATE INDEX … ON … ( k, data ) Visibility information is only stored in table

  • 1. Use tree
  • 2. Use doubly linked list
slide-38
SLIDE 38

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

CREATE INDEX … ON … ( k, data ) Visibility information is only stored in table

VM

Block-level info is
 in the Visibility Map (VM)


(32k times smaller)

  • 1. Use tree
  • 2. Use doubly linked list
slide-39
SLIDE 39

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

CREATE INDEX … ON … ( k, data ) Visibility information is only stored in table

VM

Block-level info is
 in the Visibility Map (VM)


(32k times smaller)

  • 1. Use tree
  • 2. Use doubly linked list
  • 3. Check visibility in VM
slide-40
SLIDE 40

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

CREATE INDEX … ON … ( k, data ) Visibility information is only stored in table

VM

Block-level info is
 in the Visibility Map (VM)


(32k times smaller)

  • 1. Use tree
  • 2. Use doubly linked list
  • 3. Check visibility in VM
  • 4. Check visibility in Table
slide-41
SLIDE 41

CREATE INDEX … ON … ( k )

B-tree Index: Index Only Scan (since 9.2)

CREATE INDEX … ON … ( k, data ) Visibility information is only stored in table

VM

Block-level info is
 in the Visibility Map (VM)


(32k times smaller)

Index Only Scan using … on … Heap Fetches: 1

  • 1. Use tree
  • 2. Use doubly linked list
  • 3. Check visibility in VM
  • 4. Check visibility in Table
slide-42
SLIDE 42

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

VM

slide-43
SLIDE 43

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-44
SLIDE 44

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-45
SLIDE 45

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-46
SLIDE 46

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-47
SLIDE 47

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… CREATE INDEX … ON … ( k ) INCLUDE (data)

}

Not so deep

VM

slide-48
SLIDE 48

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-49
SLIDE 49

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-50
SLIDE 50

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-51
SLIDE 51

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-52
SLIDE 52

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-53
SLIDE 53

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT data FROM … WHERE k = $1 CREATE INDEX … ON … ( k ) INCLUDE (data)

VM

slide-54
SLIDE 54

INCLUDE — Pro and Con vs. Key-Columns

slide-55
SLIDE 55

Advantages:

INCLUDE — Pro and Con vs. Key-Columns

slide-56
SLIDE 56

Advantages:

  • Shallower: < ~40%

INCLUDE — Pro and Con vs. Key-Columns

slide-57
SLIDE 57

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns

slide-58
SLIDE 58

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns

Disadvantages:

  • …
slide-59
SLIDE 59

INCLUDE — Disadvantages: WHERE

SELECT * FROM … WHERE k = $1 AND data = $2

slide-60
SLIDE 60

QUERY PLAN

  • Index Scan using … on … (actual rows=1)

Index Cond: ((key = $1) AND (data = $2) Buffers: shared hit=5

CREATE INDEX … ON … ( k, data )

INCLUDE — Disadvantages: WHERE

SELECT * FROM … WHERE k = $1 AND data = $2

slide-61
SLIDE 61

QUERY PLAN

  • Index Scan using … on … (actual rows=1)

Index Cond: ((key = $1) AND (data = $2) Buffers: shared hit=5

CREATE INDEX … ON … ( k, data )

INCLUDE — Disadvantages: WHERE

SELECT * FROM … WHERE k = $1 AND data = $2

QUERY PLAN

  • Bitmap Heap Scan on … (actual rows=1)

Recheck Cond: (key = 123) Filter: (data = $2) Rows Removed by Filter: 9999 Heap Blocks: exact=10000 Buffers: shared hit=2186 read=7867

  • > Bitmap Index Scan on … (actual rows=10000)

Index Cond: (key = 123) Buffers: shared read=53

CREATE INDEX … ON … ( k ) INCLUDE ( data )

slide-62
SLIDE 62

QUERY PLAN

  • Index Scan using … on … (actual rows=1)

Index Cond: ((key = $1) AND (data = $2) Buffers: shared hit=5

CREATE INDEX … ON … ( k, data )

INCLUDE — Disadvantages: WHERE

SELECT * FROM … WHERE k = $1 AND data = $2

QUERY PLAN

  • Bitmap Heap Scan on … (actual rows=1)

Recheck Cond: (key = 123) Filter: (data = $2) Rows Removed by Filter: 9999 Heap Blocks: exact=10000 Buffers: shared hit=2186 read=7867

  • > Bitmap Index Scan on … (actual rows=10000)

Index Cond: (key = 123) Buffers: shared read=53

CREATE INDEX … ON … ( k ) INCLUDE ( data ) Doesn’t Filter on INCLUDE
 column

slide-63
SLIDE 63

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … ON … ( k ) INCLUDE (data)

slide-64
SLIDE 64

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … ON … ( k ) INCLUDE (data)

slide-65
SLIDE 65

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … ON … ( k ) INCLUDE (data)

slide-66
SLIDE 66

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … ON … ( k ) INCLUDE (data)

slide-67
SLIDE 67

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … ON … ( k ) INCLUDE (data) Ignored

(unknown if visible)

slide-68
SLIDE 68

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

…

K E Y

I
 N C L U D E

K E Y

I
 N C L U D E

K E Y


 N C

Table
 (Heap)

B-tree Index: INCLUDE (since 11)

K E Y K E Y K E Y K E Y K E Y K E Y K E Y K E Y

… SELECT * FROM … WHERE k = $1 AND data = $2 CREATE INDEX … ON … ( k ) INCLUDE (data) Ignored

(unknown if visible)

Table access
 not reduced

slide-69
SLIDE 69

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
slide-70
SLIDE 70

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

O p e r a t i o n s

slide-71
SLIDE 71

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

Safe


(in b-tree key: <=, <, =, >, >=)

O p e r a t i o n s

slide-72
SLIDE 72

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

Safe


(in b-tree key: <=, <, =, >, >=)

Other
 O p e r a t i o n s

slide-73
SLIDE 73

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

Safe


(in b-tree key: <=, <, =, >, >=)

Other
 O p e r a t i o n s C o l u m n s

slide-74
SLIDE 74

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

Safe


(in b-tree key: <=, <, =, >, >=)

Other
 Key O p e r a t i o n s C o l u m n s

slide-75
SLIDE 75

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

Safe


(in b-tree key: <=, <, =, >, >=)

Other
 Key O p e r a t i o n s C o l u m n s

✓

slide-76
SLIDE 76

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

Safe


(in b-tree key: <=, <, =, >, >=)

Other
 Key O p e r a t i o n s C o l u m n s

✓ ✗

slide-77
SLIDE 77

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

Safe


(in b-tree key: <=, <, =, >, >=)

Other
 Key INCLUDE O p e r a t i o n s C o l u m n s

✓ ✗

slide-78
SLIDE 78

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

Safe


(in b-tree key: <=, <, =, >, >=)

Other
 Key INCLUDE O p e r a t i o n s C o l u m n s

✓ ✗ ✗

slide-79
SLIDE 79

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

Safe


(in b-tree key: <=, <, =, >, >=)

Other
 Key INCLUDE O p e r a t i o n s C o l u m n s

✓ ✗ ✗

Not applicable:
 INCLUDE columns
 have no op class

slide-80
SLIDE 80

INCLUDE — Disadvantages: WHERE

SELECT data FROM … WHERE k = $1 AND data = $2

slide-81
SLIDE 81

INCLUDE — Disadvantages: WHERE

SELECT data FROM … WHERE k = $1 AND data = $2

Allow Index Only Scan

slide-82
SLIDE 82

INCLUDE — Disadvantages: WHERE

SELECT data FROM … WHERE k = $1 AND data = $2

Safe in B-Tree Key

slide-83
SLIDE 83

QUERY PLAN

  • Index Only Scan using … on … (actual rows=1)

Index Cond: (key = $1) AND (data =$2) Heap Fetches: 0 Buffers: shared hit=5

CREATE INDEX … ON … ( k, data )

INCLUDE — Disadvantages: WHERE

SELECT data FROM … WHERE k = $1 AND data = $2

Safe in B-Tree Key

slide-84
SLIDE 84

QUERY PLAN

  • Index Only Scan using … on … (actual rows=1)

Index Cond: (key = $1) AND (data =$2) Heap Fetches: 0 Buffers: shared hit=5

CREATE INDEX … ON … ( k, data )

INCLUDE — Disadvantages: WHERE

SELECT data FROM … WHERE k = $1 AND data = $2

QUERY PLAN

  • Index Only Scan using … on … (actual rows=1)

Index Cond: (key = $1) Filter: (data = $2) Rows Removed by Filter: 9999 Heap Fetches: 0 Buffers: shared hit=56

CREATE INDEX … ON … ( k ) INCLUDE ( data )

slide-85
SLIDE 85

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns

Disadvantages:

slide-86
SLIDE 86

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns

Disadvantages:

  • No safe WHERE conditions


(visibility always checked first)

slide-87
SLIDE 87

INCLUDE — Disadvantages: ORDER BY

SELECT * FROM … WHERE k = $1 ORDER BY data LIMIT 1

slide-88
SLIDE 88

QUERY PLAN

  • Limit (actual rows=1)

Buffers: shared hit=5

  • > Index Scan using … (actual rows=1)

Index Cond: (key = 123) Heap Fetches: 0 Buffers: shared hit=5

CREATE INDEX … ON … ( k, data )

INCLUDE — Disadvantages: ORDER BY

SELECT * FROM … WHERE k = $1 ORDER BY data LIMIT 1

slide-89
SLIDE 89

QUERY PLAN

  • Limit (actual rows=1)

Buffers: shared hit=5

  • > Index Scan using … (actual rows=1)

Index Cond: (key = 123) Heap Fetches: 0 Buffers: shared hit=5

CREATE INDEX … ON … ( k, data )

INCLUDE — Disadvantages: ORDER BY

SELECT * FROM … WHERE k = $1 ORDER BY data LIMIT 1 No Sort

slide-90
SLIDE 90

QUERY PLAN

  • Limit (actual rows=1)

Buffers: shared hit=5

  • > Index Scan using … (actual rows=1)

Index Cond: (key = 123) Heap Fetches: 0 Buffers: shared hit=5

CREATE INDEX … ON … ( k, data )

INCLUDE — Disadvantages: ORDER BY

SELECT * FROM … WHERE k = $1 ORDER BY data LIMIT 1

QUERY PLAN

  • Limit (actual rows=1)
  • > Sort (actual rows=1)
  • > Bitmap Heap Scan on…(actual rows=10000)

Recheck Cond: (key = 123) Heap Blocks: exact=10000 Buffers: shared hit=10053

  • > Bitmap Index Scan on…(act rows=10000)

Index Cond: (key = 123) Buffers: shared hit=53

CREATE INDEX … ON … ( k ) INCLUDE ( data ) No Sort

slide-91
SLIDE 91

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns

Disadvantages:

  • Doesn’t help WHERE


(except Index Only Scan)

slide-92
SLIDE 92

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns

Disadvantages:

  • Doesn’t help WHERE


(except Index Only Scan)

  • Cannot replace sorting


(ORDER BY)

slide-93
SLIDE 93

INCLUDE — Differences: Constraints

slide-94
SLIDE 94

ALTER TABLE … ADD PRIMARY KEY|UNIQUE
 (key, data)

INCLUDE — Differences: Constraints

slide-95
SLIDE 95

ALTER TABLE … ADD PRIMARY KEY|UNIQUE
 (key, data)

INCLUDE — Differences: Constraints

INSERT INTO … VALUES (1, 1) , (1, 2)✓

slide-96
SLIDE 96

ALTER TABLE … ADD PRIMARY KEY|UNIQUE
 (key, data)

INCLUDE — Differences: Constraints

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

slide-97
SLIDE 97

ALTER TABLE … ADD PRIMARY KEY|UNIQUE
 (key, data)

INCLUDE — Differences: Constraints

ALTER TABLE … ADD PRIMARY KEY|UNIQUE
 (key) INCLUDE(data) INSERT INTO … VALUES (1, 1) , (1, 2) INSERT INTO … VALUES (1, 1) , (1, 2)

✓ ✗

slide-98
SLIDE 98

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns

Disadvantages:

  • Doesn’t help WHERE


(except Index Only Scan)

  • Cannot replace sorting


(ORDER BY) Differences: PRIMARY KEY / UNIQUE don’t take INCLUDE columns into account

slide-99
SLIDE 99

INCLUDE and the Three Powers Of B-tree Indexes

https://use-the-index-luke.com/

slide-100
SLIDE 100

INCLUDE and the Three Powers Of B-tree Indexes

  • Finding data quickly

https://use-the-index-luke.com/

slide-101
SLIDE 101

INCLUDE and the Three Powers Of B-tree Indexes

  • Finding data quickly

https://use-the-index-luke.com/

✗

slide-102
SLIDE 102

INCLUDE and the Three Powers Of B-tree Indexes

  • Finding data quickly
  • Clustering data


(Index Only Scan)

https://use-the-index-luke.com/

✗

slide-103
SLIDE 103

INCLUDE and the Three Powers Of B-tree Indexes

  • Finding data quickly
  • Clustering data


(Index Only Scan)

https://use-the-index-luke.com/

✗ ✓

slide-104
SLIDE 104

INCLUDE and the Three Powers Of B-tree Indexes

  • Finding data quickly
  • Clustering data


(Index Only Scan)

  • Sorting data

https://use-the-index-luke.com/

✗ ✓

slide-105
SLIDE 105

INCLUDE and the Three Powers Of B-tree Indexes

  • Finding data quickly
  • Clustering data


(Index Only Scan)

  • Sorting data

https://use-the-index-luke.com/

✗ ✗ ✓

slide-106
SLIDE 106

INCLUDE — Advantage: Documentation

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key, data)

slide-107
SLIDE 107

INCLUDE — Advantage: Documentation

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key, data) SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

slide-108
SLIDE 108

INCLUDE — Advantage: Documentation

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key, data) SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

slide-109
SLIDE 109

INCLUDE — Advantage: Documentation

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key, data) SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

  • Add TS as last column


⇒ Not useful for ORDER BY/LIMIT

slide-110
SLIDE 110

INCLUDE — Advantage: Documentation

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key, data) SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

  • Add TS as last column


⇒ Not useful for ORDER BY/LIMIT

  • Add TS after KEY


⇒ Might break other queries

slide-111
SLIDE 111

INCLUDE — Advantage: Documentation

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key, data) SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

  • Add TS as last column


⇒ Not useful for ORDER BY/LIMIT

  • Add TS after KEY


⇒ Might break other queries WHERE key = $1 AND data = $2

slide-112
SLIDE 112

INCLUDE — Advantage: Documentation

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key, data) SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

  • Add TS as last column


⇒ Not useful for ORDER BY/LIMIT

  • Add TS after KEY


⇒ Might break other queries WHERE key = $1 AND data = $2 WHERE key = $1 ORDER BY data LIMIT 1

slide-113
SLIDE 113

INCLUDE — Advantage: Documentation

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key, data) SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

  • Add TS as last column


⇒ Not useful for ORDER BY/LIMIT

  • Add TS after KEY


⇒ Might break other queries

  • Create new index (key, ts, data)


⇒ Adds considerable overhead WHERE key = $1 AND data = $2 WHERE key = $1 ORDER BY data LIMIT 1

slide-114
SLIDE 114

INCLUDE — Advantage: Documentation

SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

  • Add TS as last column


⇒ Not useful for ORDER BY/LIMIT

  • Add TS after KEY


⇒ Might break other queries

  • Create new index (key, ts, data)


⇒ Adds considerable overhead

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key) INCLUDE(data)

WHERE key = $1 AND data = $2 WHERE key = $1 ORDER BY data LIMIT 1

slide-115
SLIDE 115

INCLUDE — Advantage: Documentation

SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

  • Add TS as last column


⇒ Not useful for ORDER BY/LIMIT

  • Add TS after KEY


⇒ Might break other queries

  • Create new index (key, ts, data)


⇒ Adds considerable overhead

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key) INCLUDE(data)

slide-116
SLIDE 116

INCLUDE — Advantage: Documentation

SELECT data FROM … WHERE key = $1 ORDER BY ts DESC LIMIT 1

Three options:

  • Add TS as last column


⇒ Not useful for ORDER BY/LIMIT

  • Add TS after KEY


⇒ Might break other queries

  • Create new index (key, ts, data)


⇒ Adds considerable overhead

postgres=# \d …

… [ columns skipped ] …

Indexes: "…" btree (key) INCLUDE(data) Safe option

slide-117
SLIDE 117

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%

INCLUDE — Pro and Con vs. Key-Columns

Disadvantages:

  • Doesn’t help WHERE


(except Index Only Scan)

  • Cannot replace sorting


(ORDER BY) Differences: PRIMARY KEY / UNIQUE don’t take INCLUDE columns into account

slide-118
SLIDE 118

Advantages:

  • Shallower: < ~40%
  • Slightly smaller: < ~3%
  • Documents its purpose

INCLUDE — Pro and Con vs. Key-Columns

Disadvantages:

  • Doesn’t help WHERE


(except Index Only Scan)

  • Cannot replace sorting


(ORDER BY) Differences: PRIMARY KEY / UNIQUE don’t take INCLUDE columns into account

slide-119
SLIDE 119

Concluding on INCLUDE

INCLUDE columns are ignored except by Index Only Scan

slide-120
SLIDE 120

Concluding on INCLUDE

INCLUDE columns are ignored except by Index Only Scan

  • Don’t move key columns to

INCLUDE unless you are *really* sure

slide-121
SLIDE 121

Concluding on INCLUDE

INCLUDE columns are ignored except by Index Only Scan

  • Don’t move key columns to

INCLUDE unless you are *really* sure

  • If you add columns to enable an

Index Only Scan, put them into INCLUDE

  • Also for PK/Unique!
slide-122
SLIDE 122

Concluding on INCLUDE

INCLUDE columns are ignored except by Index Only Scan

  • Don’t move key columns to

INCLUDE unless you are *really* sure

use-the-index-luke.com modern-sql.com winand.at

  • If you add columns to enable an

Index Only Scan, put them into INCLUDE

  • Also for PK/Unique!

Training in Vienna: March (German), September (English) 2020.
 Inhouse: whenever you want.

slide-123
SLIDE 123

use-the-index-luke.com modern-sql.com winand.at

Training in Vienna: March (German), September (English) 2020.
 Inhouse: whenever you want.

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

https://2019.pgconf.eu/f

https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes