Question 3: Query Plans For each SQL statement below, draw the query plan that Postgres chooses (which you can obtain with the EXPLAIN command). For each plan, suggest a reason that the particular join algorithms were chosen.

1-

explain SELECT A1.last, A2.last FROM agent A1, agent A2, languagerel LR, Language L WHERE A1.salary < A2.salary AND A2.agent_ID = LR.agent_ID AND LR.lang_ID = L.lang_ID AND L.language = 'French';

QUERY PLAN --------------------------------------------------------------------------------------------------------------- Nested Loop (cost=18.80..1044.78 rows=21967 width=14) Join Filter: (a1.salary < a2.salary) -> Seq Scan on agent a1 (cost=0.00..14.62 rows=662 width=11) -> Materialize (cost=18.80..37.41 rows=100 width=11) -> Hash Join (cost=18.80..36.91 rows=100 width=11) Hash Cond: (a2.agent_id = lr.agent_id) -> Seq Scan on agent a2 (cost=0.00..14.62 rows=662 width=15) -> Hash (cost=17.55..17.55 rows=100 width=4) -> Nested Loop (cost=5.05..17.55 rows=100 width=4) -> Seq Scan on language l (cost=0.00..1.25 rows=1 width=4) Filter: ((language)::text = 'French'::text) -> Bitmap Heap Scan on languagerel lr (cost=5.05..15.30 rows=100 width=8) Recheck Cond: (lang_id = l.lang_id) -> Bitmap Index Scan on languagerel_pkey (cost=0.00..5.03 rows=100 width=0) Index Cond: (lang_id = l.lang_id) (15 rows)

2-

f17tdb47=> explain SELECT A1.last, A2.last FROM agent A1, agent A2, languagerel LR, Language L WHERE A1.salary = A2.salary AND A2.agent_ID = LR.agent_ID AND LR.lang_ID = L.lang_ID AND L.language = 'French'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Hash Join (cost=38.16..57.69 rows=242 width=14) Hash Cond: (a1.salary = a2.salary) -> Seq Scan on agent a1 (cost=0.00..14.62 rows=662 width=11) -> Hash (cost=36.91..36.91 rows=100 width=11) -> Hash Join (cost=18.80..36.91 rows=100 width=11) Hash Cond: (a2.agent_id = lr.agent_id) -> Seq Scan on agent a2 (cost=0.00..14.62 rows=662 width=15) -> Hash (cost=17.55..17.55 rows=100 width=4) -> Nested Loop (cost=5.05..17.55 rows=100 width=4) -> Seq Scan on language l (cost=0.00..1.25 rows=1 width=4) Filter: ((language)::text = 'French'::text) -> Bitmap Heap Scan on languagerel lr (cost=5.05..15.30 rows=100 width=8) Recheck Cond: (lang_id = l.lang_id) -> Bitmap Index Scan on languagerel_pkey (cost=0.00..5.03 rows=100 width=0) Index Cond: (lang_id = l.lang_id) (15 rows)

3-

explain SELECT A1.last, A2.last FROM agent A1, agent A2, languagerel LR, Language L WHERE A1.salary > A2.salary AND A2.agent_ID = LR.agent_ID AND LR.lang_ID = L.lang_ID AND L.language <> 'French'; QUERY PLAN ------------------------------------------------------------------------------------ Hash Join (cost=80.41..12135.98 rows=417379 width=14) Hash Cond: (a2.agent_id = lr.agent_id) -> Nested Loop (cost=0.00..6604.56 rows=146081 width=18) Join Filter: (a1.salary > a2.salary) -> Seq Scan on agent a1 (cost=0.00..14.62 rows=662 width=11) -> Materialize (cost=0.00..17.93 rows=662 width=15) -> Seq Scan on agent a2 (cost=0.00..14.62 rows=662 width=15) -> Hash (cost=56.77..56.77 rows=1891 width=4) -> Hash Join (cost=1.49..56.77 rows=1891 width=4) Hash Cond: (lr.lang_id = l.lang_id) -> Seq Scan on languagerel lr (cost=0.00..28.91 rows=1991 width=8) -> Hash (cost=1.25..1.25 rows=19 width=4) -> Seq Scan on language l (cost=0.00..1.25 rows=19 width=4) Filter: ((language)::text <> 'French'::text) (14 rows)