sqlquestion.sql
text/x-sql
######## QUERY 1 ####################################################################
EXPLAIN SELECT "VisitorExtra"."ID",
"Visitor"."Created",
"Visitor"."Updated",
"Tidbit"."ID",
"ProgramEvent"."ID",
"Visitor"."Email",
"Interest"."ID",
"VisitorInternetDeviceAssoc"."ID",
"Referral"."ID"
FROM "VisitorExtra"
LEFT OUTER JOIN "Tidbit" ON "VisitorExtra"."ID"="Tidbit"."VisitorID"
LEFT OUTER JOIN "ProgramEvent" ON "VisitorExtra"."ID"="ProgramEvent"."VisitorID"
LEFT OUTER JOIN "Interest" ON "VisitorExtra"."ID"="Interest"."VisitorID"
LEFT OUTER JOIN "VisitorInternetDeviceAssoc" ON "VisitorExtra"."ID"="VisitorInternetDeviceAssoc"."VisitorID"
LEFT OUTER JOIN "Referral" ON "VisitorExtra"."ID"="Referral"."FromVisitorID","Visitor"
WHERE "VisitorExtra"."ID"="Visitor"."ID" AND ("Visitor"."ID" = 2);
Nested Loop (cost=380512.64..605796.12 rows=9 width=76)
-> Index Scan using Visitor_pkey on Visitor (cost=0.00..4.29 rows=1 width=32)
-> Materialize (cost=574946.55..574946.55 rows=2467623 width=44)
-> Merge Join (cost=380512.64..574946.55 rows=2467623 width=44)
-> Merge Join (cost=380512.64..542978.20 rows=2467623 width=36)
-> Merge Join (cost=380512.64..507911.61 rows=2467623 width=28)
-> Merge Join (cost=380512.64..470620.82 rows=2467623 width=20)
-> Merge Join (cost=349342.02..405798.50 rows=2403643 width=12)
-> Index Scan using VisitorExtra_pkey on VisitorExtra (cost=0.00..22898.28 rows=281013 width=4)
-> Sort (cost=349342.02..349342.02 rows=2403643 width=8)
-> Seq Scan on Tidbit (cost=0.00..49644.43 rows=2403643 width=8)
-> Sort (cost=31170.62..31170.62 rows=288493 width=8)
-> Seq Scan on ProgramEvent (cost=0.00..5006.93 rows=288493 width=8)
-> Index Scan using Interest_VisitorID_key on Interest (cost=0.00..5340.01 rows=88439 width=8)
-> Index Scan using VisitorInternetDeviceAssoc_Visi on VisitorInternetDeviceAssoc (cost=0.00..3447.06 rows=61939 width=8)
-> Index Scan using Referral_FromVisitorID_key on Referral (cost=0.00..914.08 rows=16719 width=8)
###### QUERY 2 ################################################################
EXPLAIN SELECT "VisitorExtra"."ID",
"Visitor"."Created",
"Visitor"."Updated",
"Tidbit"."ID",
"ProgramEvent"."ID",
"Visitor"."Email",
"Interest"."ID",
"VisitorInternetDeviceAssoc"."ID",
"Referral"."ID"
FROM "VisitorExtra"
LEFT OUTER JOIN "Tidbit" ON "VisitorExtra"."ID"="Tidbit"."VisitorID"
LEFT OUTER JOIN "ProgramEvent" ON "VisitorExtra"."ID"="ProgramEvent"."VisitorID"
LEFT OUTER JOIN "Interest" ON "VisitorExtra"."ID"="Interest"."VisitorID"
LEFT OUTER JOIN "VisitorInternetDeviceAssoc" ON "VisitorExtra"."ID"="VisitorInternetDeviceAssoc"."VisitorID"
LEFT OUTER JOIN "Referral" ON "VisitorExtra"."ID"="Referral"."FromVisitorID","Visitor"
WHERE "VisitorExtra"."ID" = 2;
Nested Loop (cost=0.00..73719.08 rows=2467623 width=72)
-> Nested Loop (cost=0.00..235.97 rows=9 width=44)
-> Nested Loop (cost=0.00..163.21 rows=9 width=36)
-> Nested Loop (cost=0.00..143.81 rows=9 width=28)
-> Nested Loop (cost=0.00..73.66 rows=9 width=20)
-> Nested Loop (cost=0.00..12.45 rows=9 width=12)
-> Index Scan using VisitorExtra_pkey on VisitorExtra (cost=0.00..3.56 rows=1 width=4)
-> Index Scan using Tidbit_VisitorID_key on Tidbit (cost=0.00..8.87 rows=2 width=8)
-> Index Scan using ProgramEvent_VisitorID_key on ProgramEvent (cost=0.00..7.14 rows=2 width=8)
-> Index Scan using Interest_VisitorID_key on Interest (cost=0.00..7.93 rows=5 width=8)
-> Index Scan using VisitorInternetDeviceAssoc_Visi on VisitorInternetDeviceAssoc (cost=0.00..2.20 rows=1 width=8)
-> Index Scan using Referral_FromVisitorID_key on Referral (cost=0.00..8.21 rows=6 width=8)
-> Seq Scan on Visitor (cost=0.00..5558.13 rows=281013 width=28)