explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dkFp

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Group (cost=433,055.57..433,868.64 rows=19,131 width=180) (actual rows= loops=)

2.          

CTE teachercontract

3. 0.000 0.000 ↓ 0.0

Subquery Scan on teachercontract_1 (cost=9,068.58..19,189.88 rows=213 width=123) (actual rows= loops=)

  • Filter: (teachercontract_1.rownumber = 1)
4. 0.000 0.000 ↓ 0.0

WindowAgg (cost=9,068.58..14,821.74 rows=42,616 width=114) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=9,068.58..9,175.12 rows=42,616 width=114) (actual rows= loops=)

  • Sort Key: teachersessioncontract.servicetype, teachersessioncontract.relationship, teachersessioncontract.program_id, ((teachersessioncontract.ondemandsessionstudents)::character varying), teachersessioncontract.teacher_id, ((teachersessioncontract.participanttype)::character varying), ((((((((teachersessioncontract.until_year)::text || '-'::text) || (teachersessioncontract.until_month)::text) || '-01'::text))::date + '1 mon -1 days'::interval))::date)
6. 0.000 0.000 ↓ 0.0

Result (cost=0.00..5,791.60 rows=42,616 width=114) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Append (cost=0.00..5,791.60 rows=42,616 width=114) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on teachersessioncontract (cost=0.00..5,734.60 rows=42,156 width=114) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on teachertaskcontract (cost=0.00..57.00 rows=460 width=114) (actual rows= loops=)

10.          

CTE teacherxprogram

11. 0.000 0.000 ↓ 0.0

Unique (cost=12,400.23..12,799.72 rows=17,755 width=113) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Sort (cost=12,400.23..12,444.62 rows=17,755 width=113) (actual rows= loops=)

  • Sort Key: program_new.id, program_new.code, teacherxprogram_1.maxcourseid, teacherxprogram_1.teacherid, teacherxprogram_1.cultureaccentid, teacherxprogram_1.qualifications, cultureaccent.name, programxcourse.courseid
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=5,073.57..11,147.09 rows=17,755 width=113) (actual rows= loops=)

  • Hash Cond: (teacherxprogram_1.cultureaccentid = cultureaccent.id)
14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,024.81..10,919.26 rows=17,755 width=96) (actual rows= loops=)

  • Merge Cond: (program_new.id = programxcourse.programid)
  • Join Filter: (programxcourse.indexnumber <= maxcourselevel.indexnumber)
15. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=4,851.55..4,979.82 rows=16,030 width=84) (actual rows= loops=)

  • Merge Cond: ((program_new.id = maxcourselevel.programid) AND (teacherxprogram_1.maxcourseid = maxcourselevel.courseid))
16. 0.000 0.000 ↓ 0.0

Sort (cost=4,678.28..4,718.36 rows=16,030 width=80) (actual rows= loops=)

  • Sort Key: program_new.id, teacherxprogram_1.maxcourseid
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=74.60..3,558.71 rows=16,030 width=80) (actual rows= loops=)

  • Hash Cond: (teacherxprogram_1.programid = program_new.id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on teacherxprogram teacherxprogram_1 (cost=0.00..1,821.00 rows=16,030 width=73) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=41.90..41.90 rows=319 width=23) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on program_new (cost=0.00..41.90 rows=319 width=23) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=173.26..175.91 rows=1,060 width=36) (actual rows= loops=)

  • Sort Key: maxcourselevel.programid, maxcourselevel.courseid
22. 0.000 0.000 ↓ 0.0

Seq Scan on programxcourse maxcourselevel (cost=0.00..120.00 rows=1,060 width=36) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Sort (cost=173.26..175.91 rows=1,060 width=36) (actual rows= loops=)

  • Sort Key: programxcourse.programid
24. 0.000 0.000 ↓ 0.0

Seq Scan on programxcourse (cost=0.00..120.00 rows=1,060 width=36) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash (cost=26.10..26.10 rows=221 width=33) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on entitytype cultureaccent (cost=0.00..26.10 rows=221 width=33) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=401,065.97..401,113.80 rows=19,131 width=180) (actual rows= loops=)

  • Sort Key: teacher.user_id, stats.studentcount, stats.sessioncount, stats.corecurriculumsessionpercentage, stats.sessionreportpercentage, stats.sessionreportentrycountaverage, stats.sessionratingcount, stats.sessionratingaverage, partner.partner_id, seniorteacher.user_id, teachermanager.user_id, teacherstatus.id, educationlevel.id, agreementtype.id, nationalitycountry.country_id, stats.firstsessionon
28. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=390,525.42..399,705.41 rows=19,131 width=180) (actual rows= loops=)

  • Hash Cond: (teacher.user_id = teacherxcertificationstatus.teacherid)
29. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=390,207.95..398,737.00 rows=19,131 width=180) (actual rows= loops=)

  • Join Filter: false
30. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=390,207.95..398,737.00 rows=19,131 width=180) (actual rows= loops=)

  • Join Filter: false
31. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=390,207.95..398,737.00 rows=19,131 width=180) (actual rows= loops=)

  • Join Filter: false
32. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=390,207.95..398,737.00 rows=19,131 width=180) (actual rows= loops=)

  • Hash Cond: (teacher.user_id = teacherxlanguageproficiency.teacherid)
33. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=389,945.31..397,919.04 rows=19,131 width=180) (actual rows= loops=)

  • Hash Cond: (teacher.nationalitycountry_id = nationalitycountry.country_id)
34. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=389,893.09..397,214.48 rows=19,131 width=180) (actual rows= loops=)

  • Hash Cond: (teacher.educationlevel_id = educationlevel.id)
35. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=389,846.09..397,066.33 rows=19,131 width=180) (actual rows= loops=)

  • Hash Cond: (teacher.agreementtype_id = agreementtype.id)
36. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=389,799.08..395,233.48 rows=19,131 width=180) (actual rows= loops=)

  • Hash Cond: (teacher.status_id = teacherstatus.id)
37. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=389,752.07..394,504.43 rows=19,131 width=180) (actual rows= loops=)

  • Hash Cond: (teacher.teachermanager_id = teachermanager.user_id)
38. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=389,578.77..393,593.29 rows=19,131 width=180) (actual rows= loops=)

  • Hash Cond: (teacher.manager_id = seniorteacher.user_id)
39. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=389,405.47..392,604.45 rows=19,131 width=180) (actual rows= loops=)

  • Merge Cond: (teacher.user_id = teacherauditevent.teacher_id)
40. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=386,858.68..389,886.77 rows=19,131 width=180) (actual rows= loops=)

  • Merge Cond: (teacher.user_id = teacherxprogram.teacherid)
41. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=382,054.54..383,055.49 rows=7,353 width=180) (actual rows= loops=)

  • Merge Cond: (teacher.user_id = stats.teacher_id)
42. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=3,043.96..3,255.79 rows=6,824 width=128) (actual rows= loops=)

  • Merge Cond: (teacher.user_id = teacherxlocation.teacherid)
43. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=2,822.02..2,878.51 rows=6,824 width=128) (actual rows= loops=)

  • Merge Cond: (teacher.user_id = teachercontract.teacher_id)
44. 0.000 0.000 ↓ 0.0

Sort (cost=2,771.19..2,788.25 rows=6,824 width=128) (actual rows= loops=)

  • Sort Key: teacher.user_id
45. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14.93..2,336.62 rows=6,824 width=128) (actual rows= loops=)

  • Hash Cond: (teacher.employer_id = partner.partner_id)
46. 0.000 0.000 ↓ 0.0

Seq Scan on teacher (cost=0.00..1,911.40 rows=6,824 width=128) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Hash (cost=8.47..8.47 rows=63 width=16) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Index Only Scan using partner_pkey on partner (cost=0.14..8.47 rows=63 width=16) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Sort (cost=50.84..51.37 rows=213 width=16) (actual rows= loops=)

  • Sort Key: teachercontract.teacher_id
50. 0.000 0.000 ↓ 0.0

CTE Scan on teachercontract (cost=0.00..42.60 rows=213 width=16) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Sort (cost=221.94..225.23 rows=1,317 width=48) (actual rows= loops=)

  • Sort Key: teacherxlocation.teacherid
52. 0.000 0.000 ↓ 0.0

Seq Scan on teacherxlocation (cost=0.00..153.70 rows=1,317 width=48) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Sort (cost=379,010.57..379,028.96 rows=7,353 width=68) (actual rows= loops=)

  • Sort Key: stats.teacher_id
54. 0.000 0.000 ↓ 0.0

Foreign Scan on dw_cache_teacher_stats stats (cost=10,000.00..378,538.36 rows=7,353 width=68) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Sort (cost=4,804.14..4,848.53 rows=17,755 width=16) (actual rows= loops=)

  • Sort Key: teacherxprogram.teacherid
56. 0.000 0.000 ↓ 0.0

CTE Scan on teacherxprogram (cost=0.00..3,551.00 rows=17,755 width=16) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Sort (cost=2,546.79..2,547.84 rows=418 width=16) (actual rows= loops=)

  • Sort Key: teacherauditevent.teacher_id
58. 0.000 0.000 ↓ 0.0

Seq Scan on teacherauditevent (cost=0.00..2,528.60 rows=418 width=16) (actual rows= loops=)

  • Filter: (type = 'StatusUpdated'::teacherauditeventtype)
59. 0.000 0.000 ↓ 0.0

Hash (cost=90.48..90.48 rows=808 width=16) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Only Scan using user_pkey on "user" seniorteacher (cost=0.28..90.48 rows=808 width=16) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash (cost=90.48..90.48 rows=808 width=16) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Index Only Scan using user_pkey on "user" teachermanager (cost=0.28..90.48 rows=808 width=16) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=24.36..24.36 rows=221 width=16) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Index Only Scan using entitytype_id_pk on entitytype teacherstatus (cost=0.14..24.36 rows=221 width=16) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Hash (cost=24.36..24.36 rows=221 width=16) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Index Only Scan using entitytype_id_pk on entitytype agreementtype (cost=0.14..24.36 rows=221 width=16) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash (cost=24.36..24.36 rows=221 width=16) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Only Scan using entitytype_id_pk on entitytype educationlevel (cost=0.14..24.36 rows=221 width=16) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash (cost=26.80..26.80 rows=248 width=16) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on country nationalitycountry (cost=0.00..26.80 rows=248 width=16) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=138.30..138.30 rows=1,213 width=48) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on teacherxlanguageproficiency (cost=0.00..138.30 rows=1,213 width=48) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.10 rows=1 width=0) (actual rows= loops=)

  • One-Time Filter: false
74. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.10 rows=1 width=0) (actual rows= loops=)

  • One-Time Filter: false
75. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.10 rows=1 width=0) (actual rows= loops=)

  • One-Time Filter: false
76. 0.000 0.000 ↓ 0.0

Hash (cost=166.90..166.90 rows=1,469 width=48) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Seq Scan on teacherxcertificationstatus (cost=0.00..166.90 rows=1,469 width=48) (actual rows= loops=)