Trees (Part 2) 1 / 59 Trees (Part 2) Recap Recap 2 / 59 Trees - - PowerPoint PPT Presentation

▶
trees part 2
SMART_READER_LITE
LIVE PREVIEW

Trees (Part 2) 1 / 59 Trees (Part 2) Recap Recap 2 / 59 Trees - - PowerPoint PPT Presentation

Dec 27, 2023 201 likes •819 views

Trees (Part 2) Trees (Part 2) 1 / 59 Trees (Part 2) Recap Recap 2 / 59 Trees (Part 2) Recap B + Tree A B + Tree is a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertions, and

slide-1
SLIDE 1

1 / 59

Trees (Part 2)

Trees (Part 2)

slide-2
SLIDE 2

2 / 59

Trees (Part 2) Recap

Recap

slide-3
SLIDE 3

3 / 59

Trees (Part 2) Recap

B+Tree

  • A B+Tree is a self-balancing tree data structure that keeps data sorted and allows

searches, sequential access, insertions, and deletions in O(log n).

▶ Generalization of a binary search tree in that a node can have more than two children. ▶ Optimized for disk storage (i.e., read and write at page-granularity).

slide-4
SLIDE 4

4 / 59

Trees (Part 2) Recap

B+Tree Properties

  • A B+Tree is an M-way search tree with the following properties:

▶ It is perfectly balanced (i.e., every leaf node is at the same depth). ▶ Every node other than the root, is at least half-full: M/2-1 <= keys <= M-1 ▶ Every inner node with k keys has k+1 non-null children (node pointers)

slide-5
SLIDE 5

5 / 59

Trees (Part 2) Recap

Today’s Agenda

  • More B+Trees
  • Additional Index Magic
  • Tries / Radix Trees
  • Inverted Indexes
slide-6
SLIDE 6

6 / 59

Trees (Part 2) More B+Trees

More B+Trees

slide-7
SLIDE 7

7 / 59

Trees (Part 2) More B+Trees

Duplicate Keys

  • Approach 1: Append Record Id

▶ Add the tuple’s unique record id as part of the key to ensure that all keys are unique. ▶ The DBMS can still use partial keys to find tuples.

  • Approach 2: Overflow Leaf Nodes

▶ Allow leaf nodes to spill into overflow nodes that contain the duplicate keys. ▶ This is more complex to maintain and modify.

slide-8
SLIDE 8

8 / 59

Trees (Part 2) More B+Trees

Append Record Id

slide-9
SLIDE 9

9 / 59

Trees (Part 2) More B+Trees

Append Record Id

slide-10
SLIDE 10

10 / 59

Trees (Part 2) More B+Trees

Append Record Id

slide-11
SLIDE 11

11 / 59

Trees (Part 2) More B+Trees

Append Record Id

slide-12
SLIDE 12

12 / 59

Trees (Part 2) More B+Trees

Duplicate Keys

  • Approach 1: Append Record Id

▶ Add the tuple’s unique record id as part of the key to ensure that all keys are unique. ▶ The DBMS can still use partial keys to find tuples.

  • Approach 2: Overflow Leaf Nodes

▶ Allow leaf nodes to spill into overflow nodes that contain the duplicate keys. ▶ This is more complex to maintain and modify.

slide-13
SLIDE 13

13 / 59

Trees (Part 2) More B+Trees

Overflow Leaf Nodes

slide-14
SLIDE 14

14 / 59

Trees (Part 2) More B+Trees

Overflow Leaf Nodes

slide-15
SLIDE 15

15 / 59

Trees (Part 2) More B+Trees

Partitioned B-Tree

Bulk operations are fine if they are rare, but they are disruptive

  • usually the B-tree has to be take offline
  • the new cannot be queries easily
  • existing queries must be halted
slide-16
SLIDE 16

16 / 59

Trees (Part 2) More B+Trees

Partitioned B-Tree

Basic idea: partition the B-tree

  • add an artificial column in front
  • creates separate partitions with the B-tree

Partition no. 0 3 4

slide-17
SLIDE 17

17 / 59

Trees (Part 2) More B+Trees

Partitioned B-Tree

Benefits:

  • partitions are largely independent of each other
  • one can append to the “rightmost” partition without disrupting the rest
  • the index stays always online
  • partitions can be merged lazily
  • merge only when beneficial

Drawbacks:

  • no “global” order any more
  • lookups have to access all partitions
slide-18
SLIDE 18

18 / 59

Trees (Part 2) More B+Trees

Prefix B+-tree

A B+-tree can contain separators that do not occur in the data We can use this to save space: aaaa bbbb eeee ffff bbbb aaaa bbbb eeee ffff c

  • choose the smallest possible separator
  • no change to the lookup logic is required
slide-19
SLIDE 19

19 / 59

Trees (Part 2) More B+Trees

Prefix B+-tree

We can do even better by factoring out a common prefix: http://www. google.com sigmod.org

  • only one prefix per page
  • the change to the lookup logic is minor
  • the lookup key itself is adjusted
  • sometimes only inner nodes, to keep scans cheap
slide-20
SLIDE 20

20 / 59

Trees (Part 2) More B+Trees

Prefix B+-tree

The lexicographic sort order makes prefix compression attractive:

  • neighboring entries tend to differ only at the end
  • a common prefix occurs very frequently
  • not only for strings, also for compound keys etc.
  • in particular important if partitioned B-trees
  • with big-endian ordering any value might get compressed
slide-21
SLIDE 21

21 / 59

Trees (Part 2) Additional Index Magic

Additional Index Magic

slide-22
SLIDE 22

22 / 59

Trees (Part 2) Additional Index Magic

Implicit Indexes

  • Most DBMSs automatically create an index to enforce integrity constraints.

▶ Primary Keys ▶ Unique Constraints

CREATE TABLE foo ( id SERIAL PRIMARY KEY, val1 INT NOT NULL, val2 VARCHAR(32) UNIQUE ); CREATE UNIQUE INDEX foo_pkey ON foo (id); CREATE UNIQUE INDEX foo_val2_key ON foo (val2);

slide-23
SLIDE 23

23 / 59

Trees (Part 2) Additional Index Magic

Implicit Indexes

  • But, this is not done for referential integrity constraints (i.e., foreign keys).

CREATE TABLE bar ( id INT REFERENCES foo (val1), val VARCHAR(32) ); CREATE INDEX foo_val1_key ON foo (val1); -- Not automatically done

slide-24
SLIDE 24

24 / 59

Trees (Part 2) Additional Index Magic

Partial Indexes

  • Create an index on a subset of the entire table.
  • This potentially reduces its size and the amount of overhead to maintain it.
  • One common use case is to partition indexes by date ranges.

▶ Create a separate index per month, year.

CREATE INDEX idx_foo ON foo (a, b) WHERE c = 'October'; SELECT b FROM foo WHERE a = 123 AND c = 'October';

slide-25
SLIDE 25

25 / 59

Trees (Part 2) Additional Index Magic

Covering Indexes

  • If all the fields needed to process the query are available in an index, then the DBMS

does not need to retrieve the tuple from the heap.

  • This reduces contention on the DBMS’s buffer pool resources.

CREATE INDEX idx_foo ON foo (a, b); SELECT b FROM foo WHERE a = 123;

slide-26
SLIDE 26

26 / 59

Trees (Part 2) Additional Index Magic

Index Include Columns

  • Embed additional columns in indexes to support index-only queries.
  • These extra columns are only stored in the leaf nodes and are not part of the search key.

CREATE INDEX idx_foo ON foo (a, b) INCLUDE (c); SELECT b FROM foo WHERE a = 123 AND c = 'October';

slide-27
SLIDE 27

27 / 59

Trees (Part 2) Additional Index Magic

Functional/Expression Indexes

  • An index does not need to store keys in the same way that they appear in their base

table.

  • You can use functions/expressions when declaring an index.

SELECT * FROM users WHERE EXTRACT(dow FROM login) = 2; CREATE INDEX idx_user_login ON users (login);

slide-28
SLIDE 28

28 / 59

Trees (Part 2) Additional Index Magic

Functional/Expression Indexes

  • An index does not need to store keys in the same way that they appear in their base

table.

  • You can use functions/expressions when declaring an index.

CREATE INDEX idx_user_login ON users (EXTRACT(dow FROM login)); CREATE INDEX idx_user_login ON foo (login) WHERE EXTRACT(dow FROM login) = 2;

slide-29
SLIDE 29

29 / 59

Trees (Part 2) Tries / Radix Trees

Tries / Radix Trees

slide-30
SLIDE 30

30 / 59

Trees (Part 2) Tries / Radix Trees

Observation

  • The inner node keys in a B+Tree cannot tell you whether a key exists in the index.
  • You must always traverse to the leaf node.
  • This means that you could have (at least) one buffer pool page miss per level in the tree

just to find out a key does not exist.

slide-31
SLIDE 31

31 / 59

Trees (Part 2) Tries / Radix Trees

Trie Index

  • Use a digital representation of keys to

examine prefixes one-by-one instead of comparing entire key.

▶ a.k.a., Digital Search Tree, Prefix Tree.

slide-32
SLIDE 32

32 / 59

Trees (Part 2) Tries / Radix Trees

Properties

  • Shape only depends on key space and lengths.

▶ Does not depend on existing keys or insertion order. ▶ Does not require rebalancing operations.

  • All operations have O(k) complexity where k is the length of the key.

▶ The path to a leaf node represents the key of the leaf ▶ Keys are stored implicitly and can be reconstructed from paths.

slide-33
SLIDE 33

33 / 59

Trees (Part 2) Tries / Radix Trees

Key Span

  • The span of a trie level is the number of bits that each partial key / digit represents.

▶ If the digit exists in the corpus, then store a pointer to the next level in the trie branch. ▶ Otherwise, store null.

  • This determines the fan-out of each node and the physical height of the tree.
slide-34
SLIDE 34

34 / 59

Trees (Part 2) Tries / Radix Trees

Key Span

slide-35
SLIDE 35

35 / 59

Trees (Part 2) Tries / Radix Trees

Key Span

slide-36
SLIDE 36

36 / 59

Trees (Part 2) Tries / Radix Trees

Key Span

slide-37
SLIDE 37

37 / 59

Trees (Part 2) Tries / Radix Trees

Key Span

slide-38
SLIDE 38

38 / 59

Trees (Part 2) Tries / Radix Trees

Key Span

slide-39
SLIDE 39

39 / 59

Trees (Part 2) Tries / Radix Trees

Key Span

slide-40
SLIDE 40

40 / 59

Trees (Part 2) Tries / Radix Trees

Key Span

slide-41
SLIDE 41

41 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree

  • Omit all nodes with only a single child.

▶ a.k.a., Patricia Tree.

  • Can produce false positives
  • So the DBMS always checks the
  • riginal tuple to see whether a key

matches.

slide-42
SLIDE 42

42 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Modifications

slide-43
SLIDE 43

43 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Modifications

slide-44
SLIDE 44

44 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Modifications

slide-45
SLIDE 45

45 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Modifications

slide-46
SLIDE 46

46 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Modifications

slide-47
SLIDE 47

47 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Modifications

slide-48
SLIDE 48

48 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Binary Comparable Keys

  • Not all attribute types can be decomposed into binary comparable digits for a radix

tree.

▶ Unsigned Integers: Byte order must be flipped for little endian machines. ▶ Signed Integers: Flip two’s-complement so that negative numbers are smaller than positive. ▶ Floats: Classify into group (neg vs. pos, normalized vs. denormalized), then store as unsigned integer. ▶ Compound: Transform each attribute separately.

slide-49
SLIDE 49

49 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Binary Comparable Keys

slide-50
SLIDE 50

50 / 59

Trees (Part 2) Tries / Radix Trees

Radix Tree: Binary Comparable Keys

slide-51
SLIDE 51

51 / 59

Trees (Part 2) Inverted Index

Inverted Index

slide-52
SLIDE 52

52 / 59

Trees (Part 2) Inverted Index

Observation

  • The tree indexes that we’ve discussed so far are useful for "point" and "range" queries:

▶ Find all customers in the 30308 zip code. ▶ Find all orders between June 2020 and September 2020.

  • They are not good at keyword searches:

▶ Find all Wikipedia articles that contain the word "Trie"

slide-53
SLIDE 53

53 / 59

Trees (Part 2) Inverted Index

Wikipedia Example

CREATE TABLE pages ( userID INT PRIMARY KEY, userName VARCHAR UNIQUE, ); CREATE TABLE pages ( pageID INT PRIMARY KEY, title VARCHAR UNIQUE, latest INT REFERENCES revisions (revID), ); CREATE TABLE revisions ( revID INT PRIMARY KEY, userID INT REFERENCES useracct (userID), pageID INT REFERENCES pages (pageID), content TEXT,

  • - Text Search

updated DATETIME );

slide-54
SLIDE 54

54 / 59

Trees (Part 2) Inverted Index

Wikipedia Example

  • If we create an index on the content attribute, what does that do?
  • This doesn’t help our query.
  • Our query is also not correct since it will return any occurrence (not only exact matches)

CREATE INDEX idx_rev_content ON revisions (content); SELECT pageID FROM revisions WHERE content LIKE '%Trie%';

slide-55
SLIDE 55

55 / 59

Trees (Part 2) Inverted Index

Inverted Index

  • An inverted index stores a mapping of words to records that contain those words in

the target attribute.

▶ Sometimes called a full-text search index. ▶ Also called a concordance in old (like really old) times.

  • Major DBMSs support these natively (e.g., PostgreSQL Generalized Inverted Index

(GIN))

  • There are also specialized DBMSs (e.g., Lucene, Elasticsearch)
slide-56
SLIDE 56

56 / 59

Trees (Part 2) Inverted Index

Query Types

  • Phrase Searches

▶ Find records that contain a list of words in the given order.

  • Proximity Searches

▶ Find records where two words occur within n words of each other.

  • Wildcard Searches

▶ Find records that contain words that match some pattern (e.g., regular expression).

slide-57
SLIDE 57

57 / 59

Trees (Part 2) Inverted Index

Design Decisions

  • Decision 1: What To Store

▶ The index needs to store at least the words contained in each record (separated by punctuation characters). ▶ Can also store frequency, position, and other meta-data.

  • Decision 2: When To Update

▶ Maintain auxiliary data structures to "stage" updates and then update the index in batches.

slide-58
SLIDE 58

58 / 59

Trees (Part 2) Conclusion

Conclusion

slide-59
SLIDE 59

59 / 59

Trees (Part 2) Conclusion

Conclusion

  • B+Trees are still the way to go for tree indexes.
  • Next Class

▶ How to make indexes thread-safe!