explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qScJ : prod members page

Settings
# exclusive inclusive rows x rows loops node
1. 0.088 71,433.230 ↓ 14.0 14 1

Sort (cost=3,116.72..3,116.72 rows=1 width=75) (actual time=71,433.229..71,433.230 rows=14 loops=1)

  • Sort Key: users.first_name, users.last_name
  • Sort Method: quicksort Memory: 26kB
2.          

CTE learner_task_count

3. 0.279 71,432.964 ↓ 14.0 14 1

GroupAggregate (cost=3,099.82..3,099.94 rows=1 width=57) (actual time=71,432.632..71,432.964 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 0.996 71,432.685 ↓ 532.0 532 1

Sort (cost=3,099.82..3,099.83 rows=1 width=46) (actual time=71,432.589..71,432.685 rows=532 loops=1)

  • Sort Key: program_memberships.user_id
  • Sort Method: quicksort Memory: 66kB
5. 5.771 71,431.689 ↓ 532.0 532 1

Nested Loop Left Join (cost=2,213.38..3,099.81 rows=1 width=46) (actual time=17.858..71,431.689 rows=532 loops=1)

  • Join Filter: ((program_memberships_1.user_id = program_memberships.user_id) AND ((program_memberships_1.program_id)::integer = program_memberships.program_id))
  • Rows Removed by Join Filter: 6,916
6. 0.038 0.060 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships (cost=4.44..26.96 rows=1 width=16) (actual time=0.040..0.060 rows=14 loops=1)

  • Recheck Cond: (program_id = 202)
  • Filter: ((learner IS TRUE) AND (user_id = ANY ('{1185,585,582,586,583,592,590,594,1169,1186,1183,1184,1178,1188,1182,1177,1181,1179,1180,1176,1187}'::bigint[])))
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=2
7. 0.022 0.022 ↑ 1.0 21 1

Bitmap Index Scan on index_program_memberships_on_program_and_user (cost=0.00..4.43 rows=21 width=0) (actual time=0.022..0.022 rows=21 loops=1)

  • Index Cond: (program_id = 202)
8. 56.742 71,425.858 ↓ 14.0 532 14

Nested Loop Left Join (cost=2,208.94..3,072.19 rows=38 width=54) (actual time=11.480..5,101.847 rows=532 loops=14)

  • Join Filter: (tasks_1.id = tasks.id)
  • Rows Removed by Join Filter: 19,684
9. 21.886 71,331.876 ↓ 14.0 532 14

Hash Left Join (cost=2,151.83..2,862.52 rows=38 width=84) (actual time=11.388..5,095.134 rows=532 loops=14)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
10. 440.762 71,308.846 ↓ 14.0 532 14

Nested Loop Left Join (cost=1,868.81..2,578.83 rows=38 width=431) (actual time=11.300..5,093.489 rows=532 loops=14)

11. 8.414 37.604 ↓ 14.0 532 14

Nested Loop (cost=4.86..92.25 rows=38 width=52) (actual time=0.033..2.686 rows=532 loops=14)

12. 0.238 1.162 ↓ 14.0 14 14

Nested Loop (cost=4.58..35.23 rows=1 width=39) (actual time=0.024..0.083 rows=14 loops=14)

13. 0.098 0.098 ↑ 1.0 1 14

Index Scan using programs_pkey on programs (cost=0.14..8.16 rows=1 width=19) (actual time=0.006..0.007 rows=1 loops=14)

  • Index Cond: (id = 202)
14. 0.700 0.826 ↓ 14.0 14 14

Bitmap Heap Scan on program_memberships program_memberships_1 (cost=4.44..27.06 rows=1 width=24) (actual time=0.014..0.059 rows=14 loops=14)

  • Recheck Cond: (program_id = 202)
  • Filter: (learner AND ((program_id)::integer = 202) AND (user_id = ANY ('{1185,585,582,586,583,592,590,594,1169,1186,1183,1184,1178,1188,1182,1177,1181,1179,1180,1176,1187}'::bigint[])))
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=28
15. 0.126 0.126 ↑ 1.0 21 14

Bitmap Index Scan on index_program_memberships_on_program_and_user (cost=0.00..4.43 rows=21 width=0) (actual time=0.009..0.009 rows=21 loops=14)

  • Index Cond: (program_id = 202)
16. 28.028 28.028 ↑ 1.0 38 196

Index Scan using index_tasks_on_program_id on tasks (cost=0.28..56.64 rows=38 width=17) (actual time=0.007..0.143 rows=38 loops=196)

  • Index Cond: (program_id = 202)
17. 534.240 70,830.480 ↑ 1.0 1 7,448

Hash Join (cost=1,863.95..1,880.32 rows=1 width=387) (actual time=8.516..9.510 rows=1 loops=7,448)

  • Hash Cond: ((task_interactions_1.learner_id = task_interactions.learner_id) AND (task_interactions_1.task_id = task_interactions.task_id) AND ((max(task_interactions_1.created_at)) = task_interactions.created_at))
18. 10,548.314 70,020.664 ↑ 1.1 367 5,138

GroupAggregate (cost=1,855.49..1,863.44 rows=396 width=16) (actual time=10.723..13.628 rows=367 loops=5,138)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
19. 21,564.186 59,472.350 ↓ 22.8 9,098 5,138

Sort (cost=1,855.49..1,856.48 rows=399 width=16) (actual time=10.716..11.575 rows=9,098 loops=5,138)

  • Sort Key: task_interactions_1.learner_id, task_interactions_1.task_id
20. 28,171.654 37,908.164 ↓ 22.8 9,098 5,138

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=437.60..1,838.25 rows=399 width=16) (actual time=2.117..7.378 rows=9,098 loops=5,138)

  • Recheck Cond: ((program_id = 202) AND (learner_id = ANY ('{1185,585,582,586,583,592,590,594,1169,1186,1183,1184,1178,1188,1182,1177,1181,1179,1180,1176,1187}'::integer[])))
  • Heap Blocks: exact=8,559,908
21. 364.798 9,736.510 ↓ 0.0 0 5,138

BitmapAnd (cost=437.60..437.60 rows=399 width=0) (actual time=1.895..1.895 rows=0 loops=5,138)

22. 3,540.082 3,540.082 ↑ 1.0 9,098 5,138

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..172.77 rows=9,113 width=0) (actual time=0.689..0.689 rows=9,098 loops=5,138)

  • Index Cond: (program_id = 202)
23. 5,831.630 5,831.630 ↓ 1.1 13,829 5,138

Bitmap Index Scan on index_task_interactions_on_learner_id (cost=0.00..264.38 rows=12,198 width=0) (actual time=1.135..1.135 rows=13,829 loops=5,138)

  • Index Cond: (learner_id = ANY ('{1185,585,582,586,583,592,590,594,1169,1186,1183,1184,1178,1188,1182,1177,1181,1179,1180,1176,1187}'::integer[]))
24. 81.928 275.576 ↓ 17.0 17 7,448

Hash (cost=8.44..8.44 rows=1 width=387) (actual time=0.037..0.037 rows=17 loops=7,448)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
25. 193.648 193.648 ↓ 17.0 17 7,448

Index Scan using index_task_interactions_on_task_and_learner on task_interactions (cost=0.42..8.44 rows=1 width=387) (actual time=0.014..0.026 rows=17 loops=7,448)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
26. 0.106 1.144 ↓ 44.4 355 1

Hash (cost=282.90..282.90 rows=8 width=16) (actual time=1.144..1.144 rows=355 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
27. 0.192 1.038 ↓ 44.4 355 1

GroupAggregate (cost=282.66..282.82 rows=8 width=16) (actual time=0.810..1.038 rows=355 loops=1)

  • Group Key: completions.learner_id, completions.task_id
28. 0.227 0.846 ↓ 44.4 355 1

Sort (cost=282.66..282.68 rows=8 width=16) (actual time=0.805..0.846 rows=355 loops=1)

  • Sort Key: completions.learner_id, completions.task_id
  • Sort Method: quicksort Memory: 41kB
29. 0.511 0.619 ↓ 44.4 355 1

Bitmap Heap Scan on completions (cost=81.82..282.54 rows=8 width=16) (actual time=0.311..0.619 rows=355 loops=1)

  • Recheck Cond: (learner_id = ANY ('{1185,585,582,586,583,592,590,594,1169,1186,1183,1184,1178,1188,1182,1177,1181,1179,1180,1176,1187}'::integer[]))
  • Filter: (program_id = 202)
  • Rows Removed by Filter: 250
  • Heap Blocks: exact=102
30. 0.108 0.108 ↓ 1.2 605 1

Bitmap Index Scan on index_completions_on_learner_id (cost=0.00..81.82 rows=509 width=0) (actual time=0.108..0.108 rows=605 loops=1)

  • Index Cond: (learner_id = ANY ('{1185,585,582,586,583,592,590,594,1169,1186,1183,1184,1178,1188,1182,1177,1181,1179,1180,1176,1187}'::integer[]))
31. 35.336 37.240 ↑ 1.0 38 7,448

Materialize (cost=57.11..187.15 rows=38 width=29) (actual time=0.001..0.005 rows=38 loops=7,448)

32. 0.938 1.904 ↑ 1.0 38 1

Hash Right Join (cost=57.11..186.96 rows=38 width=29) (actual time=1.236..1.904 rows=38 loops=1)

  • Hash Cond: (date_references.id = tasks_1.date_reference_id)
33. 0.903 0.903 ↑ 1.0 5,347 1

Seq Scan on date_references (cost=0.00..109.47 rows=5,347 width=33) (actual time=0.010..0.903 rows=5,347 loops=1)

34. 0.013 0.063 ↑ 1.0 38 1

Hash (cost=56.64..56.64 rows=38 width=12) (actual time=0.063..0.063 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
35. 0.050 0.050 ↑ 1.0 38 1

Index Scan using index_tasks_on_program_id on tasks tasks_1 (cost=0.28..56.64 rows=38 width=12) (actual time=0.013..0.050 rows=38 loops=1)

  • Index Cond: (program_id = 202)
36. 0.013 71,433.142 ↓ 14.0 14 1

Nested Loop Left Join (cost=0.56..16.77 rows=1 width=75) (actual time=71,432.699..71,433.142 rows=14 loops=1)

37. 0.019 71,433.059 ↓ 14.0 14 1

Nested Loop (cost=0.28..8.33 rows=1 width=71) (actual time=71,432.660..71,433.059 rows=14 loops=1)

38. 71,432.984 71,432.984 ↓ 14.0 14 1

CTE Scan on learner_task_count (cost=0.00..0.02 rows=1 width=57) (actual time=71,432.642..71,432.984 rows=14 loops=1)

39. 0.056 0.056 ↑ 1.0 1 14

Index Scan using users_pkey on users (cost=0.28..8.29 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=14)

  • Index Cond: (id = learner_task_count.learner_id)
40. 0.070 0.070 ↑ 1.0 1 14

Index Only Scan using index_mentorings on mentorings mentoring (cost=0.28..8.43 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=14)

  • Index Cond: ((program_id = 202) AND (learner_id = learner_task_count.learner_id))
  • Heap Fetches: 14
Planning time : 10.459 ms
Execution time : 71,433.922 ms