Database assignment

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)