explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mkeg

Settings
# exclusive inclusive rows x rows loops node
1. 14.932 12,769.609 ↓ 2,143.0 6,429 1

Sort (cost=97,187.10..97,187.11 rows=3 width=164) (actual time=12,769.156..12,769.609 rows=6,429 loops=1)

  • Sort Key: ((count(DISTINCT dedupe_teams.userhandle))::integer) DESC NULLS LAST, (max((min(dedupe_teams.coursename))))
  • Sort Method: quicksort Memory: 1,827kB
2.          

CTE members_and_teams

3. 55.970 1,609.632 ↓ 146,448.0 146,448 1

Nested Loop (cost=1,001.11..31,263.67 rows=1 width=179) (actual time=48.971..1,609.632 rows=146,448 loops=1)

4. 281.246 516.614 ↓ 129,631.0 129,631 1

Gather (cost=1,000.55..31,255.29 rows=1 width=107) (actual time=48.939..516.614 rows=129,631 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 6.437 235.368 ↓ 43,210.0 43,210 3 / 3

Nested Loop (cost=0.56..30,255.19 rows=1 width=107) (actual time=16.525..235.368 rows=43,210 loops=3)

6. 7.523 7.523 ↓ 5.5 11 3 / 3

Parallel Seq Scan on plan_teams pt (cost=0.00..4,348.88 rows=2 width=71) (actual time=1.731..7.523 rows=11 loops=3)

  • Filter: (plan_id = 'jpmctrial092315'::text)
  • Rows Removed by Filter: 67,003
7. 221.408 221.408 ↓ 3,813.0 3,813 34 / 3

Index Only Scan using member_teams_pkey on member_teams mt (cost=0.56..12,953.15 rows=1 width=97) (actual time=4.929..19.536 rows=3,813 loops=34)

  • Index Cond: ((plan_id = 'jpmctrial092315'::text) AND (team_id = pt.team_id))
  • Filter: (team_id = ANY ('{fd8f26a4-567c-43dd-91e5-9e12f94def34,9dabe225-61d4-43b3-b244-2b0973c51925,831821ce-4cfd-4331-8632-3192d17289cf,db43a12c-1c30-4ec0-805c-1a3689b56fef,211fdea1-527c-4696-8a2e-65af7283059e,90cd807a-2877-4e88-a762-2f93eba44a66,d5a9edc5-5616-4891-af4f-cbf85ed56d90,4be0bd36-4e25-4177-8b92-0e5e22d34d8d,9bf9ed58-ec08-46bd-a1a6-f4cc77e06221,f267e613-39f6-403c-91e0-56b089f71077,f62ad315-011b-4727-a578-c855315d8b10,d438e03f-a78d-4506-967b-ac9147ddeade,1f7736ff-d723-46c7-9951-1ef9b0af9066,cd4b8edf-95e5-4646-883d-a64d85346179,119337da-158e-4698-8c30-062f4e1dbe96,c333a56c-1728-40e6-9e44-cfa9143d5a1f,e058ce7e-65b6-4f6a-bcac-116254233a04,5155b70c-5aa5-40b4-816b-4a1d69bbb921,915801c2-64df-4521-bf13-9dccddfdce05,b9a12e47-5bb0-46b0-b266-fc36ed9f61e4,96b4f57c-aca2-443f-aae1-2c45e463810f,5b346427-ba79-4547-9682-4bc3117a4f73,a2237fb6-1e5d-491f-91b4-98714d573704,36864165-9c8e-4945-99e0-a142da749dc4,053fe40f-7066-4183-944f-1e7ad52e2008,aeb04793-d78b-46e8-9845-7446d3230c92,8ca4ea53-fa09-455b-a981-517eff27c13a,93889e7f-8b16-46d1-a3f3-f4adc980919f,0066e62a-9aee-45d2-b650-07a20b5d62e3,5abab5e5-4087-419b-b8c0-a221dd1f7b62,38788e2a-4352-425f-9447-1939eaf3486d,dfab1f18-3d5b-428d-8de2-031882a19c60,45878f04-2500-4870-86e3-3e0c9c186132,ac908666-a3d2-4847-813c-ccd257161f0c}'::text[]))
  • Heap Fetches: 287
8. 1,037.048 1,037.048 ↑ 1.0 1 129,631

Index Scan using members_pkey on members m_3 (cost=0.56..8.37 rows=1 width=132) (actual time=0.007..0.008 rows=1 loops=129,631)

  • Index Cond: ((plan_id = 'jpmctrial092315'::text) AND (user_handle = mt.user_handle))
9. 125.055 12,754.677 ↓ 2,143.0 6,429 1

GroupAggregate (cost=65,923.19..65,923.40 rows=3 width=164) (actual time=12,499.887..12,754.677 rows=6,429 loops=1)

  • Group Key: dedupe_teams.courseslug
10. 118.073 12,629.622 ↓ 66,062.0 198,186 1

GroupAggregate (cost=65,923.19..65,923.28 rows=3 width=192) (actual time=12,499.857..12,629.622 rows=198,186 loops=1)

  • Group Key: dedupe_teams.courseslug, dedupe_teams.userhandle
11. 583.184 12,511.549 ↓ 66,062.7 198,188 1

Sort (cost=65,923.19..65,923.19 rows=3 width=192) (actual time=12,499.837..12,511.549 rows=198,188 loops=1)

  • Sort Key: dedupe_teams.courseslug, dedupe_teams.userhandle
  • Sort Method: quicksort Memory: 58,798kB
12. 19.596 11,928.365 ↓ 66,062.7 198,188 1

Subquery Scan on dedupe_teams (cost=65,923.01..65,923.16 rows=3 width=192) (actual time=11,628.808..11,928.365 rows=198,188 loops=1)

13. 245.437 11,908.769 ↓ 66,062.7 198,188 1

GroupAggregate (cost=65,923.01..65,923.13 rows=3 width=460) (actual time=11,628.807..11,908.769 rows=198,188 loops=1)

  • Group Key: d.plan_id, d.content_id, d.user_handle, d.team_name
14. 914.574 11,663.332 ↓ 66,062.7 198,188 1

Sort (cost=65,923.01..65,923.01 rows=3 width=260) (actual time=11,628.779..11,663.332 rows=198,188 loops=1)

  • Sort Key: d.plan_id, d.content_id, d.user_handle, d.team_name
  • Sort Method: quicksort Memory: 65,271kB
15. 19.854 10,748.758 ↓ 66,062.7 198,188 1

Subquery Scan on d (cost=65,922.92..65,922.98 rows=3 width=260) (actual time=10,610.296..10,748.758 rows=198,188 loops=1)

16. 361.815 10,728.904 ↓ 66,062.7 198,188 1

HashAggregate (cost=65,922.92..65,922.95 rows=3 width=544) (actual time=10,610.296..10,728.904 rows=198,188 loops=1)

  • Group Key: ""*SELECT* 1"".plan_id, ""*SELECT* 1"".user_handle, ""*SELECT* 1"".content_id, ""*SELECT* 1"".display_name, ""*SELECT* 1"".team_name, ""*SELECT* 1"".first_name, ""*SELECT* 1"".last_name, ""*SELECT* 1"".email, ""*SELECT* 1"".note, ""*SELECT* 1"".start_date, ('VideoCourse'::text), ""*SELECT* 1"".course_guid, (NULL::text), (NULL::text), ""*SELECT* 1"".slug, ""*SELECT* 1"".title, ""*SELECT* 1"".duration, (NULL::text), ""*SELECT* 1"".view_duration, ""*SELECT* 1"".percent_complete, ""*SELECT* 1"".activity_at
17. 15.864 10,367.089 ↓ 66,062.7 198,188 1

Append (cost=26.22..65,922.76 rows=3 width=544) (actual time=8,382.952..10,367.089 rows=198,188 loops=1)

18. 26.394 8,888.838 ↓ 195,895.0 195,895 1

Subquery Scan on *SELECT* 1 (cost=26.22..26.29 rows=1 width=539) (actual time=8,382.952..8,888.838 rows=195,895 loops=1)

19. 444.760 8,862.444 ↓ 195,895.0 195,895 1

GroupAggregate (cost=26.22..26.28 rows=1 width=571) (actual time=8,382.951..8,862.444 rows=195,895 loops=1)

  • Group Key: dcv.plan_id, dcv.user_handle, dcv.course_guid, m.team_id
20. 926.328 8,417.684 ↓ 380,989.0 380,989 1

Sort (cost=26.22..26.23 rows=1 width=413) (actual time=8,382.927..8,417.684 rows=380,989 loops=1)

  • Sort Key: dcv.user_handle, dcv.course_guid, m.team_id
  • Sort Method: quicksort Memory: 207,842kB
21. 461.224 7,491.356 ↓ 380,989.0 380,989 1

Nested Loop Left Join (cost=2.08..26.21 rows=1 width=413) (actual time=49.091..7,491.356 rows=380,989 loops=1)

22. 75.013 4,363.209 ↓ 380,989.0 380,989 1

Nested Loop Left Join (cost=1.39..17.50 rows=1 width=489) (actual time=49.063..4,363.209 rows=380,989 loops=1)

23. 209.587 3,526.218 ↓ 380,989.0 380,989 1

Nested Loop (cost=1.11..17.19 rows=1 width=378) (actual time=49.051..3,526.218 rows=380,989 loops=1)

24. 27.190 1,761.059 ↓ 129,631.0 129,631 1

Nested Loop (cost=0.42..8.47 rows=1 width=308) (actual time=48.997..1,761.059 rows=129,631 loops=1)

25. 0.020 0.020 ↑ 1.0 1 1

Index Scan using plans_pkey on plans p (cost=0.42..8.44 rows=1 width=44) (actual time=0.019..0.020 rows=1 loops=1)

  • Index Cond: (plan_id = 'jpmctrial092315'::text)
26. 1,733.849 1,733.849 ↓ 129,631.0 129,631 1

CTE Scan on members_and_teams m (cost=0.00..0.02 rows=1 width=264) (actual time=48.976..1,733.849 rows=129,631 loops=1)

  • Filter: (is_latest AND (plan_id = 'jpmctrial092315'::text))
  • Rows Removed by Filter: 16,817
27. 1,555.572 1,555.572 ↓ 3.0 3 129,631

Index Scan using daily_course_views_plan_id_user_handle_rollup_date_idx on daily_course_views dcv (cost=0.69..8.71 rows=1 width=95) (actual time=0.009..0.012 rows=3 loops=129,631)

  • Index Cond: ((plan_id = 'jpmctrial092315'::text) AND (user_handle = m.user_handle) AND (rollup_date >= '2020-01-01'::date) AND (rollup_date <= '2021-01-01'::date))
28. 761.978 761.978 ↑ 1.0 1 380,989

Index Scan using courses_pkey on courses c (cost=0.29..0.31 rows=1 width=111) (actual time=0.002..0.002 rows=1 loops=380,989)

  • Index Cond: (dcv.course_guid = course_guid)
29. 2,666.923 2,666.923 ↑ 1.0 1 380,989

Index Only Scan using course_progress_plnid_userhandle_courseguid_percentcomplete_idx on course_progress cp (cost=0.69..8.71 rows=1 width=79) (actual time=0.007..0.007 rows=1 loops=380,989)

  • Index Cond: ((plan_id = m.plan_id) AND (plan_id = 'jpmctrial092315'::text) AND (user_handle = m.user_handle) AND (course_guid = c.course_guid))
  • Heap Fetches: 360,124
30. 0.041 677.564 ↓ 279.0 279 1

Subquery Scan on *SELECT* 2 (cost=18,859.37..18,859.44 rows=1 width=545) (actual time=677.072..677.564 rows=279 loops=1)

31. 0.447 677.523 ↓ 279.0 279 1

GroupAggregate (cost=18,859.37..18,859.43 rows=1 width=577) (actual time=677.071..677.523 rows=279 loops=1)

  • Group Key: pp.plan_id, pp.user_handle, pp.project_id, m_1.team_id
32. 0.678 677.076 ↓ 378.0 378 1

Sort (cost=18,859.37..18,859.38 rows=1 width=474) (actual time=677.056..677.076 rows=378 loops=1)

  • Sort Key: pp.plan_id, pp.user_handle, pp.project_id, m_1.team_id
  • Sort Method: quicksort Memory: 219kB
33. 0.318 676.398 ↓ 378.0 378 1

Nested Loop Left Join (cost=18,842.25..18,859.36 rows=1 width=474) (actual time=632.704..676.398 rows=378 loops=1)

  • Join Filter: (proj.id = pp.project_id)
  • Rows Removed by Join Filter: 125
34. 0.344 672.678 ↓ 378.0 378 1

Nested Loop Left Join (cost=18,841.84..18,850.91 rows=1 width=474) (actual time=632.680..672.678 rows=378 loops=1)

35. 0.299 671.578 ↓ 378.0 378 1

Nested Loop (cost=18,841.57..18,849.89 rows=1 width=324) (actual time=632.661..671.578 rows=378 loops=1)

36. 30.929 669.767 ↓ 378.0 378 1

Merge Join (cost=18,841.15..18,841.44 rows=1 width=328) (actual time=632.633..669.767 rows=378 loops=1)

  • Merge Cond: (m_1.user_handle = dtc.user_handle)
37. 592.354 622.976 ↓ 129,598.0 129,598 1

Sort (cost=0.03..0.04 rows=1 width=264) (actual time=616.758..622.976 rows=129,598 loops=1)

  • Sort Key: m_1.user_handle
  • Sort Method: quicksort Memory: 37,505kB
38. 30.622 30.622 ↓ 129,631.0 129,631 1

CTE Scan on members_and_teams m_1 (cost=0.00..0.02 rows=1 width=264) (actual time=0.003..30.622 rows=129,631 loops=1)

  • Filter: (is_latest AND (plan_id = 'jpmctrial092315'::text))
  • Rows Removed by Filter: 16,817
39. 0.455 15.862 ↓ 6.9 378 1

Sort (cost=18,841.12..18,841.26 rows=55 width=100) (actual time=15.823..15.862 rows=378 loops=1)

  • Sort Key: dtc.user_handle
  • Sort Method: quicksort Memory: 78kB
40. 0.034 15.407 ↓ 6.9 378 1

Subquery Scan on dtc (cost=18,838.15..18,839.53 rows=55 width=100) (actual time=14.714..15.407 rows=378 loops=1)

41. 0.520 15.373 ↓ 6.9 378 1

Group (cost=18,838.15..18,838.98 rows=55 width=100) (actual time=14.713..15.373 rows=378 loops=1)

  • Group Key: task_completion.plan_id, task_completion.project_id, task_completion.user_handle, ((task_completion.completed_at)::date)
42. 3.177 14.853 ↓ 59.5 3,273 1

Sort (cost=18,838.15..18,838.29 rows=55 width=100) (actual time=14.711..14.853 rows=3,273 loops=1)

  • Sort Key: task_completion.project_id, task_completion.user_handle, ((task_completion.completed_at)::date)
  • Sort Method: quicksort Memory: 557kB
43. 10.625 11.676 ↓ 59.5 3,273 1

Bitmap Heap Scan on task_completion (cost=407.58..18,836.56 rows=55 width=100) (actual time=2.109..11.676 rows=3,273 loops=1)

  • Recheck Cond: (plan_id = 'jpmctrial092315'::text)
  • Filter: (((completed_at)::date >= '2020-01-01'::date) AND ((completed_at)::date <= '2021-01-01'::date))
  • Rows Removed by Filter: 7,838
  • Heap Blocks: exact=4,622
44. 1.051 1.051 ↓ 1.0 11,118 1

Bitmap Index Scan on task_completion_plan_id_completed_at_idx (cost=0.00..407.56 rows=11,085 width=0) (actual time=1.051..1.051 rows=11,118 loops=1)

  • Index Cond: (plan_id = 'jpmctrial092315'::text)
45. 1.512 1.512 ↑ 1.0 1 378

Index Scan using plans_pkey on plans p_1 (cost=0.42..8.44 rows=1 width=44) (actual time=0.004..0.004 rows=1 loops=378)

  • Index Cond: (plan_id = 'jpmctrial092315'::text)
46. 0.756 0.756 ↑ 1.0 1 378

Index Scan using projects_pkey on projects proj (cost=0.27..1.01 rows=1 width=150) (actual time=0.002..0.002 rows=1 loops=378)

  • Index Cond: (dtc.project_id = id)
47. 3.402 3.402 ↑ 1.0 1 378

Index Scan using project_progress_pkey on project_progress pp (cost=0.41..8.44 rows=1 width=101) (actual time=0.005..0.009 rows=1 loops=378)

  • Index Cond: ((m_1.plan_id = plan_id) AND (plan_id = 'jpmctrial092315'::text) AND (m_1.user_handle = user_handle))
48. 0.297 784.823 ↓ 2,014.0 2,014 1

Subquery Scan on *SELECT* 3 (cost=47,036.95..47,037.03 rows=1 width=540) (actual time=780.400..784.823 rows=2,014 loops=1)

49. 3.968 784.526 ↓ 2,014.0 2,014 1

GroupAggregate (cost=47,036.95..47,037.02 rows=1 width=588) (actual time=780.398..784.526 rows=2,014 loops=1)

  • Group Key: iccp.plan_id, iccp.user_handle, iccp.interactive_course_id, m_2.team_id
50. 5.700 780.558 ↓ 2,833.0 2,833 1

Sort (cost=47,036.95..47,036.96 rows=1 width=399) (actual time=780.378..780.558 rows=2,833 loops=1)

  • Sort Key: iccp.plan_id, iccp.user_handle, iccp.interactive_course_id, m_2.team_id
  • Sort Method: quicksort Memory: 1,549kB
51. 2.088 774.858 ↓ 2,833.0 2,833 1

Nested Loop Left Join (cost=47,019.54..47,036.94 rows=1 width=399) (actual time=694.965..774.858 rows=2,833 loops=1)

  • Join Filter: (iccp.interactive_course_id = ic.interactive_course_id)
  • Rows Removed by Join Filter: 3,207
52. 1.563 755.772 ↓ 2,833.0 2,833 1

Nested Loop Left Join (cost=47,019.12..47,028.49 rows=1 width=383) (actual time=694.940..755.772 rows=2,833 loops=1)

53. 2.234 751.376 ↓ 2,833.0 2,833 1

Nested Loop (cost=47,018.98..47,028.24 rows=1 width=303) (actual time=694.925..751.376 rows=2,833 loops=1)

54. 35.954 737.810 ↓ 2,833.0 2,833 1

Merge Join (cost=47,018.56..47,019.80 rows=1 width=308) (actual time=694.888..737.810 rows=2,833 loops=1)

  • Merge Cond: (m_2.user_handle = icpa.user_handle)
55. 585.646 616.223 ↓ 129,617.0 129,617 1

Sort (cost=0.03..0.04 rows=1 width=264) (actual time=609.517..616.223 rows=129,617 loops=1)

  • Sort Key: m_2.user_handle
  • Sort Method: quicksort Memory: 37,505kB
56. 30.577 30.577 ↓ 129,631.0 129,631 1

CTE Scan on members_and_teams m_2 (cost=0.00..0.02 rows=1 width=264) (actual time=0.002..30.577 rows=129,631 loops=1)

  • Filter: (is_latest AND (plan_id = 'jpmctrial092315'::text))
  • Rows Removed by Filter: 16,817
57. 3.822 85.633 ↓ 11.6 2,833 1

Sort (cost=47,018.53..47,019.14 rows=244 width=80) (actual time=85.331..85.633 rows=2,833 loops=1)

  • Sort Key: icpa.user_handle
  • Sort Method: quicksort Memory: 494kB
58. 0.250 81.811 ↓ 11.6 2,833 1

Subquery Scan on icpa (cost=47,002.75..47,008.85 rows=244 width=80) (actual time=77.213..81.811 rows=2,833 loops=1)

59. 3.038 81.561 ↓ 11.6 2,833 1

Group (cost=47,002.75..47,006.41 rows=244 width=80) (actual time=77.212..81.561 rows=2,833 loops=1)

  • Group Key: page_activity.plan_id, page_activity.interactive_course_id, page_activity.user_handle, ((page_activity.activity_at)::date)
60. 27.474 78.523 ↓ 78.9 19,241 1

Sort (cost=47,002.75..47,003.36 rows=244 width=80) (actual time=77.209..78.523 rows=19,241 loops=1)

  • Sort Key: page_activity.interactive_course_id, page_activity.user_handle, ((page_activity.activity_at)::date)
  • Sort Method: quicksort Memory: 3,473kB
61. 46.212 51.049 ↓ 78.9 19,241 1

Bitmap Heap Scan on page_activity (cost=1,751.09..46,993.08 rows=244 width=80) (actual time=8.192..51.049 rows=19,241 loops=1)

  • Recheck Cond: (plan_id = 'jpmctrial092315'::text)
  • Filter: (((activity_at)::date >= '2020-01-01'::date) AND ((activity_at)::date <= '2021-01-01'::date))
  • Rows Removed by Filter: 33,825
  • Heap Blocks: exact=22,428
62. 4.837 4.837 ↓ 1.1 53,066 1

Bitmap Index Scan on page_activity_plan_id_activity_at_idx (cost=0.00..1,751.03 rows=48,864 width=0) (actual time=4.837..4.837 rows=53,066 loops=1)

  • Index Cond: (plan_id = 'jpmctrial092315'::text)
63. 11.332 11.332 ↑ 1.0 1 2,833

Index Scan using plans_pkey on plans p_2 (cost=0.42..8.44 rows=1 width=44) (actual time=0.004..0.004 rows=1 loops=2,833)

  • Index Cond: (plan_id = 'jpmctrial092315'::text)
64. 2.833 2.833 ↑ 1.0 1 2,833

Index Scan using interactive_courses_pkey on interactive_courses ic (cost=0.14..0.24 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=2,833)

  • Index Cond: (interactive_course_id = icpa.interactive_course_id)
65. 16.998 16.998 ↓ 2.0 2 2,833

Index Scan using course_progress_pkey on course_progress iccp (cost=0.42..8.44 rows=1 width=80) (actual time=0.005..0.006 rows=2 loops=2,833)

  • Index Cond: ((plan_id = m_2.plan_id) AND (plan_id = 'jpmctrial092315'::text) AND (user_handle = m_2.user_handle))
Planning time : 4.171 ms
Execution time : 12,797.154 ms