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
72

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
47
Understanding the go runtime
jespino
0
79
Tests end-to-end en entornos complejos con testcontainers
jespino
0
19
Dissecting Slices, Maps and Channels in Go
jespino
0
40
Understanding the memory model of 5 languages
jespino
0
95
The Javascript Toolset
jespino
1
69
Deep dive in a SQL query
jespino
0
76
10 Aha! Moments reading the go source code
jespino
0
92
The secret life of a goroutine (Fosdem 2024)
jespino
0
110

Other Decks in Programming

See All in Programming
見せてあげますよ、「本物のLaravel批判」ってやつを。
77web
7
7.7k
ActiveSupport::Notifications supporting instrumentation of Rails apps with OpenTelemetry
ymtdzzz
1
230
C++でシェーダを書く
fadis
6
4.1k
役立つログに取り組もう
irof
28
9.6k
Webの技術スタックで マルチプラットフォームアプリ開発を可能にするElixirDesktopの紹介
thehaigo
2
1k
CSC509 Lecture 12
javiergs
PRO
0
160
3 Effective Rules for Using Signals in Angular
manfredsteyer
PRO
0
100
NSOutlineView何もわからん:( 前編 / I Don't Understand About NSOutlineView :( Pt. 1
usagimaru
0
330
PHP でアセンブリ言語のように書く技術
memory1994
PRO
1
170
2024/11/8 関西Kaggler会 2024 #3 / Kaggle Kernel で Gemma 2 × vLLM を動かす。
kohecchi
5
910
Tauriでネイティブアプリを作りたい
tsucchinoko
0
370
Duckdb-Wasmでローカルダッシュボードを作ってみた
nkforwork
0
120

Featured

See All Featured
Docker and Python
trallard
40
3.1k
Build The Right Thing And Hit Your Dates
maggiecrowley
33
2.4k
StorybookのUI Testing Handbookを読んだ
zakiyama
27
5.3k
Designing the Hi-DPI Web
ddemaree
280
34k
We Have a Design System, Now What?
morganepeng
50
7.2k
Rebuilding a faster, lazier Slack
samanthasiow
79
8.7k
Rails Girls Zürich Keynote
gr2m
94
13k
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
28
2k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
506
140k
Side Projects
sachag
452
42k
The Web Performance Landscape in 2024 [PerfNow 2024]
tammyeverts
0
89
A better future with KSS
kneath
238
17k

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