explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GmjO : existing plan, that EffectiveDueDates generates

Settings
# exclusive inclusive rows x rows loops node
1. 50.804 6,665.628 ↓ 459.3 37,200 1

Nested Loop Left Join (cost=967.73..978.30 rows=81 width=80) (actual time=6,594.109..6,665.628 rows=37,200 loops=1)

  • Join Filter: ((periods.start_date < overrides.trunc_due_at) AND (overrides.trunc_due_at <= periods.end_date))
  • Rows Removed by Join Filter: 148260
2.          

CTE models

3. 4,425.968 4,425.968 ↓ 621.0 621 1

Index Scan using index_assignments_on_context_id_and_context_type on assignments (cost=0.43..281.98 rows=1 width=1,681) (actual time=38.993..4,425.968 rows=621 loops=1)

  • Index Cond: ((context_id = 1128554) AND ((context_type)::text = 'Course'::text))
  • Filter: (((workflow_state)::text <> 'deleted'::text) AND (id = ANY ('{11546996,11546997,11546998,11546999,11547000,11547001,11547002,11547003,11547004,11547005,11547008,11547009,11547010,11547011,11547012,11547013,11547014,11547015,11547016,11547017,11547018,11547019,11547020,11547021,11547022,11547023,11547024,11547025,11547026,11547027,11547028,11547029,11547030,11547031,11547032,11547033,11547034,11547035,11547036,11547037,11547038,11547039,11547040,11547041,11547042,11547043,11547045,11547046,11547047,11547048,11547049,11547050,11547051,11547052,11547053,11547054,11547055,11547056,11547057,11547058,11547059,11547060,11547061,11547062,11547063,11547064,11547065,11547066,11547067,11547068,11547069,11547070,11547071,11547072,11547073,11547074,11547075,11547076,11547077,11547078,11547079,11547080,11547081,11547082,11547083,11547084,11547085,11547086,11547087,11547088,11547089,11547090,11547091,11547093,11547094,11547095,11547096,11547097,11547098,11547099,11547100,11547101,11547102,11547103,11547104,11547105,11547106,11547107,11547109,11547110,11547111,11547112,11547113,11547114,11547115,11547116,11547117,11547118,11547119,11547120,11547121,11547122,11547124,11547125,11547126,11547127,11547128,11547129,11547130,11547131,11547132,11547133,11547134,11547135,11547136,11547138,11547139,11547140,11547141,11547142,11547143,11547144,11547145,11547146,11547147,11547148,11547149,11547150,11547151,11547152,11547153,11547154,11547155,11547156,11547157,11547158,11547159,11547160,11547161,11547162,11547163,11547164,11547165,11547166,11547167,11547168,11547169,11547170,11547171,11547172,11547173,11547174,11547175,11547176,11547177,11547178,11547179,11547180,11547181,11547182,11547183,11547184,11547185,11547186,11547187,11547188,11547189,11547190,11547192,11547193,11547194,11547195,11547196,11547197,11547198,11547199,11547200,11547201,11547202,11547203,11547204,11547205,11547206,11547207,11547208,11547209,11547210,11547211,11547212,11547213,11547214,11547215,11547216,11547217,11547218,11547219,11547220,11547221,11547222,11547223,11547224,11547225,11547226,11547227,11547228,11547229,11547230,11547231,11547232,11547233,11547234,11547235,11547236,11547237,11547238,11547239,11547240,11547241,11547242,11547243,11547244,11547245,11547246,11547247,11547248,11547249,11547250,11547251,11547252,11547253,11547254,11547255,11547256,11547257,11547258,11547259,11547260,11547261,11547262,11547263,11547264,11547265,11547266,11547267,11547268,11547269,11547270,11547271,11547272,11547273,11547274,11547275,11547276,11547277,11547278,11547279,11547280,11547281,11547282,11547283,11547284,11547285,11547286,11547287,11547288,11547289,11547290,11547291,11547292,11547293,11547294,11547295,11547296,11547297,11547298,11547299,11547300,11547301,11547302,11547303,11547304,11547305,11547306,11547307,11547308,11547309,11547310,11547311,11547312,11547313,11547314,11547315,11547316,11547317,11547319,11547320,11547321,11547322,11547323,11547324,11547325,11547326,11547327,11547328,11547329,11547330,11547331,11547332,11547333,11547334,11547335,11547336,11547337,11547338,11547339,11547340,11547341,11547342,11547343,11547344,11547345,11547346,11547347,11547348,11547349,11547350,11547351,11547352,11547353,11547354,11547355,11547356,11547357,11547358,11547359,11547360,11547361,11547362,11547363,11547364,11547365,11547366,11547367,11547368,11547369,11547370,11547371,11547372,11547373,11547374,11547375,11547376,11547377,11547379,11547380,11547381,11547382,11547383,11547384,11547385,11547387,11547388,11547389,11547391,11547392,11547393,11547395,11547396,11547397,11547399,11547400,11547401,11547403,11547405,11547407,11547408,11547410,11547412,11547414,11547416,11547418,11547419,11547421,11547423,11547424,11547425,11547426,11547427,11547428,11547429,11547430,11547431,11547432,11547433,11547434,11547435,11547436,11547437,11547438,11547439,11547440,11547441,11547442,11547443,11547444,11547445,11547446,11547447,11547448,11547449,11547450,11547451,11547452,11547453,11547454,11547455,11547456,11547457,11547458,11547459,11547460,11547461,11547462,11547463,11547464,11547465,11547466,11547467,11547468,11547469,11547470,11547471,11547472,11547473,11547474,11547475,11547476,11547477,11547478,11547479,11547480,11547481,11547482,11547483,11547484,11547485,11547486,11547487,11547488,11547489,11547490,11547491,11547492,11547493,11547494,11547495,11547496,11547497,11547498,11547499,11547500,11547501,11547502,11547503,11547504,11547505,11547506,11547507,11547508,11547509,11547510,11547511,11547512,11547513,11547514,11547515,11547516,11547517,11547518,11547519,11547521,11547522,11547523,11547524,11547525,11547526,11547527,11547528,11547530,11547531,11547532,11547533,11547534,11547535,11547536,11547537,11547538,11547539,11547540,11547541,11547542,11547543,11547544,11547545,11547546,11547547,11547548,11547549,11547550,11547551,11547552,11547553,11547554,11547555,11547556,11547557,11547558,11547559,11547560,11547561,11547562,11547563,11547564,11547565,11547566,11547567,11547568,11547569,11547570,11547571,11547572,11547573,11547574,11547575,11547576,11547577,11547578,11547579,11547580,11547581,11547583,11547584,11547585,11547587,11547588,11547589,11547590,11547591,11547592,11547593,11547594,11547595,11547596,11547597,11547598,11547599,11547600,11547601,11547602,11547603,11547604,11547605,11547606,11547607,11547608,11547610,11547611,11547612,11547613,11547614,11547615,11547616,11547617,11547618,11547619,11547620,11547621,11547622,11547623,11547625,11547626,11547627,11547628,11547630,11547631,11547632,11547633,11547634,11547635,11547636,11547637,11547638,11547640,11547641,11547642,11547643,11547644,11547645,11557402,11581324,11597323}'::bigint[])))
4.          

CTE overrides

5. 1.227 4,486.821 ↓ 0.0 0 1

Nested Loop (cost=0.43..85.70 rows=28 width=48) (actual time=4,486.821..4,486.821 rows=0 loops=1)

6. 4,429.083 4,429.083 ↓ 621.0 621 1

CTE Scan on models a (cost=0.00..0.02 rows=1 width=16) (actual time=38.998..4,429.083 rows=621 loops=1)

7. 56.511 56.511 ↓ 0.0 0 621

Index Scan using index_assignment_overrides_on_assignment_id on assignment_overrides o (cost=0.43..85.40 rows=28 width=40) (actual time=0.091..0.091 rows=0 loops=621)

  • Index Cond: (assignment_id = a.id)
  • Filter: ((workflow_state)::text = 'active'::text)
8.          

CTE override_adhoc_students

9. 0.001 4,486.824 ↓ 0.0 0 1

Nested Loop (cost=0.43..3.88 rows=1 width=549) (actual time=4,486.824..4,486.824 rows=0 loops=1)

10. 4,486.823 4,486.823 ↓ 0.0 0 1

CTE Scan on overrides o_1 (cost=0.00..0.63 rows=1 width=541) (actual time=4,486.823..4,486.823 rows=0 loops=1)

  • Filter: ((set_type)::text = 'ADHOC'::text)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using index_assignment_override_students_on_assignment_override_id on assignment_override_students os (cost=0.43..3.24 rows=1 width=16) (never executed)

  • Index Cond: (assignment_override_id = o_1.id)
  • Filter: ((workflow_state)::text = 'active'::text)
12.          

CTE override_groups_students

13. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.71..3.96 rows=3 width=549) (actual time=0.002..0.002 rows=0 loops=1)

14. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.29..3.15 rows=1 width=557) (actual time=0.001..0.001 rows=0 loops=1)

15. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on overrides o_2 (cost=0.00..0.63 rows=1 width=549) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((set_type)::text = 'Group'::text)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using groups_pkey on groups g (cost=0.29..2.51 rows=1 width=8) (never executed)

  • Index Cond: (id = o_2.set_id)
  • Filter: ((workflow_state)::text <> 'deleted'::text)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using index_group_memberships_on_group_id_and_user_id on group_memberships gm (cost=0.42..0.72 rows=8 width=16) (never executed)

  • Index Cond: (group_id = g.id)
  • Filter: ((workflow_state)::text = 'accepted'::text)
18.          

CTE override_sections_students

19. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.86..162.34 rows=7 width=549) (actual time=0.001..0.001 rows=0 loops=1)

20. 0.001 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.42..3.29 rows=1 width=557) (actual time=0.001..0.001 rows=0 loops=1)

21. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on overrides o_3 (cost=0.00..0.63 rows=1 width=549) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((set_type)::text = 'CourseSection'::text)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using course_sections_pkey on course_sections s (cost=0.42..2.65 rows=1 width=8) (never executed)

  • Index Cond: (id = o_3.set_id)
  • Filter: ((workflow_state)::text <> 'deleted'::text)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using index_enrollments_on_course_section_id on enrollments e (cost=0.44..158.67 rows=37 width=16) (never executed)

  • Index Cond: (course_section_id = s.id)
  • Filter: (((type)::text = ANY ('{StudentEnrollment,StudentViewEnrollment}'::text[])) AND ((workflow_state)::text <> ALL ('{rejected,deleted,inactive}'::text[])))
24.          

CTE override_everyonelse_students

25. 10.224 1,961.971 ↓ 531.4 37,200 1

Nested Loop (cost=0.56..417.62 rows=70 width=24) (actual time=37.287..1,961.971 rows=37,200 loops=1)

26. 0.607 0.607 ↓ 620.0 620 1

CTE Scan on models a_1 (cost=0.00..0.02 rows=1 width=24) (actual time=0.002..0.607 rows=620 loops=1)

  • Filter: (only_visible_to_overrides IS NOT TRUE)
  • Rows Removed by Filter: 1
27. 1,951.140 1,951.140 ↑ 1.2 60 620

Index Scan using index_enrollments_on_course_id_and_user_id on enrollments e_1 (cost=0.56..416.73 rows=70 width=16) (actual time=0.064..3.147 rows=60 loops=620)

  • Index Cond: (course_id = a_1.context_id)
  • Filter: (((workflow_state)::text <> ALL ('{rejected,deleted}'::text[])) AND ((type)::text = ANY ('{StudentEnrollment,StudentViewEnrollment}'::text[])))
  • Rows Removed by Filter: 123
28.          

CTE override_all_students

29. 4.024 6,477.090 ↓ 459.3 37,200 1

Append (cost=0.00..2.43 rows=81 width=139) (actual time=4,524.124..6,477.090 rows=37,200 loops=1)

30. 4,486.824 4,486.824 ↓ 0.0 0 1

CTE Scan on override_adhoc_students (cost=0.00..0.02 rows=1 width=561) (actual time=4,486.824..4,486.824 rows=0 loops=1)

31. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on override_groups_students (cost=0.00..0.06 rows=3 width=561) (actual time=0.003..0.003 rows=0 loops=1)

32. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on override_sections_students (cost=0.00..0.14 rows=7 width=561) (actual time=0.002..0.002 rows=0 loops=1)

33. 8.798 1,986.237 ↓ 531.4 37,200 1

Subquery Scan on *SELECT* 4 (cost=0.00..2.10 rows=70 width=73) (actual time=37.294..1,986.237 rows=37,200 loops=1)

34. 1,977.439 1,977.439 ↓ 531.4 37,200 1

CTE Scan on override_everyonelse_students (cost=0.00..1.40 rows=70 width=73) (actual time=37.290..1,977.439 rows=37,200 loops=1)

35.          

CTE calculated_overrides

36. 6.273 6,533.828 ↓ 459.3 37,200 1

Unique (cost=4.19..4.80 rows=81 width=77) (actual time=6,520.298..6,533.828 rows=37,200 loops=1)

37. 34.335 6,527.555 ↓ 459.3 37,200 1

Sort (cost=4.19..4.39 rows=81 width=77) (actual time=6,520.297..6,527.555 rows=37,200 loops=1)

  • Sort Key: override_all_students.student_id, override_all_students.assignment_id, override_all_students.priority, override_all_students.due_at_overridden DESC, override_all_students.due_at DESC
  • Sort Method: quicksort Memory: 4930kB
38. 6,493.220 6,493.220 ↓ 459.3 37,200 1

CTE Scan on override_all_students (cost=0.00..1.62 rows=81 width=77) (actual time=4,524.125..6,493.220 rows=37,200 loops=1)

39.          

CTE course_and_account_grading_periods

40. 0.223 73.763 ↓ 2.0 4 1

Unique (cost=4.71..4.72 rows=2 width=48) (actual time=73.268..73.763 rows=4 loops=1)

41. 0.770 73.540 ↓ 1,242.0 2,484 1

Sort (cost=4.71..4.71 rows=2 width=48) (actual time=73.268..73.540 rows=2,484 loops=1)

  • Sort Key: gp.id
  • Sort Method: quicksort Memory: 291kB
42. 1.377 72.770 ↓ 1,242.0 2,484 1

Nested Loop (cost=0.70..4.70 rows=2 width=48) (actual time=48.856..72.770 rows=2,484 loops=1)

43. 1.076 44.690 ↓ 621.0 621 1

Nested Loop (cost=0.56..4.10 rows=1 width=24) (actual time=23.813..44.690 rows=621 loops=1)

  • Join Filter: ((gpg.course_id = c.id) OR (gpg.id = term.grading_period_group_id))
  • Rows Removed by Join Filter: 3105
44. 0.305 42.372 ↓ 621.0 621 1

Nested Loop (cost=0.56..2.84 rows=1 width=16) (actual time=23.797..42.372 rows=621 loops=1)

45. 0.241 41.446 ↓ 621.0 621 1

Nested Loop (cost=0.42..2.67 rows=1 width=16) (actual time=23.785..41.446 rows=621 loops=1)

46. 0.219 0.219 ↓ 621.0 621 1

CTE Scan on models a_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.219 rows=621 loops=1)

47. 40.986 40.986 ↑ 1.0 1 621

Index Scan using courses_pkey on courses c (cost=0.42..2.64 rows=1 width=16) (actual time=0.040..0.066 rows=1 loops=621)

  • Index Cond: (id = a_2.context_id)
48. 0.621 0.621 ↑ 1.0 1 621

Index Scan using enrollment_terms_pkey on enrollment_terms term (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=621)

  • Index Cond: (id = c.enrollment_term_id)
49. 1.242 1.242 ↑ 1.5 6 621

Seq Scan on grading_period_groups gpg (cost=0.00..1.12 rows=9 width=24) (actual time=0.001..0.002 rows=6 loops=621)

  • Filter: ((workflow_state)::text = 'active'::text)
  • Rows Removed by Filter: 4
50. 26.703 26.703 ↓ 2.0 4 621

Index Scan using index_grading_periods_on_grading_period_group_id on grading_periods gp (cost=0.14..0.56 rows=2 width=36) (actual time=0.042..0.043 rows=4 loops=621)

  • Index Cond: (grading_period_group_id = gpg.id)
  • Filter: ((workflow_state)::text = 'active'::text)
51.          

CTE applied_grading_periods

52. 0.001 73.775 ↑ 1.0 4 1

Append (cost=0.02..0.16 rows=4 width=48) (actual time=73.773..73.775 rows=4 loops=1)

53. 0.002 73.769 ↓ 0.0 0 1

Result (cost=0.02..0.06 rows=2 width=48) (actual time=73.769..73.769 rows=0 loops=1)

  • One-Time Filter: $19
54.          

Initplan (forResult)

55. 73.767 73.767 ↓ 0.0 0 1

CTE Scan on course_and_account_grading_periods (cost=0.00..0.04 rows=2 width=0) (actual time=73.767..73.767 rows=0 loops=1)

  • Filter: (course_id IS NOT NULL)
  • Rows Removed by Filter: 4
56. 0.000 0.000 ↓ 0.0 0

CTE Scan on course_and_account_grading_periods course_and_account_grading_periods_2 (cost=0.00..0.04 rows=2 width=48) (never executed)

  • Filter: (course_id IS NOT NULL)
57. 0.002 0.005 ↓ 2.0 4 1

Result (cost=0.02..0.06 rows=2 width=48) (actual time=0.003..0.005 rows=4 loops=1)

  • One-Time Filter: (NOT $20)
58.          

Initplan (forResult)

59. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on course_and_account_grading_periods course_and_account_grading_periods_1 (cost=0.00..0.04 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (course_id IS NOT NULL)
  • Rows Removed by Filter: 4
60. 0.002 0.002 ↓ 2.0 4 1

CTE Scan on course_and_account_grading_periods course_and_account_grading_periods_3 (cost=0.00..0.04 rows=2 width=48) (actual time=0.001..0.002 rows=4 loops=1)

  • Filter: (account_id IS NOT NULL)
61.          

CTE last_period

62. 0.002 0.011 ↑ 1.0 1 1

Limit (cost=0.10..0.10 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=1)

63. 0.008 0.009 ↑ 4.0 1 1

Sort (cost=0.10..0.11 rows=4 width=24) (actual time=0.009..0.009 rows=1 loops=1)

  • Sort Key: applied_grading_periods.end_date DESC
  • Sort Method: top-N heapsort Memory: 25kB
64. 0.001 0.001 ↑ 1.0 4 1

CTE Scan on applied_grading_periods (cost=0.00..0.08 rows=4 width=24) (actual time=0.000..0.001 rows=4 loops=1)

65.          

Initplan (forNested Loop Left Join)

66. 0.012 0.012 ↑ 1.0 1 1

CTE Scan on last_period (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)

67. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on last_period last_period_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

68. 6,540.411 6,540.411 ↓ 459.3 37,200 1

CTE Scan on calculated_overrides overrides (cost=0.00..1.62 rows=81 width=72) (actual time=6,520.300..6,540.411 rows=37,200 loops=1)

69. 74.400 74.400 ↑ 1.0 4 37,200

CTE Scan on applied_grading_periods periods (cost=0.00..0.08 rows=4 width=32) (actual time=0.002..0.002 rows=4 loops=37,200)

Planning time : 339.844 ms
Execution time : 6,669.984 ms