Tweet
Tweet

Slide 1

Slide 1 text

Deep dive in a SQL query

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

Introducción

Slide 4

Slide 4 text

Postgres 16.2

Slide 5

Slide 5 text

El Parser src/backend/parser/

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

El proceso de transformación src/backend/rewrite/rewriteHandler.c:3621

Slide 15

Slide 15 text

● Dependientes del catálogo de la BBDD ● Desambiguación de tipos ● Añade información de tipos Transformaciones src/backend/rewrite/rewriteHandler.c:3621

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

● Scan Methods ● Merge Methods ● Join Order El Optimizador src/backend/optimizer/plan/planner.c:273

Slide 22

Slide 22 text

Scan Methods ● Sequential Scan ● Bitmap Index Scan ● Index Scan

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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 →

Slide 25

Slide 25 text

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 →

Slide 26

Slide 26 text

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 →

Slide 27

Slide 27 text

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 →

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

Scan Methods Index Scan

Slide 30

Slide 30 text

Scan Methods Index Scan

Slide 31

Slide 31 text

Scan Methods Index Scan

Slide 32

Slide 32 text

Scan Methods Index Scan

Slide 33

Slide 33 text

Scan Methods Index Scan

Slide 34

Slide 34 text

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 →

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

Join Methods ● Nested Loop ● Hash Join ● Merge Join

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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 →

Slide 72

Slide 72 text

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 →

Slide 73

Slide 73 text

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 →

Slide 74

Slide 74 text

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 →

Slide 75

Slide 75 text

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 →

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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)

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

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

Slide 111

Slide 111 text

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 →

Slide 112

Slide 112 text

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 →

Slide 113

Slide 113 text

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 →

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

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

Slide 116

Slide 116 text

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 →

Slide 117

Slide 117 text

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 →

Slide 118

Slide 118 text

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 →

Slide 119

Slide 119 text

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

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

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)

Slide 122

Slide 122 text

Merge Join → 1 2 3 4 5 6 1 4 5 5 6 6 Mismo orden

Slide 123

Slide 123 text

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

Slide 124

Slide 124 text

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

Slide 125

Slide 125 text

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

Slide 126

Slide 126 text

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

Slide 127

Slide 127 text

Merge Join → → film_id | language_id | title ---------+-------------+-------------------- 984 | 4 | WONDERFUL DROP 1 2 3 4 5 6 1 4 5 5 6 6

Slide 128

Slide 128 text

Merge Join → → film_id | language_id | title ---------+-------------+-------------------- 984 | 4 | WONDERFUL DROP 1 2 3 4 5 6 1 4 5 5 6 6

Slide 129

Slide 129 text

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

Slide 130

Slide 130 text

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

Slide 131

Slide 131 text

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

Slide 132

Slide 132 text

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

Slide 133

Slide 133 text

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

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

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

Slide 136

Slide 136 text

El ejecutor ● Ejecuta el Query Plan ● Lo hace en orden recursivo ● Devuelve las filas una a una src/backend/executor/execMain.c:302

Slide 137

Slide 137 text

El Cursor

Slide 138

Slide 138 text

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

Slide 139

Slide 139 text

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