explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Jqg : faythe members page

Settings
# exclusive inclusive rows x rows loops node
1. 0.131 1,126.727 ↓ 14.0 14 1

Sort (cost=2,552.09..2,552.09 rows=1 width=74) (actual time=1,126.725..1,126.727 rows=14 loops=1)

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

CTE learner_task_count

3. 0.495 1,126.255 ↓ 14.0 14 1

GroupAggregate (cost=2,535.20..2,535.31 rows=1 width=57) (actual time=1,125.715..1,126.255 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 0.606 1,125.760 ↓ 532.0 532 1

Sort (cost=2,535.20..2,535.20 rows=1 width=46) (actual time=1,125.636..1,125.760 rows=532 loops=1)

  • Sort Key: program_memberships.user_id
  • Sort Method: quicksort Memory: 66kB
5. 1.594 1,125.154 ↓ 532.0 532 1

Nested Loop Left Join (cost=1,859.56..2,535.19 rows=1 width=46) (actual time=15.423..1,125.154 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.051 0.088 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships (cost=4.44..26.96 rows=1 width=16) (actual time=0.060..0.088 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.037 0.037 ↑ 1.0 21 1

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

  • Index Cond: (program_id = 202)
8. 44.044 1,123.472 ↓ 14.0 532 14

Nested Loop Left Join (cost=1,855.13..2,507.56 rows=38 width=54) (actual time=1.158..80.248 rows=532 loops=14)

  • Join Filter: (tasks_1.id = tasks.id)
  • Rows Removed by Join Filter: 19,684
9. 8.174 1,049.636 ↓ 14.0 532 14

Hash Left Join (cost=1,800.21..2,300.09 rows=38 width=84) (actual time=1.050..74.974 rows=532 loops=14)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
10. 7.462 1,039.696 ↓ 14.0 532 14

Nested Loop Left Join (cost=1,517.18..2,016.39 rows=38 width=124) (actual time=0.921..74.264 rows=532 loops=14)

11. 3.570 11.858 ↓ 14.0 532 14

Nested Loop (cost=4.86..90.06 rows=38 width=52) (actual time=0.044..0.847 rows=532 loops=14)

12. 0.392 1.624 ↓ 14.0 14 14

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

13. 0.112 0.112 ↑ 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.008 rows=1 loops=14)

  • Index Cond: (id = 202)
14. 0.966 1.120 ↓ 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.017..0.080 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.154 0.154 ↑ 1.0 21 14

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

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

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

  • Index Cond: (program_id = 202)
17. 380.996 1,020.376 ↑ 1.0 1 7,448

Hash Join (cost=1,512.32..1,523.20 rows=1 width=80) (actual time=0.081..0.137 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. 475.944 482.972 ↓ 1.1 367 5,138

HashAggregate (cost=1,503.86..1,507.34 rows=348 width=16) (actual time=0.003..0.094 rows=367 loops=5,138)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
19. 4.874 7.028 ↓ 26.0 9,090 1

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=419.69..1,501.23 rows=350 width=16) (actual time=2.269..7.028 rows=9,090 loops=1)

  • 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=610
20. 0.046 2.154 ↓ 0.0 0 1

BitmapAnd (cost=419.69..419.69 rows=350 width=0) (actual time=2.154..2.154 rows=0 loops=1)

21. 0.819 0.819 ↓ 1.1 9,090 1

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..159.61 rows=8,425 width=0) (actual time=0.819..0.819 rows=9,090 loops=1)

  • Index Cond: (program_id = 202)
22. 1.289 1.289 ↓ 1.2 13,821 1

Bitmap Index Scan on index_task_interactions_on_learner_id (cost=0.00..259.65 rows=11,567 width=0) (actual time=1.289..1.289 rows=13,821 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[]))
23. 52.136 156.408 ↓ 17.0 17 7,448

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
24. 104.272 104.272 ↓ 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=80) (actual time=0.006..0.014 rows=17 loops=7,448)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
25. 0.162 1.766 ↓ 44.4 355 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
26. 0.261 1.604 ↓ 44.4 355 1

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

  • Group Key: completions.learner_id, completions.task_id
27. 0.387 1.343 ↓ 44.4 355 1

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

  • Sort Key: completions.learner_id, completions.task_id
  • Sort Method: quicksort Memory: 41kB
28. 0.828 0.956 ↓ 44.4 355 1

Bitmap Heap Scan on completions (cost=81.82..282.54 rows=8 width=16) (actual time=0.468..0.956 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
29. 0.128 0.128 ↓ 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.128..0.128 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[]))
30. 27.560 29.792 ↑ 1.0 38 7,448

Materialize (cost=54.92..184.96 rows=38 width=29) (actual time=0.000..0.004 rows=38 loops=7,448)

31. 1.005 2.232 ↑ 1.0 38 1

Hash Right Join (cost=54.92..184.77 rows=38 width=29) (actual time=1.479..2.232 rows=38 loops=1)

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

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

33. 0.025 0.092 ↑ 1.0 38 1

Hash (cost=54.45..54.45 rows=38 width=12) (actual time=0.092..0.092 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
34. 0.067 0.067 ↑ 1.0 38 1

Index Scan using index_tasks_on_program_id on tasks tasks_1 (cost=0.28..54.45 rows=38 width=12) (actual time=0.029..0.067 rows=38 loops=1)

  • Index Cond: (program_id = 202)
35. 0.021 1,126.596 ↓ 14.0 14 1

Nested Loop Left Join (cost=0.56..16.77 rows=1 width=74) (actual time=1,125.854..1,126.596 rows=14 loops=1)

36. 0.039 1,126.421 ↓ 14.0 14 1

Nested Loop (cost=0.28..8.33 rows=1 width=70) (actual time=1,125.767..1,126.421 rows=14 loops=1)

37. 1,126.284 1,126.284 ↓ 14.0 14 1

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

38. 0.098 0.098 ↑ 1.0 1 14

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

  • Index Cond: (id = learner_task_count.learner_id)
39. 0.154 0.154 ↑ 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.010..0.011 rows=1 loops=14)

  • Index Cond: ((program_id = 202) AND (learner_id = learner_task_count.learner_id))
  • Heap Fetches: 14
Planning time : 9.985 ms
Execution time : 1,129.615 ms