On the optimization of recursive relational queries. DIG Seminar - PowerPoint PPT Presentation

On the optimization of recursive relational queries. DIG Seminar Louis Jachiet Centre de Recherche en Informatique, Signal et Automatique de Lille 1

The relational algebra

Generalities The relational algebra [Cod70] • a set of base relations the tables in SQL • combined through operators union, projection, filter, join, etc. • operates on named tuples 2

Syntax ϕ ::= term | X relation variable | | c → v | constant | ∅ empty set | ϕ 1 ∪ ϕ 2 union | ϕ 1 ⊲ ⊳ ϕ 2 join | ϕ 1 ⊲ ϕ 2 antijoin | σ filter ( ϕ ) filter ρ b | a ( ϕ ) rename | π P ( ϕ ) projection Figure 1: Syntax of the relational algebra 3

Examples T from to π to ( T ) to Lille Paris Lille Saclay Paris Paris Grenoble Saclay Paris Saclay Grenoble Saclay Grenoble 4

Examples ρ step T ( T ) to from to from step Lille Paris Lille Paris Lille Saclay Lille Saclay Paris Grenoble Paris Grenoble Paris Saclay Paris Saclay Saclay Grenoble Saclay Grenoble 4

Examples T ρ step ⊳ ρ step ( T ) ⊲ from ( T ) to from to from step to Lille Paris Lille Paris Saclay Lille Saclay Lille Paris Grenoble Paris Grenoble Lille Saclay Grenoble Paris Saclay Paris Saclay Grenoble Saclay Grenoble 4

Examples T from to σ from=Lille ( T ) Lille Paris from to Lille Saclay Lille Paris Paris Grenoble Lille Saclay Paris Saclay Saclay Grenoble 4

Examples T σ from=Paris ( T ) ∪ σ from=Lille ( T ) from to from to Lille Paris Lille Paris Lille Saclay Lille Saclay Paris Grenoble Paris Saclay Paris Saclay Paris Grenoble Saclay Grenoble 4

Examples T from to π to ( T ) ⊲ σ from=Lille ( T ) Lille Paris Lille Saclay to Paris Grenoble Grenoble Paris Saclay Saclay Grenoble 4

Recursive relational algebra

Syntax ϕ ::= term | relation variable X | | c → v | constant | ∅ empty set | ϕ 1 ∪ ϕ 2 union | ϕ 1 ⊲ ⊳ ϕ 2 join | ϕ 1 ⊲ ϕ 2 antijoin | σ filter ( ϕ ) filtering ρ b | a ( ϕ ) rename | π c 1 ,..., c n ( ϕ ) projection Figure 2: Syntax of our relational algebra 5

Syntax ϕ ::= term | relation variable X | | c → v | constant | ∅ empty set | ϕ 1 ∪ ϕ 2 union | ϕ 1 ⊲ ⊳ ϕ 2 join | ϕ 1 ⊲ ϕ 2 antijoin | σ filter ( ϕ ) filtering ρ b | a ( ϕ ) rename | ˜ π c ( ϕ ) anti-projection Figure 2: Syntax of our relational algebra 5

Syntax ϕ ::= term | relation variable X | | c → v | constant | ∅ empty set | ϕ 1 ∪ ϕ 2 union | ϕ 1 ⊲ ⊳ ϕ 2 join | ϕ 1 ⊲ ϕ 2 antijoin | σ filter ( ϕ ) filtering ρ b | a ( ϕ ) rename | ˜ π c ( ϕ ) anti-projection β b | a ( ϕ ) duplication Figure 2: Syntax of our relational algebra 5

Syntax ϕ ::= term | relation variable X | | c → v | constant | ∅ empty set | ϕ 1 ∪ ϕ 2 union | ϕ 1 ⊲ ⊳ ϕ 2 join | ϕ 1 ⊲ ϕ 2 antijoin | σ filter ( ϕ ) filtering ρ b | a ( ϕ ) rename | π c ( ϕ ) ˜ anti-projection β b | a ( ϕ ) duplication | µ ( X = ϕ ) fixpoint Figure 2: Syntax of our relational algebra 5

Differences Anti-projection Remove a column ˜ π d 1 ( . . . ˜ π d k ( ϕ ) . . . ) = π c 1 ... c n ( ϕ ) 6

Differences Anti-projection Remove a column ˜ π d 1 ( . . . ˜ π d k ( ϕ ) . . . ) = π c 1 ... c n ( ϕ ) Duplication Copy a column β b � ⊳ ρ b � a ( ϕ ) = σ a = b ϕ ⊲ a ( ϕ ) 6

Differences Anti-projection Remove a column ˜ π d 1 ( . . . ˜ π d k ( ϕ ) . . . ) = π c 1 ... c n ( ϕ ) Duplication Copy a column β b � ⊳ ρ b � a ( ϕ ) = σ a = b ϕ ⊲ a ( ϕ ) Fixpoints Compute the least fixpoint of a function S → ϕ [ X / S ] U 0 = ∅ � µ ( X = ϕ ) � V = lim n →∞ U n U n +1 = U n ∪ � ϕ � V [ X / U n ] 6

Examples T from to π from ( T ) ˜ to Lille Paris Lille Saclay Paris Paris Grenoble Saclay Paris Saclay Grenoble Saclay Grenoble 7

Examples T β to from to from (˜ π to ( T )) from to Lille Paris Lille Saclay Lille Lille Paris Grenoble Saclay Saclay Paris Saclay Paris Paris Saclay Grenoble 7

Examples T + = µ ( X = T ∪ T / X ) from to T from to Lille Paris Paris Saclay Saclay Lyon Lyon Grenoble 8

Examples T + = µ ( X = T ∪ ˜ π s ( ρ s ⊳ ρ s to ( X ) ⊲ from ( T ))) from to Lille Paris Paris Saclay T Saclay Lyon from to Lyon Grenoble Lille Paris Paris Saclay Saclay Lyon Lyon Grenoble 8

Examples T + = µ ( X = T ∪ ˜ π s ( ρ s ⊳ ρ s to ( X ) ⊲ from ( T ))) from to Lille Paris Paris Saclay T Saclay Lyon from to Lyon Grenoble Lille Paris Lille Saclay Paris Saclay Paris Lyon Saclay Lyon Saclay Grenoble Lyon Grenoble 8

Examples T + = µ ( X = T ∪ ˜ π s ( ρ s ⊳ ρ s to ( X ) ⊲ from ( T ))) from to Lille Paris Paris Saclay T Saclay Lyon from to Lyon Grenoble Lille Paris Lille Saclay Paris Saclay Paris Lyon Saclay Lyon Saclay Grenoble Lyon Grenoble Lille Lyon Paris Grenoble 8

Examples T + = µ ( X = T ∪ ˜ π s ( ρ s ⊳ ρ s to ( X ) ⊲ from ( T ))) from to Lille Paris Paris Saclay T Saclay Lyon from to Lyon Grenoble Lille Paris Lille Saclay Paris Saclay Paris Lyon Saclay Lyon Saclay Grenoble Lyon Grenoble Lille Lyon Paris Grenoble Lille Grenoble 8

Limitations Limitations on fixpoints • recursive variables must appear positively No µ ( X = R ⊲ X ) 9

Limitations Limitations on fixpoints • recursive variables must appear positively No µ ( X = R ⊲ X ) • no join between recursive terms No µ ( X = X ⋊ ⋉ X ) 9

Limitations Limitations on fixpoints • recursive variables must appear positively No µ ( X = R ⊲ X ) • no join between recursive terms No µ ( X = X ⋊ ⋉ X ) • no mutually recursive fixpoints No µ ( X = µ ( Y = X ∪ Y )) 9

Limitations Limitations on fixpoints • recursive variables must appear positively No µ ( X = R ⊲ X ) • no join between recursive terms No µ ( X = X ⋊ ⋉ X ) • no mutually recursive fixpoints No µ ( X = µ ( Y = X ∪ Y )) → corresponds to linear datalog! → superset of WITH RECURSIVE in SQL! 9

Performance of recursive queries

An example :Lille :TGV / :Bus ∗ ?o 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :Lille :TGV / :Bus ∗ ?o L 10

An example :L :TGV / :Bus ∗ ?o 11

An example :L :TGV / :Bus ∗ ?o π ? s ( σ ? s =: L (:TGV /µ ( X = β o ˜ s ( AllNodes ) ∪ X / :Bus))) 11

An example :L :TGV / :Bus ∗ ?o π ? s ( σ ? s =: L (:TGV /µ ( X = β o ˜ s ( AllNodes ) ∪ X / :Bus))) π ? s ( σ ? s =: L (:TGV /µ ( X = β o ˜ s ( AllNodes ) ∪ :Bus / X ))) 11

Benchmarking 10 1 Postgres SQLite Virtuoso 10 0 ARQ 1 Time (s) ARQ 2 DLV 1 DLV 2 10 − 1 Ramsdell 2 Ramsdell 1 Vlog 1 Vlog 2 10 − 2 10 2 10 3 10 4 10 5 10 6 number of nodes n 12 ∗

Benchmarking Logicblox • Materialization of all intermediate predicate • ... except for “on demand” predicate • therefore manual optimization 13

Rewrite rules Rewrite rules for fixpoints • pushing filters? σ filter ( µ ( X = ϕ )) ? = µ ( X = σ filter ( ϕ )) 14

Rewrite rules Rewrite rules for fixpoints • pushing filters? σ filter ( µ ( X = ϕ )) ? = µ ( X = σ filter ( ϕ )) • pushing joins? ⊳ µ ( X = ϕ ) ? ψ ⊲ = µ ( X = ψ ⊲ ⊳ ϕ ) 14

Rewrite rules Rewrite rules for fixpoints • pushing filters? σ filter ( µ ( X = ϕ )) ? = µ ( X = σ filter ( ϕ )) • pushing joins? ⊳ µ ( X = ϕ ) ? ψ ⊲ = µ ( X = ψ ⊲ ⊳ ϕ ) • pushing antijoins? µ ( X = ϕ ) ⊲ ψ ? = µ ( X = ϕ ⊲ ψ ) 14

Rewrite rules Rewrite rules for fixpoints • pushing filters? σ filter ( µ ( X = ϕ )) ? = µ ( X = σ filter ( ϕ )) • pushing joins? ⊳ µ ( X = ϕ ) ? ψ ⊲ = µ ( X = ψ ⊲ ⊳ ϕ ) • pushing antijoins? µ ( X = ϕ ) ⊲ ψ ? = µ ( X = ϕ ⊲ ψ ) • pushing anti-projections? π p ( µ ( X = ϕ )) ? ˜ = µ ( X = ˜ π p ( ϕ )) 14

Rewrite rules Rewrite rules for fixpoints • pushing filters? σ filter ( µ ( X = ϕ )) ? = µ ( X = σ filter ( ϕ )) • pushing joins? ⊳ µ ( X = ϕ ) ? ψ ⊲ = µ ( X = ψ ⊲ ⊳ ϕ ) • pushing antijoins? µ ( X = ϕ ) ⊲ ψ ? = µ ( X = ϕ ⊲ ψ ) • pushing anti-projections? π p ( µ ( X = ϕ )) ? ˜ = µ ( X = ˜ π p ( ϕ )) • combine fixpoints? ⊳ µ ( X = ϕ ∪ ξ ) ? µ ( X = ψ ∪ κ ) ⊲ = µ ( X = ψ ⊲ ⊳ ϕ ∪ ξ ∪ κ ) 14