The explain command

----------------------------

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

------------------------------------------------------------------------------------------------

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.

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

PgAdmin3 File Edit Query Favourites Macros View Window Help ? 4) 28% G) Sat 2:18 PM a?E Query- assign1 on postgres@localhost:5432 ??????? 1??1 p, 1??/ut3 u l e e i ? | : Dassign l on postgres@local host:54 3 2 SQL Editor Graphical Query Builder rv Object browser Scratch pad ? Server Groups Previous queries DeleteDelete All Servers (1) INF407 imosc Query 1' ? Explain analyze Select Databases ( ? Li assign! ? From Student join enrollment on student.ID enrollment.Student ID Catalo ?Extensi Foreig join section on section.ID enrollment.section-1D join department on major- department.nane join faculty on faculty.id section.faculty_ID join address on address.id - student.address TD join Course on section.course Number - course.course-number and section,dept.id - course. deptID SchemWhere publ | --we want to make sure we have name information for students if we want to reach out to them Student.Name Last Not Like C' the theater deoartment has asked to be out of this studv Sel Output pane [Query 1] Data Outpu Messages History QUERY PLAN Slony O postgres %, Tablespace ?Group Roles A. Login Roles! (cost-28469.02. .28471.65 rows=1852 width-381) (actual time-3429.388.. 3429.312 rows-120 Loops-1) > 2 Sort Key: student.name last, enrollment.grade DESC, faculty.name last, student.major Sort Method: quicksort Memory: 61kB 3 4 Nested Loop Hash Join (cost-11549.26..28078.87 rows-1052 width-234) (actual time-3286.629. .3428.127 roNs-120 loops-1) ? (cost-11549.56 ..28416.22 rows-1052 width-301) (actual time-3286.650. .3429.037 rows-12a loops-1) Hash Cond: (Cstudent.major)::text- (department.name)::text) -> Hash Join (cost-11547.90, .28862. 81 rows-1117 width-140) (actual time-3286.586. .3427.999 rows-138 loops-11 Hash Cond: (student. id-enrollment, student-id) Join Filter: (Cstudent.advisor-id section.faculty id) AND (student.addressid faculty.addressid)) -Sea Scan on student Ccost-0.03..14687.80 rows-584090 width-53) Cactual time-0.064..105.75 10 5 rows-584685 loops-1 Filter: (((name-last) ;;text !- Rows Removed by Filter: 33835 ..;; text) AND CCmajor);:text 'Theater, ;;text)) 12 13 -Hash (cost-11532.98..11532.98 rows-1194 width-87) (actual tie-3283.437. .3283.437 rows-172 loops-1) OK. Unix Ln 1, Col 17, Ch 17 51 rows 3.4 secs Retrieving details on database assign1... Done assignl on postgres@local host:5432 anL 25 msec