Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Deep dive in a SQL query (PgIbz)

Jesús Espino
September 10, 2024
Programming
2
73

Deep dive in a SQL query (PgIbz)

Jesús Espino

September 10, 2024
Tweet

More Decks by Jesús Espino

See All by Jesús Espino
Deep dive into a go binary
jespino
0
64
Understanding the go runtime
jespino
0
80
Tests end-to-end en entornos complejos con testcontainers
jespino
0
20
Dissecting Slices, Maps and Channels in Go
jespino
0
43
Understanding the memory model of 5 languages
jespino
0
100
The Javascript Toolset
jespino
1
74
Deep dive in a SQL query
jespino
0
79
10 Aha! Moments reading the go source code
jespino
0
95
The secret life of a goroutine (Fosdem 2024)
jespino
0
120

Other Decks in Programming

See All in Programming
Fibonacci Function Gallery - Part 1
philipschwarz
PRO
0
210
Cloudflare MCP ServerでClaude Desktop からWeb APIを構築
kutakutat
1
540
From Translations to Multi Dimension Entities
alexanderschranz
2
130
Beyond ORM
77web
3
380
フロントエンドのディレクトリ構成どうしてる？　Feature-Sliced Design 導入体験談
osakatechlab
8
4.1k
複雑な仕様に立ち向かうアーキテクチャ
myohei
0
170
命名をリントする
chiroruxx
1
390
HTTP compression in PHP and Symfony apps
dunglas
2
1.7k
創造的活動から切り拓く新たなキャリア 好きから始めてみる夜勤オペレーターからSREへの転身
yjszk
1
130
StarlingMonkeyを触ってみた話 - 2024冬
syumai
3
270
良いユニットテストを書こう
mototakatsu
5
2k
Jakarta EE meets AI
ivargrimstad
0
240

Featured

See All Featured
Why You Should Never Use an ORM
jnunemaker
PRO
54
9.1k
VelocityConf: Rendering Performance Case Studies
addyosmani
326
24k
Into the Great Unknown - MozCon
thekraken
33
1.5k
Fantastic passwords and where to find them - at NoRuKo
philnash
50
2.9k
Fontdeck: Realign not Redesign
paulrobertlloyd
82
5.3k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
229
52k
Large-scale JavaScript Application Architecture
addyosmani
510
110k
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
0
97
Put a Button on it: Removing Barriers to Going Fast.
kastner
59
3.6k
Designing on Purpose - Digital PM Summit 2013
jponch
116
7k
Bootstrapping a Software Product
garrettdimon
PRO
305
110k
Speed Design
sergeychernyshev
25
670

Transcript

  1. Deep dive in a SQL query

  2. Jesús Espino About me Principal Engineer Full Stack Developer

  3. Introduction

  4. Disclaimer

  5. Postgres 16.2

  6. The Parser src/backend/parser/

  7. ( {SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name

    <> :indirection <> :val {COLUMNREF :fields ( {A_STAR} ) } } ) :fromClause ( {RANGEVAR :schemaname <> :relname foo :relpersistence p :alias <> } ) :whereClause {AEXPR :name ("=") :lexpr {COLUMNREF :fields ("bar") } :rexpr {A_CONST :val 42} } :groupClause <> :havingClause <> :windowClause <> :valuesLists <> :sortClause ( {SORTBY :node {COLUMNREF :fields ("id") } :sortby_dir 2 :sortby_nulls 0 :useOp <> } ) :limitOffset <> :limitCount {A_CONST :val 23 } :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <> } ) SELECT * FROM foo WHERE bar = 42 ORDER BY id DESC LIMIT 23;

  8. ( {SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name

    <> :indirection <> :val {COLUMNREF :fields ( {A_STAR} ) } } ) :fromClause ( {RANGEVAR :schemaname <> :relname foo :relpersistence p :alias <> } ) :whereClause {AEXPR :name ("=") :lexpr {COLUMNREF :fields ("bar") } :rexpr {A_CONST :val 42} } :groupClause <> :havingClause <> :windowClause <> :valuesLists <> :sortClause ( {SORTBY :node {COLUMNREF :fields ("id") } :sortby_dir 2 :sortby_nulls 0 :useOp <> } ) :limitOffset <> :limitCount {A_CONST :val 23 } :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <> } ) SELECT * FROM foo WHERE bar = 42 ORDER BY id DESC LIMIT 23;

  9. ( {SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name

    <> :indirection <> :val {COLUMNREF :fields ( {A_STAR} ) } } ) :fromClause ( {RANGEVAR :schemaname <> :relname foo :relpersistence p :alias <> } ) :whereClause {AEXPR :name ("=") :lexpr {COLUMNREF :fields ("bar") } :rexpr {A_CONST :val 42} } :groupClause <> :havingClause <> :windowClause <> :valuesLists <> :sortClause ( {SORTBY :node {COLUMNREF :fields ("id") } :sortby_dir 2 :sortby_nulls 0 :useOp <> } ) :limitOffset <> :limitCount {A_CONST :val 23 } :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <> } ) SELECT * FROM foo WHERE bar = 42 ORDER BY id DESC LIMIT 23;

  10. ( {SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name

    <> :indirection <> :val {COLUMNREF :fields ( {A_STAR} ) } } ) :fromClause ( {RANGEVAR :schemaname <> :relname foo :relpersistence p :alias <> } ) :whereClause {AEXPR :name ("=") :lexpr {COLUMNREF :fields ("bar") } :rexpr {A_CONST :val 42} } :groupClause <> :havingClause <> :windowClause <> :valuesLists <> :sortClause ( {SORTBY :node {COLUMNREF :fields ("id") } :sortby_dir 2 :sortby_nulls 0 :useOp <> } ) :limitOffset <> :limitCount {A_CONST :val 23 } :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <> } ) SELECT * FROM foo WHERE bar = 42 ORDER BY id DESC LIMIT 23;

  11. ( {SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name

    <> :indirection <> :val {COLUMNREF :fields ( {A_STAR} ) } } ) :fromClause ( {RANGEVAR :schemaname <> :relname foo :relpersistence p :alias <> } ) :whereClause {AEXPR :name ("=") :lexpr {COLUMNREF :fields ("bar") } :rexpr {A_CONST :val 42} } :groupClause <> :havingClause <> :windowClause <> :valuesLists <> :sortClause ( {SORTBY :node {COLUMNREF :fields ("id") } :sortby_dir 2 :sortby_nulls 0 :useOp <> } ) :limitOffset <> :limitCount {A_CONST :val 23 } :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <> } ) SELECT * FROM foo WHERE bar = 42 ORDER BY id DESC LIMIT 23;

  12. ( {SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name

    <> :indirection <> :val {COLUMNREF :fields ( {A_STAR} ) } } ) :fromClause ( {RANGEVAR :schemaname <> :relname foo :relpersistence p :alias <> } ) :whereClause {AEXPR :name ("=") :lexpr {COLUMNREF :fields ("bar") } :rexpr {A_CONST :val 42} } :groupClause <> :havingClause <> :windowClause <> :valuesLists <> :sortClause ( {SORTBY :node {COLUMNREF :fields ("id") } :sortby_dir 2 :sortby_nulls 0 :useOp <> } ) :limitOffset <> :limitCount {A_CONST :val 23 } :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <> } ) SELECT * FROM foo WHERE bar = 42 ORDER BY id DESC LIMIT 23;

  13. ( {SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name

    <> :indirection <> :val {COLUMNREF :fields ( {A_STAR} ) } } ) :fromClause ( {RANGEVAR :schemaname <> :relname foo :relpersistence p :alias <> } ) :whereClause {AEXPR :name ("=") :lexpr {COLUMNREF :fields ("bar") } :rexpr {A_CONST :val 42} } :groupClause <> :havingClause <> :windowClause <> :valuesLists <> :sortClause ( {SORTBY :node {COLUMNREF :fields ("id") } :sortby_dir 2 :sortby_nulls 0 :useOp <> } ) :limitOffset <> :limitCount {A_CONST :val 23 } :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <> } ) SELECT * FROM foo WHERE bar = 42 ORDER BY id DESC LIMIT 23;

  14. ( {SELECT :distinctClause <> :intoClause <> :targetList ( {RESTARGET :name

    <> :indirection <> :val {COLUMNREF :fields ( {A_STAR} ) } } ) :fromClause ( {RANGEVAR :schemaname <> :relname foo :relpersistence p :alias <> } ) :whereClause {AEXPR :name ("=") :lexpr {COLUMNREF :fields ("bar") } :rexpr {A_CONST :val 42} } :groupClause <> :havingClause <> :windowClause <> :valuesLists <> :sortClause ( {SORTBY :node {COLUMNREF :fields ("id") } :sortby_dir 2 :sortby_nulls 0 :useOp <> } ) :limitOffset <> :limitCount {A_CONST :val 23 } :lockingClause <> :withClause <> :op 0 :all false :larg <> :rarg <> } ) SELECT * FROM foo WHERE bar = 42 ORDER BY id DESC LIMIT 23;

  15. The transformation process src/backend/rewrite/rewriteHandler.c:3621

  16. • Dependants from the catalog in the database • Types

    disambiguation • Add types information Transformations src/backend/rewrite/rewriteHandler.c:3621

  17. The rules system src/backend/rewrite/rewriteHandler.c:1986

  18. The rules system CREATE VIEW myview AS SELECT * FROM

    mytab; CREATE TABLE myview (same COLUMN list AS mytab); CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; src/backend/rewrite/rewriteHandler.c:1986

  19. The Query Plan src/include/optimizer/nodes/plannodes.h:120

  20. EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND

    unique2 > 9000; QUERY PLAN ------------------------------------------------------------------- ------------------ Bitmap Heap Scan ON tenk1 (cost=25.08..60.21 ROWS=10 width=244) Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) -> BitmapAnd (cost=25.08..25.08 ROWS=10 width=0) -> Bitmap INDEX Scan ON tenk1_unique1 (cost=0.00..5.04 ROWS=101 width=0) INDEX Cond: (unique1 < 100) -> Bitmap INDEX Scan ON tenk1_unique2 (cost=0.00..19.78 ROWS=999 width=0) INDEX Cond: (unique2 > 9000) src/include/optimizer/nodes/plannodes.h:120

  21. The Optimizer src/backend/optimizer/plan/planner.c:273

  22. • Scan Methods • Merge Methods • Join Order The

    Optimizer src/backend/optimizer/plan/planner.c:273

  23. Scan Methods • Sequential Scan • Bitmap Index Scan •

    Index Scan

  24. Scan Methods Sequential Scan sakila=# EXPLAIN SELECT * FROM film;

    QUERY PLAN ---------------------------------------------------------- Seq Scan ON film (cost=0.00..74.00 ROWS=1000 width=390) src/backend/executor/nodeSeqscan.c

  25. Scan Methods Sequential Scan film_id | title ---------+---------------------- 854 |

    STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 81 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE 488 | JOON NORTHWEST 985 | WONDERLAND CHRISTMAS 840 | STAMPEDE DISTURBING 399 | HAPPINESS UNITED 984 | WONDERFUL DROP 149 | CHRISTMAS MOONSHINE 988 | WORKER TARZAN 135 | CHANCE RESURRECTION →

  26. Scan Methods Sequential Scan film_id | title ---------+---------------------- 854 |

    STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 81 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE 488 | JOON NORTHWEST 985 | WONDERLAND CHRISTMAS 840 | STAMPEDE DISTURBING 399 | HAPPINESS UNITED 984 | WONDERFUL DROP 149 | CHRISTMAS MOONSHINE 988 | WORKER TARZAN 135 | CHANCE RESURRECTION →

  27. Scan Methods Sequential Scan film_id | title ---------+---------------------- 854 |

    STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 81 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE 488 | JOON NORTHWEST 985 | WONDERLAND CHRISTMAS 840 | STAMPEDE DISTURBING 399 | HAPPINESS UNITED 984 | WONDERFUL DROP 149 | CHRISTMAS MOONSHINE 988 | WORKER TARZAN 135 | CHANCE RESURRECTION →

  28. Scan Methods Sequential Scan film_id | title ---------+---------------------- 854 |

    STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 81 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE 488 | JOON NORTHWEST 985 | WONDERLAND CHRISTMAS 840 | STAMPEDE DISTURBING 399 | HAPPINESS UNITED 984 | WONDERFUL DROP 149 | CHRISTMAS MOONSHINE 988 | WORKER TARZAN 135 | CHANCE RESURRECTION →

  29. Scan Methods Index Scan sakila=# EXPLAIN SELECT * FROM film

    WHERE film_id=31; QUERY PLAN ------------------------------------------------------------------------ INDEX Scan USING film_pkey ON film (cost=0.28..8.29 ROWS=1 width=390) INDEX Cond: (film_id = 31) src/backend/executor/nodeIndexscan.c

  30. Scan Methods Index Scan

  31. Scan Methods Index Scan

  32. Scan Methods Index Scan

  33. Scan Methods Index Scan

  34. Scan Methods Index Scan

  35. Scan Methods Index Scan film_id | title ---------+---------------------- 854 |

    STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE 488 | JOON NORTHWEST 985 | WONDERLAND CHRISTMAS 840 | STAMPEDE DISTURBING 399 | HAPPINESS UNITED 984 | WONDERFUL DROP 149 | CHRISTMAS MOONSHINE 988 | WORKER TARZAN 135 | CHANCE RESURRECTION →

  36. Scan Methods Bitmap Index Scan sakila=# EXPLAIN SELECT * FROM

    film WHERE film_id > 30 and film_id < 60; QUERY PLAN ------------------------------------------------------------------------- Bitmap Heap Scan on film (cost=4.57..56.92 rows=29 width=390) Recheck Cond: ((film_id > 30) AND (film_id < 60)) -> Bitmap Index Scan on film_pkey (cost=0.00..4.57 rows=29 width=0) Index Cond: ((film_id > 30) AND (film_id < 60)) src/backend/executor/nodeBitmapIndexscan.c

  37. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  38. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  39. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  40. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  41. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 0 0 1 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  42. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 0 0 1 0 | 0 0 0 0 | 0 0 0 0 | 1 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  43. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 1 0 1 0 | 0 0 0 0 | 0 0 0 0 | 1 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  44. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 1 0 1 0 | 0 0 0 0 | 0 1 0 0 | 1 0 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  45. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 0 0 0 0 | 1 0 1 0 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  46. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 1 0 0 0 | 1 0 1 0 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  47. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 0] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  48. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  49. Scan Methods Bitmap Index Scan [0 0 0 0 |

    0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10

  50. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+----------------------

  51. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+----------------------

  52. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+----------------------

  53. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+----------------------

  54. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI

  55. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI

  56. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL

  57. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN

  58. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN

  59. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN

  60. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN

  61. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS

  62. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS

  63. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR

  64. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE

  65. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE

  66. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE

  67. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE

  68. Scan Methods Bitmap Index Scan Heap Scan [0 0 0

    0 | 0 0 0 0 | 1 0 0 0 | 1 0 1 1 | 0 0 0 0 | 0 1 0 0 | 1 1 0 0 | 0 0 0 0 | 0 0 0 0 | 0 0 0 1] page1 page2 page3 page4 page5 page6 page7 page8 page9 page10 film_id | title ---------+---------------------- 854 | STRANGERS GRAFFITI 967 | WEEKEND PERSONAL 789 | SHOCK CABIN 31 | BLINDNESS GUN 600 | MOTIONS DETAILS 144 | CHINATOWN GLADIATOR 835 | SPY MILE 488 | JOON NORTHWEST

  69. Join Methods • Nested Loop • Hash Join • Merge

    Join

  70. Nested Loop sakila=# EXPLAIN SELECT * FROM film JOIN LANGUAGE

    ON LANGUAGE.language_id = film.language_id WHERE LANGUAGE.language_id =2; QUERY PLAN --------------------------------------------------------------------------------------- Nested Loop (cost=0.15..5.25 ROWS=1 width=423) -> Seq Scan ON LANGUAGE (cost=0.00..1.07 ROWS=1 width=33) FILTER: (language_id = 2) -> Seq Scan ON film (cost=0.00..76.50 ROWS=1000 width=390) FILTER: (language_id = 2) src/backend/executor/nodeNestloop.c

  71. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE

  72. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE →

  73. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE →

  74. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE →

  75. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE →

  76. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE →

  77. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI

  78. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI

  79. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI

  80. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI

  81. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI

  82. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI

  83. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI 835 | 2 | SPY MILE

  84. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI 835 | 2 | SPY MILE

  85. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI 835 | 2 | SPY MILE

  86. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI 835 | 2 | SPY MILE

  87. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI 835 | 2 | SPY MILE

  88. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → → 854 | 2 | STRANGERS GRAFFITI 835 | 2 | SPY MILE

  89. Nested Loop language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 1 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE → 854 | 2 | STRANGERS GRAFFITI 835 | 2 | SPY MILE

  90. Hash Join sakila=# EXPLAIN SELECT * FROM film JOIN LANGUAGE

    ON LANGUAGE.language_id = film.language_id WHERE LANGUAGE.language_id > 3; QUERY PLAN --------------------------------------------------------------------- Hash Join (cost=1.12..79.61 rows=667 width=423) Hash Cond: (film.language_id = language.language_id) -> Seq Scan on film (cost=0.00..74.00 rows=1000 width=390) -> Hash (cost=1.07..1.07 rows=4 width=33) -> Seq Scan on language (cost=0.00..1.07 rows=4 width=33) Filter: (language_id > 3) src/backend/executor/nodeHashjoin.c

  91. Hash Join language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese Hash | rows -------------+---------------------- h0 | h1 | h2 | h3 | hash(x) ⇒

  92. Hash Join language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese Hash | rows -------------+---------------------- h0 | h1 | h2 | h3 | 5 hash(x) ⇒ →

  93. Hash Join language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese Hash | rows -------------+---------------------- h0 | h1 | h2 | h3 | 5 hash(x) ⇒ →

  94. Hash Join language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese Hash | rows -------------+---------------------- h0 | h1 | h2 | h3 | 5,6 hash(x) ⇒ →

  95. Hash Join language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese Hash | rows -------------+---------------------- h0 | 4 h1 | h2 | h3 | 5,6 hash(x) ⇒ →

  96. Hash Join language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese Hash | rows -------------+---------------------- h0 | 4 h1 | h2 | h3 | 5,6 hash(x) ⇒ →

  97. Hash Join language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese Hash | rows -------------+---------------------- h0 | 4 h1 | h2 | h3 | 5,6 hash(x) ⇒ →

  98. Hash Join language_id | name -------------+---------------------- 5 | French 1

    | English 6 | German 4 | Mandarin 2 | Italian 3 | Japanese Hash | rows -------------+---------------------- h0 | 4 h1 | h2 | h3 | 5,6 hash(x) ⇒

  99. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x)

  100. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) →

  101. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) →

  102. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) →

  103. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) →

  104. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) →

  105. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) →

  106. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → →

  107. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → →

  108. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → → 144 | 6 | CHINATOWN GLADIATOR

  109. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR

  110. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR

  111. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR

  112. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR →

  113. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING →

  114. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING →

  115. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING

  116. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING

  117. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING →

  118. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING 984 | 4 | WONDERFUL DROP →

  119. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING 984 | 4 | WONDERFUL DROP →

  120. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE

  121. Hash Join Hash | rows -------------+---------------------- h0 | 4 h1

    | h2 | h3 | 5,6 film_id | language_id | title ---------+-------------+-------------------- 854 | 2 | STRANGERS GRAFFITI 967 | 3 | WEEKEND PERSONAL 789 | 1 | SHOCK CABIN 81 | 3 | BLINDNESS GUN 600 | 1 | MOTIONS DETAILS 144 | 6 | CHINATOWN GLADIATOR 835 | 2 | SPY MILE 488 | 1 | JOON NORTHWEST 840 | 5 | STAMPEDE DISTURBING 399 | 1 | HAPPINESS UNITED 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE ⇐ hash(x) → → 144 | 6 | CHINATOWN GLADIATOR 840 | 5 | STAMPEDE DISTURBING 984 | 4 | WONDERFUL DROP 149 | 5 | CHRISTMAS MOONSHINE

  122. Merge Join src/backend/executor/nodeMergejoin.c sakila=# EXPLAIN SELECT * FROM film JOIN

    LANGUAGE ON LANGUAGE.language_id = film.language_id WHERE LANGUAGE.language_id > 3; QUERY PLAN --------------------------------------------------------------------------------------- Merge Join (cost=0.85..3.66 rows=99 width=338) Merge Cond: (film.language_id = language.language_id) -> Index Scan using idx_film_language_id on film (cost=0.42..2.45 rows=99 width=324) Index Cond: (language_id > 3) -> Index Only Scan using pk_language on language (cost=0.42..0.84 rows=12 width=18) Index Cond: (language_id > 3)

  123. Merge Join → 1 2 3 4 5 6 1

    4 5 5 6 6 Same order

  124. Merge Join → 1 2 3 4 5 6 1

    4 5 5 6 6

  125. Merge Join → 1 2 3 4 5 6 1

    4 5 5 6 6

  126. Merge Join → → 1 2 3 4 5 6

    1 4 5 5 6 6

  127. Merge Join → → 1 2 3 4 5 6

    1 4 5 5 6 6

  128. Merge Join → → film_id | language_id | title ---------+-------------+--------------------

    984 | 4 | WONDERFUL DROP 1 2 3 4 5 6 1 4 5 5 6 6

  129. Merge Join → → film_id | language_id | title ---------+-------------+--------------------

    984 | 4 | WONDERFUL DROP 1 2 3 4 5 6 1 4 5 5 6 6

  130. Merge Join → → film_id | language_id | title ---------+-------------+--------------------

    984 | 4 | WONDERFUL DROP 840 | 5 | STAMPEDE DISTURBING 1 2 3 4 5 6 1 4 5 5 6 6

  131. Merge Join → → film_id | language_id | title ---------+-------------+--------------------

    984 | 4 | WONDERFUL DROP 840 | 5 | STAMPEDE DISTURBING 149 | 5 | CHRISTMAS MOONSHINE 1 2 3 4 5 6 1 4 5 5 6 6

  132. Merge Join → → film_id | language_id | title ---------+-------------+--------------------

    984 | 4 | WONDERFUL DROP 840 | 5 | STAMPEDE DISTURBING 149 | 5 | CHRISTMAS MOONSHINE 1 2 3 4 5 6 1 4 5 5 6 6

  133. Merge Join → → film_id | language_id | title ---------+-------------+--------------------

    984 | 4 | WONDERFUL DROP 840 | 5 | STAMPEDE DISTURBING 149 | 5 | CHRISTMAS MOONSHINE 399 | 6 | HAPPINESS UNITED 1 2 3 4 5 6 1 4 5 5 6 6

  134. Merge Join → → film_id | language_id | title ---------+-------------+--------------------

    984 | 4 | WONDERFUL DROP 840 | 5 | STAMPEDE DISTURBING 149 | 5 | CHRISTMAS MOONSHINE 399 | 6 | HAPPINESS UNITED 967 | 6 | WEEKEND PERSONAL 1 2 3 4 5 6 1 4 5 5 6 6

  135. Genetic Query Optimizer src/backend/optimizer/geqo/geqo_main.c:67

  136. The executor src/backend/executor/execMain.c:302

  137. The executor • Executes the Query Plan • It does

    it in recursive order • Return the rows one by one src/backend/executor/execMain.c:302

  138. References • Explaining the Postgres Query Optimizer: https://youtu.be/wLpcVM9qxV0?si=pMmq2nHiHlV_37kf • Overview

    of PostgreSQL Internals: https://www.postgresql.org/docs/current/overview.html • Using EXPLAIN: https://www.postgresql.org/docs/current/using-explain.html • The Rule System: https://www.postgresql.org/docs/current/rules.html • Database System Research Group at the University of Tübingen: https://www.youtube.com/playlist?list=PL1XF9qjV8kH0ghGRGo3_f-FWqWvAbv1dh • PostgreSQL 14 Internals (book): https://postgrespro.com/community/books/internals

  139. CREDITS: This presentation template was created by Slidesgo, including icons

    by Flaticon and infographics & images by Freepik Thanks! Let’s keep in touch Please keep this slide for attribution jespinog jesus-espino jespino