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

Deep dive in a SQL query

Jesús Espino
March 19, 2024
Programming
0
79

Deep dive in a SQL query

Jesús Espino

March 19, 2024
Tweet

More Decks by Jesús Espino

See All by Jesús Espino
Deep dive into a go binary
jespino
0
64
Deep dive in a SQL query (PgIbz)
jespino
2
73
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
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
【re:Growth 2024】 Aurora DSQL をちゃんと話します！
maroon1st
0
770
たのしいparse.y
ydah
3
120
LLM Supervised Fine-tuningの理論と実践
datanalyticslabo
3
1.1k
フロントエンドのディレクトリ構成どうしてる？　Feature-Sliced Design 導入体験談
osakatechlab
8
4.1k
talk-with-local-llm-with-web-streams-api
kbaba1001
0
180
[JAWS-UG横浜 #76] イケてるアップデートを宇宙いち早く紹介するよ！
maroon1st
0
460
KMP와 kotlinx.rpc로 서버와 클라이언트 동기화
kwakeuijin
0
140
17年周年のWebアプリケーションにTanStack Queryを導入する / Implementing TanStack Query in a 17th Anniversary Web Application
saitolume
0
250
CQRS+ES の力を使って効果を感じる / Feel the effects of using the power of CQRS+ES
seike460
PRO
0
120
CSC305 Lecture 26
javiergs
PRO
0
140
nekko cloudにおけるProxmox VE利用事例
irumaru
3
420
今からはじめるAndroidアプリ開発 2024 / DevFest 2024
star_zero
0
1k

Featured

See All Featured
Automating Front-end Workflow
addyosmani
1366
200k
How to train your dragon (web standard)
notwaldorf
88
5.7k
How STYLIGHT went responsive
nonsquared
95
5.2k
Facilitating Awesome Meetings
lara
50
6.1k
Large-scale JavaScript Application Architecture
addyosmani
510
110k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
28
9.1k
A Modern Web Designer's Workflow
chriscoyier
693
190k
We Have a Design System, Now What?
morganepeng
51
7.3k
How to Think Like a Performance Engineer
csswizardry
22
1.2k
Stop Working from a Prison Cell
hatefulcrawdad
267
20k
Done Done
chrislema
181
16k
The Art of Delivering Value - GDevCon NA Keynote
reverentgeek
8
1.2k

Transcript

  1. Deep dive in a SQL query

  2. Jesús Espino Quien Soy Ex-Alumno Ex-Miembro Principal Engineer Cofundador

  3. Introducción

  4. Postgres 16.2

  5. El Parser src/backend/parser/

  6. ( {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;

  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. El proceso de transformación src/backend/rewrite/rewriteHandler.c:3621

  15. • Dependientes del catálogo de la BBDD • Desambiguación de

    tipos • Añade información de tipos Transformaciones src/backend/rewrite/rewriteHandler.c:3621

  16. El sistema de reglas src/backend/rewrite/rewriteHandler.c:1986

  17. El sistema de reglas 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

  18. El Query Plan src/include/optimizer/nodes/plannodes.h:120

  19. 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

  20. El Optimizador src/backend/optimizer/plan/planner.c:273

  21. • Scan Methods • Merge Methods • Join Order El

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

  22. Scan Methods • Sequential Scan • Bitmap Index Scan •

    Index Scan

  23. 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

  24. 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 →

  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 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

  29. Scan Methods Index Scan

  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 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 →

  35. 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

  36. 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

  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 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

  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 | 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

  42. 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

  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 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

  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 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

  45. 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

  46. 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

  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 1] 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 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 ---------+----------------------

  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 ---------+---------------------- 854 | STRANGERS GRAFFITI

  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 967 | WEEKEND PERSONAL

  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 789 | SHOCK CABIN

  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 31 | BLINDNESS GUN

  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 600 | MOTIONS DETAILS

  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 144 | CHINATOWN GLADIATOR

  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 835 | SPY MILE

  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 488 | JOON NORTHWEST

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

    Join

  69. 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

  70. 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

  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 → → 854 | 2 | STRANGERS GRAFFITI

  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 835 | 2 | SPY MILE

  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. 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

  90. 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) ⇒

  91. 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) ⇒ →

  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,6 hash(x) ⇒ →

  94. 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) ⇒ →

  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 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)

  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) → → 144 | 6 | CHINATOWN GLADIATOR

  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 840 | 5 | STAMPEDE DISTURBING →

  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 984 | 4 | WONDERFUL DROP →

  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 149 | 5 | CHRISTMAS MOONSHINE

  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. 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)

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

    4 5 5 6 6 Mismo orden

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

    4 5 5 6 6

  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 → → film_id | language_id | title ---------+-------------+--------------------

    984 | 4 | WONDERFUL DROP 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 840 | 5 | STAMPEDE DISTURBING 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 149 | 5 | CHRISTMAS MOONSHINE 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 399 | 6 | HAPPINESS UNITED 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 967 | 6 | WEEKEND PERSONAL 1 2 3 4 5 6 1 4 5 5 6 6

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

  135. El ejecutor src/backend/executor/execMain.c:302

  136. El ejecutor • Ejecuta el Query Plan • Lo hace

    en orden recursivo • Devuelve las filas una a una src/backend/executor/execMain.c:302

  137. El Cursor

  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 ¡Gracias! Mantengamos el contacto Please keep this slide for attribution jespinog jesus-espino jespino