Optimal Join Algorithms meet Top- Nikolaos Tziavelis, Wolfgang - PowerPoint PPT Presentation

SIGMOD 2020 tutorial Optimal Join Algorithms meet Top- 𝑙 Nikolaos Tziavelis, Wolfgang Gatterbauer, Mirek Riedewald Ranked results Northeastern University, Boston Part 2 : Optimal Join Algorithms Time Slides: https://northeastern-datalab.github.io/topk-join-tutorial/ DOI: https://doi.org/10.1145/3318464.3383132 Data Lab: https://db.khoury.northeastern.edu This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 4.0 International License. See https://creativecommons.org/licenses/by-nc-sa/4.0/for details 1

Outline tutorial • Part 1: Top- 𝑙 (Wolfgang): ~20min • Part 2: Optimal Join Algorithms (Mirek): ~30min – Lower Bound and the Yannakakis Algorithm – Problems Caused by Cycles – Tree Decompositions – Summary and Further Reading • Part 3: Ranked enumeration over joins (Nikolaos): ~40min 2

Basic Terminology and Assumptions • Terminology - Full conjunctive query (CQ) • Natural join of 𝑚 relations with O(𝑜) tuples each • E.g.: 𝑅 𝐵 1 , 𝐵 2 , 𝐵 3 , 𝐵 4 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 • Any selections comparing attributes to constants, e.g., 𝐵 4 < 1 - Query size: O(𝑚) - Output cardinality: 𝑠 • Assumptions - No pre-computed data structures such as indexes, sorted representation, materialized views 3

Complexity Notation • Standard O and Ω notation for time and memory complexity in the RAM model of computation • Common practice: focus on data complexity - We care about scalability in data size • Treat query size 𝑚 as a constant - E.g., O 𝑔 𝑚 ⋅ 𝑜 𝑔(𝑚) + log 𝑜 𝑔(𝑚) ⋅ 𝑠 simplifies to O 𝑜 𝑔(𝑚) + log 𝑜 𝑔(𝑚) ⋅ 𝑠 4

Complexity Notation • Standard O and Ω notation for time and memory complexity in the RAM model of computation • Common practice: focus on data complexity - We care about scalability in data size • Treat query size 𝑚 as a constant - E.g., O 𝑔 𝑚 ⋅ 𝑜 𝑔(𝑚) + log 𝑜 𝑔(𝑚) ⋅ 𝑠 simplifies to O 𝑜 𝑔(𝑚) + log 𝑜 𝑔(𝑚) ⋅ 𝑠 • We mostly use ෩ O -notation (soft-O) data complexity - Abstracts away polylog factors in input size that clutter formulas - E.g., O 𝑜 𝑔(𝑚) + log𝑜 𝑔(𝑚) ⋅ 𝑠 further simplifies to ෩ O 𝑜 𝑔(𝑚) + 𝑠 5

Outline tutorial • Part 1: Top- 𝑙 (Wolfgang): ~20min • Part 2: Optimal Join Algorithms (Mirek): ~30min – Lower Bound and the Yannakakis Algorithm – Problems Caused by Cycles – Tree Decompositions – Summary and Further Reading • Part 3: Ranked enumeration over joins (Nikolaos): ~40min 6

Lower Bound for Any Query • Need to read entire input at least once: Ω(𝑚𝑜) - Ω(𝑜) data complexity • Need to output every result, each of size 𝑚 : Ω(𝑚𝑠) - Ω(𝑠) data complexity • Together: Ω(𝑜 + 𝑠) time complexity to compute any CQ • Amazingly, the Yannakakis algorithm essentially matches the lower bound for acyclic CQs - Time complexity ෩ O 𝑜 + 𝑠 7

Yannakakis Algorithm • Given: acyclic conjunctive query Q as a rooted join tree • Step 1: semi-join reduction (two sweeps) - Semi-join reduction sweep from the leaves to root - Semi-join reduction sweep from root to the leaves • Step 2: use the join tree as the query plan - Compute the joins bottom up, with early projections [Mihalis Yannakakis. Algorithms for acyclic database schemes. VLDB’81] https://dl.acm.org/doi/10.5555/1286831.1286840 8

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 4 60 𝐵 1 , 𝐵 2 𝑆 2 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 1 20 100 3 10 300 1 40 300 2 30 200 𝐵 2 𝐵 1 , 𝐵 2 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 30 1 20 2000 2 20 2000 9

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 4 60 𝐵 1 , 𝐵 2 Bottom-up traversal (semi-joins) 𝑆 2 1. 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 𝑺 𝟑 ⋉ 𝑺 𝟓 1 20 100 3 10 300 1 40 300 2 30 200 𝐵 2 𝐵 1 , 𝐵 2 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 30 1 20 2000 2 20 2000 10

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 4 60 𝐵 1 , 𝐵 2 Bottom-up traversal (semi-joins) 𝑆 2 1. 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 𝑺 𝟑 ⋉ 𝑺 𝟓 1 20 100 3 10 300 𝑩 𝟐 𝑩 𝟑 1 40 300 2 30 200 1 10 1 20 𝐵 2 2 20 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 30 1 20 2000 2 20 2000 11

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 4 60 𝐵 1 , 𝐵 2 Bottom-up traversal (semi-joins) 𝑆 2 1. 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 𝑺 𝟑 ⋉ 𝑺 𝟓 1 20 100 3 10 300 𝑩 𝟐 𝑩 𝟑 1 40 300 2 30 200 1 10 1 20 𝐵 2 2 20 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 30 1 20 2000 2 20 2000 12

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 4 60 𝐵 1 , 𝐵 2 Bottom-up traversal (semi-joins) 𝑆 2 1. 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 𝑺 𝟑 ⋉ 𝑺 𝟒 𝑺 𝟑 ⋉ 𝑺 𝟓 1 20 100 3 10 300 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 1 40 300 2 30 200 10 1 10 20 1 20 30 2 20 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 30 1 20 2000 2 20 2000 13

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 𝑺 𝟐 ⋉ 𝑺 𝟑 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 4 60 𝑩 𝟐 𝑩 𝟑 1 10 1 20 Bottom-up traversal (semi-joins) 𝑆 2 1. 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 𝑺 𝟑 ⋉ 𝑺 𝟒 𝑺 𝟑 ⋉ 𝑺 𝟓 1 20 100 3 10 300 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 1 40 300 2 30 200 10 1 10 20 1 20 30 2 20 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 30 1 20 2000 2 20 2000 14

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 4 60 𝑺 𝟑 ⋉ 𝑺 𝟐 Bottom-up traversal (semi-joins) 𝑆 2 1. 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 Top-down traversal (semi-joins) 2. 1 20 100 3 10 300 1 40 300 2 30 200 𝑺 𝟒 ⋉ 𝑺 𝟑 𝑺 𝟓 ⋉ 𝑺 𝟑 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 30 1 20 2000 2 20 2000 15

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 Bottom-up traversal (semi-joins) 𝑆 2 1. 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 Top-down traversal (semi-joins) 2. 1 20 100 Join bottom-up 3. 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 1 20 2000 16

Yannakakis Algorithm – Example 𝑆 1 𝑩 𝟐 𝑩 𝟑 Database Theory 1 20 1 10 𝑅 = 𝑆 1 𝐵 1 , 𝐵 2 ⋈ 𝑆 2 𝐵 1 , 𝐵 2 , 𝐵 3 ⋈ 𝑆 3 𝐵 2 ⋈ 𝑆 4 𝐵 1 , 𝐵 2 , 𝐵 4 Bottom-up traversal (semi-joins) 𝑆 2 1. 𝑩 𝟐 𝑩 𝟑 𝑩 𝟒 1 10 100 Top-down traversal (semi-joins) 2. 1 20 100 Join bottom-up 3. In each join step, each left input tuple joins with at least 1 right input tuple, and vice versa! 𝑆 3 𝑆 4 𝑩 𝟑 𝑩 𝟐 𝑩 𝟑 𝑩 𝟓 10 1 10 1000 20 1 20 1000 1 20 2000 17

Yannakakis Algorithm – Properties • Semi-join sweeps take ෩ O 𝑜 • A join step can never shrink intermediate result size - This does not hold for all trees - Tree must be attribute-connected (more on this soon) • Hence all intermediate results are of size O 𝑠 • Each join step therefore has O 𝑜 + 𝑠 input and O 𝑠 output • Easy to compute a binary join with O 𝑜 + 𝑠 input and O 𝑠 output in time ෩ O 𝑜 + 𝑠 , e.g., using sort-merge join 18

Outline tutorial • Part 1: Top- 𝑙 (Wolfgang): ~20min • Part 2: Optimal Join Algorithms (Mirek): ~30min – Lower Bound and the Yannakakis Algorithm – Problems Caused by Cycles – Tree Decompositions – Summary and Further Reading • Part 3: Ranked enumeration over joins (Nikolaos): ~40min 19

CQs with Cycles • 3-path: 𝑅 3𝑞 = 𝑆 1 (𝐵 1 , 𝐵 2 ) ⋈ 𝑆 2 (𝐵 2 , 𝐵 3 ) ⋈ 𝑆 3 (𝐵 3 , 𝐵 4 ) • 3-cycle: 𝑅 3𝑑 = 𝑆 1 (𝐵 1 , 𝐵 2 ) ⋈ 𝑆 2 (𝐵 2 , 𝐵 3 ) ⋈ 𝑆 3 (𝐵 3 , 𝐵 1 ) 𝑅 3𝑞 𝑅 3𝑑 𝐵 3 𝐵 3 𝐵 2 𝐵 1 𝐵 2 𝐵 3 𝐵 1 𝐵 2 𝐵 3 𝐵 1 𝐵 2 𝐵 4 20