/*explain analyze*/ Select *

From Student join enrollment on student.ID = enrollment.Student_ID

join section on section.ID = enrollment.section_ID

join department on major = department.name

join faculty on faculty.id = section.faculty_ID

join address on address.id = student.address_ID

join Course on section.course_Number = course.course_number and section.dept_id = course.dept_ID

Where

--we want to make sure we have name information for students if we want to reach out to them

Student.Name_Last Not Like ('')

-- the theater department has asked to be out of this study

and Student.Major 'Theater'

--no students who have failed as we're looking for passing grades

and Grade > '1.33'

--we want to make sure we only have instructors, and the theater department is not part of this study

and Faculty.job in

(Select job

From Faculty

Where Job not in ('Administrative','General Services','Human Resources')

and Dept 'THT')

and Section_ID >=1

--summer courses don't always reflect accurately given their tight schedule and rapid fire delivery of materials

and Semester 'Summer'

--we don't want bias of an adivosr giving better grades

and Student.Advisor_ID Section.Faculty_ID

-- we don't want bias if a student is possibly a faculty members child

and Student.Address_ID Faculty.Address_ID

Order by Student.Name_Last, Grade desc, Faculty.Name_Last, Major

1. Based on the output from the explain command, determine the steps you would take (building indexes on certain columns, modifying the query, both, etc.) to get the same output, but improve overall performance.

2. Execute three steps that you think would make for improvement in the performance of this query.

Paralel Seq Scan on Hash Join Hash Join Hash Join Sort Gather Merge address _pkey Merge Jon Hash Sort section Hash Join Hash Join Hash faculty Hash faculty Hash Hash Paralel Seq Scan on Hash Join Hash Join Hash Join Sort Gather Merge address _pkey Merge Jon Hash Sort section Hash Join Hash Join Hash faculty Hash faculty Hash Hash