explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1qK2 : Optimization for: Optimization for: Optimization for: plan #iOwY; plan #QjvD; plan #vTE5

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 41.034 8,583.236 ↓ 6,076.0 6,076 1

Sort (cost=389,875.65..389,876.65 rows=1 width=288) (actual time=8,582.220..8,583.236 rows=6,076 loops=1)

  • Sort Key: i.event_type, i.due_date, i.title
  • Sort Method: quicksort Memory: 2078kB
2. 12.315 8,542.202 ↓ 6,076.0 6,076 1

Nested Loop (cost=390.66..389,873.65 rows=1 width=288) (actual time=4.375..8,542.202 rows=6,076 loops=1)

  • Join Filter: ((0 < (SubPlan 5)) OR (0 < (SubPlan 6)) OR (0 < (SubPlan 7)) OR (((SubPlan 8))::text <> ALL ('{S,U}'::text[])))
3. 7.171 1,129.825 ↓ 6,076.0 6,076 1

Nested Loop (cost=196.00..372,284.41 rows=1 width=256) (actual time=2.716..1,129.825 rows=6,076 loops=1)

4. 14.391 42.627 ↓ 306.9 7,059 1

Index Scan using eud_item_recipient_if1 on eud_item_recipient ir (cost=112.50..349,604.39 rows=23 width=36) (actual time=0.037..42.627 rows=7,059 loops=1)

  • Index Cond: (user_pk1 = '13864'::bigint)
  • Filter: ((uuid IS NULL) AND (status <> 'Z'::bpchar) AND (status <> 'D'::bpchar) AND ((SubPlan 3) = 'Y0'::text))
  • Rows Removed by Filter: 30
5.          

SubPlan (for Index Scan)

6. 28.236 28.236 ↑ 1.0 1 7,059

Index Scan using users_pk on users (cost=57.00..68.00 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=7,059)

  • Index Cond: (pk1 = ir.user_pk1)
7. 233.847 1,080.027 ↑ 1.0 1 7,059

Index Scan using eud_item_pk on eud_item i (cost=83.50..985.59 rows=1 width=228) (actual time=0.153..0.153 rows=1 loops=7,059)

  • Index Cond: (pk1 = ir.eud_item_pk1)
  • Filter: ((data_pending_ind = 'N'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone >= COALESCE(start_date, (clock_timestamp())::timestamp(3) without time zone)) AND ((clock_timestamp())::timestamp(3) without time zone <= COALESCE(end_date, (clock_timestamp())::timestamp(3) without time zone)) AND (((source_type = 'DF'::bpchar) AND ((event_type)::text = ANY ('{DF_ATTEMPT,DF_GR_ATTEMPT}'::text[]))) OR ((source_type = 'SU'::bpchar) AND ((event_type)::text = ANY ('{SU_AVAIL,SU_SUBMIT,DUE,OVERDUE}'::text[]))) OR ((source_type = 'JN'::bpchar) AND ((event_type)::text = ANY ('{JN_ATTEMPT,JN_GR_ATTEMPT}'::text[]))) OR ((source_type = 'QU'::bpchar) AND ((event_type)::text = 'QU_AVAIL'::text)) OR ((source_type = 'BL'::bpchar) AND ((event_type)::text = ANY ('{BL_ATTEMPT,BL_GR_ATTEMPT}'::text[]))) OR ((source_type = 'CM'::bpchar) AND ((event_type)::text = 'CM_RCVD'::text)) OR ((source_type = 'CO'::bpchar) AND ((event_type)::text = 'CO_AVAIL'::text)) OR ((source_type = 'AN'::bpchar) AND ((event_type)::text = 'AN_AVAIL'::text)) OR ((source_type = 'CR'::bpchar) AND ((event_type)::text = 'CR_AVAIL'::text)) OR ((source_type = 'DT'::bpchar) AND ((event_type)::text = ANY ('{DT_ATTEMPT,DT_GR_ATTEMPT}'::text[]))) OR ((source_type = 'SC'::bpchar) AND ((event_type)::text = ANY ('{SC_AVAIL,DUE,OVERDUE,SC_SUBMIT_LATE,SC_SUBMIT,SC_GRA_UPDATED}'::text[]))) OR ((source_type = 'TE'::bpchar) AND ((event_type)::text = ANY ('{TE_AVAIL,TE_AVAIL_RESEND,TE_SUBMIT_LATE,TE_SUBMIT,DUE,OVERDUE,TE_GT_SUBMIT_LATE,TE_GT_SUBMIT}'::text[]))) OR ((source_type = 'AS'::bpchar) AND ((event_type)::text = ANY ('{AS_AVAIL,AS_AVAIL_RESEND,AS_GA_ATTEMPT,AS_GA_AVAIL,AS_GA_AVAIL_RESEND,AS_GA_LATE_ATTEMPT,AS_LATE_ATTEMPT,AS_ATTEMPT,DUE,OVERDUE}'::text[]))) OR ((source_type = 'WK'::bpchar) AND ((event_type)::text = ANY ('{WK_ATTEMPT,WK_GR_ATTEMPT}'::text[]))) OR ((source_type = 'GB'::bpchar) AND ((event_type)::text = ANY ('{DUE,OVERDUE,GB_GRA_UPDATED,GB_GRA_CLEARED,GB_ATT_UPDATED,GB_NEEDS_RECON}'::text[])))) AND ((SubPlan 4) = 'Y0'::text) AND ((crs_contents_pk1 IS NULL) OR ((SubPlan 9) = 'Y'::bpchar) OR ((SubPlan 10) = 'Y'::bpchar)) AND ((has_course_entitlement(crsmain_pk1, ir.user_pk1, 'course.gradebook.MODIFY'::character varying) = 'Y'::bpchar) OR (NOT (SubPlan 12))))
  • Rows Removed by Filter: 0
8.          

SubPlan (for Index Scan)

9. 21.177 21.177 ↑ 1.0 1 7,059

Index Only Scan using course_users_ak2 on course_users (cost=85.00..96.50 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=7,059)

  • Index Cond: ((users_pk1 = ir.user_pk1) AND (crsmain_pk1 = i.crsmain_pk1))
  • Heap Fetches: 7026
10. 218.054 253.224 ↑ 1.0 1 7,034

Nested Loop (cost=168.50..239.51 rows=1 width=32) (actual time=0.035..0.036 rows=1 loops=7,034)

11. 21.102 21.102 ↑ 1.0 1 7,034

Index Scan using course_contents_pk on course_contents cc (cost=83.50..93.00 rows=1 width=78) (actual time=0.003..0.003 rows=1 loops=7,034)

  • Index Cond: (pk1 = i.crs_contents_pk1)
  • Filter: (crsmain_pk1 = i.crsmain_pk1)
12. 14.068 14.068 ↑ 1.0 1 7,034

Index Only Scan using course_users_ak2 on course_users cu_3 (cost=85.00..94.50 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=7,034)

  • Index Cond: ((users_pk1 = ir.user_pk1) AND (crsmain_pk1 = i.crsmain_pk1))
  • Heap Fetches: 7001
13. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..50.50 rows=1 width=32) (never executed)

14. 8.313 571.779 ↓ 0.0 0 7,059

Append (cost=167.50..943.20 rows=3 width=4) (actual time=0.081..0.081 rows=0 loops=7,059)

15. 1.255 381.186 ↓ 0.0 0 7,059

Nested Loop (cost=167.50..532.41 rows=1 width=4) (actual time=0.054..0.054 rows=0 loops=7,059)

16. 21.177 21.177 ↑ 1.5 2 7,059

Index Scan using x_course_contents_ie1 on x_course_contents xcc (cost=84.00..97.39 rows=3 width=8) (actual time=0.003..0.003 rows=2 loops=7,059)

  • Index Cond: (descendant_pk1 = i.crs_contents_pk1)
17. 358.754 358.754 ↓ 0.0 0 16,307

Index Scan using course_contents_pk on course_contents cc_1 (cost=83.50..144.50 rows=1 width=8) (actual time=0.022..0.022 rows=0 loops=16,307)

  • Index Cond: (pk1 = xcc.ancestor_pk1)
  • Filter: (content_is_available(pk1, ir.user_pk1, available_ind, partially_visible_ind, cnthndlr_handle, web_url_host, start_date, end_date, (clock_timestamp())::timestamp(3) without time zone) = 'N'::bpchar)
  • Rows Removed by Filter: 1
18. 127.596 127.596 ↓ 0.0 0 6,076

Index Scan using course_contents_pk on course_contents cc_2 (cost=83.50..144.50 rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=6,076)

  • Index Cond: (pk1 = i.crs_contents_pk1)
  • Filter: (content_is_available(pk1, ir.user_pk1, available_ind, partially_visible_ind, cnthndlr_handle, web_url_host, start_date, end_date, (clock_timestamp())::timestamp(3) without time zone) = 'N'::bpchar)
  • Rows Removed by Filter: 1
19. 0.000 54.684 ↓ 0.0 0 6,076

Nested Loop (cost=239.89..264.79 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=6,076)

20.          

Initplan (for Nested Loop)

21. 6.051 12.102 ↑ 1.0 1 6,051

Aggregate (cost=98.89..99.39 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=6,051)

22. 6.051 6.051 ↑ 1.5 2 6,051

Index Scan using x_course_contents_ie1 on x_course_contents (cost=84.00..97.39 rows=3 width=4) (actual time=0.001..0.001 rows=2 loops=6,051)

  • Index Cond: (descendant_pk1 = i.crs_contents_pk1)
23. 30.380 30.380 ↑ 1.0 1 6,076

Index Scan using x_course_contents_ie1 on x_course_contents xcc_1 (cost=84.00..98.89 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=6,076)

  • Index Cond: (descendant_pk1 = i.crs_contents_pk1)
  • Filter: (distance = $40)
  • Rows Removed by Filter: 1
24. 18.153 18.153 ↓ 0.0 0 6,051

Index Scan using course_toc_if1 on course_toc ct_3 (cost=56.50..66.00 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=6,051)

  • Index Cond: (course_contents_pk1 = xcc_1.ancestor_pk1)
  • Filter: (enabled_ind = 'N'::bpchar)
  • Rows Removed by Filter: 1
25. 24.185 24.304 ↑ 1.0 1 6,076

Index Scan using course_main_pk on course_main c (cost=194.66..198.18 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=6,076)

  • Index Cond: (pk1 = i.crsmain_pk1)
  • Filter: ((row_status = '0'::numeric) AND ((((honor_term_avail_ind = 'N'::bpchar) OR (honor_term_avail_ind IS NULL)) AND (available_ind = 'Y'::bpchar)) OR ((honor_term_avail_ind = 'Y'::bpchar) AND (hashed SubPlan 2))))
26.          

SubPlan (for Index Scan)

27. 0.044 0.119 ↓ 39.6 198 1

Nested Loop (cost=64.66..137.66 rows=5 width=8) (actual time=0.013..0.119 rows=198 loops=1)

28. 0.011 0.011 ↓ 16.0 16 1

Seq Scan on term trm (cost=0.00..50.00 rows=1 width=8) (actual time=0.004..0.011 rows=16 loops=1)

  • Filter: (available_ind = 'Y'::bpchar)
29. 0.032 0.064 ↑ 1.0 12 16

Bitmap Heap Scan on course_term ct (cost=64.66..81.66 rows=12 width=16) (actual time=0.003..0.004 rows=12 loops=16)

  • Recheck Cond: (term_pk1 = trm.pk1)
  • Heap Blocks: exact=30
30. 0.032 0.032 ↓ 1.1 13 16

Bitmap Index Scan on course_term_if1 (cost=0.00..64.06 rows=12 width=0) (actual time=0.002..0.002 rows=13 loops=16)

  • Index Cond: (term_pk1 = trm.pk1)
31.          

SubPlan (for Nested Loop)

32. 1,117.984 7,303.352 ↑ 1.0 1 6,076

Aggregate (cost=16,745.52..16,746.02 rows=1 width=8) (actual time=1.202..1.202 rows=1 loops=6,076)

33. 6,185.368 6,185.368 ↑ 1.0 3,119 6,076

Index Only Scan using eud_item_recipient_ie2 on eud_item_recipient (cost=112.50..15,165.02 rows=3,161 width=0) (actual time=0.017..1.018 rows=3,119 loops=6,076)

  • Index Cond: ((eud_item_pk1 = i.pk1) AND (type = 'R'::bpchar) AND (uuid IS NULL))
  • Filter: (status <> 'D'::bpchar)
  • Rows Removed by Filter: 0
  • Heap Fetches: 18464683
34. 6.076 6.076 ↑ 1.0 1 6,076

Aggregate (cost=108.50..109.00 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=6,076)

35. 0.000 0.000 ↓ 0.0 0 6,076

Result (cost=85.00..108.00 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=6,076)

  • One-Time Filter: ((c.available_ind = 'Y'::bpchar) AND ((c.honor_term_avail_ind = 'N'::bpchar) OR (c.honor_term_avail_ind IS NULL)) AND (c.pk1 = i.crsmain_pk1))
36. 0.092 0.092 ↑ 1.0 1 46

Index Scan using course_users_ak2 on course_users cu (cost=85.00..108.00 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=46)

  • Index Cond: ((users_pk1 = ir.user_pk1) AND (crsmain_pk1 = i.crsmain_pk1))
  • Filter: ((c.duration = 'C'::bpchar) OR ((c.duration = 'R'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone >= COALESCE(c.start_date, (clock_timestamp())::timestamp(3) without time zone)) AND ((clock_timestamp())::timestamp(3) without time zone <= COALESCE(c.end_date, (clock_timestamp())::timestamp(3) without time zone))) OR ((c.duration = 'D'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone <= (enrollment_date + (((c.days_of_use)::text || ' days'::text))::interval))))
37. 6.030 66.330 ↑ 1.0 1 6,030

Aggregate (cost=225.01..225.51 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=6,030)

38. 6.030 60.300 ↑ 1.0 1 6,030

Result (cost=168.00..224.51 rows=1 width=0) (actual time=0.009..0.010 rows=1 loops=6,030)

  • One-Time Filter: ((c.honor_term_avail_ind = 'Y'::bpchar) AND (c.pk1 = i.crsmain_pk1))
39. 18.090 54.270 ↑ 1.0 1 6,030

Nested Loop (cost=168.00..224.51 rows=1 width=0) (actual time=0.008..0.009 rows=1 loops=6,030)

  • Join Filter: ((c.duration = 'C'::bpchar) OR ((c.duration = 'R'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone >= COALESCE(c.start_date, (clock_timestamp())::timestamp(3) without time zone)) AND ((clock_timestamp())::timestamp(3) without time zone <= COALESCE(c.end_date, (clock_timestamp())::timestamp(3) without time zone))) OR ((c.duration = 'D'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone <= (cu_1.enrollment_date + (((c.days_of_use)::text || ' days'::text))::interval))) OR ((c.duration = 'T'::bpchar) AND ((trm_1.duration = 'C'::bpchar) OR ((trm_1.duration = 'R'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone >= COALESCE(trm_1.start_date, (clock_timestamp())::timestamp(3) without time zone)) AND ((clock_timestamp())::timestamp(3) without time zone <= COALESCE(trm_1.end_date, (clock_timestamp())::timestamp(3) without time zone))) OR ((trm_1.duration = 'D'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone <= (cu_1.enrollment_date + (((trm_1.days_of_use)::text || ' days'::text))::interval))))))
40. 0.000 24.120 ↑ 1.0 1 6,030

Nested Loop (cost=82.00..101.01 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=6,030)

41. 12.060 12.060 ↑ 1.0 1 6,030

Index Scan using course_term_if2 on course_term ct_1 (cost=54.00..63.00 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=6,030)

  • Index Cond: (crsmain_pk1 = i.crsmain_pk1)
42. 12.060 12.060 ↑ 1.0 1 6,030

Index Scan using term_pk on term trm_1 (cost=28.00..37.51 rows=1 width=46) (actual time=0.002..0.002 rows=1 loops=6,030)

  • Index Cond: (pk1 = ct_1.term_pk1)
  • Filter: (available_ind = 'Y'::bpchar)
43. 12.060 12.060 ↑ 1.0 1 6,030

Index Scan using course_users_ak2 on course_users cu_1 (cost=85.00..94.50 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=6,030)

  • Index Cond: ((users_pk1 = ir.user_pk1) AND (crsmain_pk1 = i.crsmain_pk1))
44. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=213.01..213.51 rows=1 width=8) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Result (cost=169.00..212.51 rows=1 width=0) (never executed)

  • One-Time Filter: ((c.available_ind = 'Y'::bpchar) AND ((c.honor_term_avail_ind = 'N'::bpchar) OR (c.honor_term_avail_ind IS NULL)) AND (c.duration = 'T'::bpchar) AND (c.pk1 = i.crsmain_pk1))
46. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=169.00..212.51 rows=1 width=0) (never executed)

  • Join Filter: ((trm_2.duration = 'C'::bpchar) OR ((trm_2.duration = 'R'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone >= COALESCE(trm_2.start_date, (clock_timestamp())::timestamp(3) without time zone)) AND ((clock_timestamp())::timestamp(3) without time zone <= COALESCE(trm_2.end_date, (clock_timestamp())::timestamp(3) without time zone))) OR ((trm_2.duration = 'D'::bpchar) AND ((clock_timestamp())::timestamp(3) without time zone <= (cu_2.enrollment_date + (((trm_2.days_of_use)::text || ' days'::text))::interval))))
47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=82.00..102.01 rows=1 width=46) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Index Scan using course_term_if2 on course_term ct_2 (cost=54.00..63.00 rows=1 width=16) (never executed)

  • Index Cond: (crsmain_pk1 = i.crsmain_pk1)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using term_pk on term trm_2 (cost=28.00..38.51 rows=1 width=46) (never executed)

  • Index Cond: (pk1 = ct_2.term_pk1)
  • Filter: ((duration = 'C'::bpchar) OR (duration = 'R'::bpchar) OR (duration = 'D'::bpchar))
50. 0.000 0.000 ↓ 0.0 0

Index Scan using course_users_ak2 on course_users cu_2 (cost=85.00..94.50 rows=1 width=16) (never executed)

  • Index Cond: ((users_pk1 = ir.user_pk1) AND (crsmain_pk1 = i.crsmain_pk1))
51. 0.000 0.000 ↓ 0.0 0

Index Only Scan using course_users_ak2 on course_users course_users_1 (cost=85.00..94.50 rows=1 width=2) (never executed)

  • Index Cond: ((users_pk1 = ir.user_pk1) AND (crsmain_pk1 = i.crsmain_pk1))
  • Heap Fetches: 0
Planning time : 5.407 ms
Execution time : 8,585.448 ms