explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sg5t : after release learner_task_count production

Settings
# exclusive inclusive rows x rows loops node
1. 39,820.181 39,820.181 ↓ 14.0 14 1

CTE Scan on learner_task_count (cost=2,092.10..2,092.12 rows=1 width=57) (actual time=39,820.120..39,820.181 rows=14 loops=1)

2.          

CTE learner_task_count

3. 2.038 39,820.165 ↓ 14.0 14 1

HashAggregate (cost=2,092.04..2,092.10 rows=1 width=57) (actual time=39,820.116..39,820.165 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 5.177 39,818.127 ↓ 616.0 616 1

Nested Loop Left Join (cost=1,525.38..2,091.99 rows=1 width=46) (actual time=12.441..39,818.127 rows=616 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: 8,008
5. 0.033 0.072 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships (cost=4.40..21.52 rows=1 width=16) (actual time=0.052..0.072 rows=14 loops=1)

  • Recheck Cond: (program_id = 121)
  • Filter: ((learner IS TRUE) AND (user_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::bigint[])))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=2
6. 0.039 0.039 ↓ 1.1 17 1

Bitmap Index Scan on index_program_memberships_on_program_and_user (cost=0.00..4.40 rows=16 width=0) (actual time=0.038..0.039 rows=17 loops=1)

  • Index Cond: (program_id = 121)
7. 35.956 39,812.878 ↓ 13.7 616 14

Hash Left Join (cost=1,520.98..2,069.68 rows=45 width=54) (actual time=6.522..2,843.777 rows=616 loops=14)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
8. 306.264 39,775.176 ↓ 13.7 616 14

Nested Loop Left Join (cost=1,146.25..1,693.59 rows=45 width=452) (actual time=6.393..2,841.084 rows=616 loops=14)

9. 7.140 221.088 ↓ 13.7 616 14

Nested Loop (cost=135.90..261.92 rows=45 width=76) (actual time=0.497..15.792 rows=616 loops=14)

10. 0.280 1.092 ↓ 14.0 14 14

Nested Loop (cost=4.54..29.77 rows=1 width=38) (actual time=0.028..0.078 rows=14 loops=14)

11. 0.126 0.126 ↑ 1.0 1 14

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

  • Index Cond: (id = 121)
12. 0.546 0.686 ↓ 14.0 14 14

Bitmap Heap Scan on program_memberships program_memberships_1 (cost=4.40..21.60 rows=1 width=24) (actual time=0.014..0.049 rows=14 loops=14)

  • Recheck Cond: (program_id = 121)
  • Filter: (learner AND ((program_id)::integer = 121) AND (user_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::bigint[])))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=28
13. 0.140 0.140 ↓ 1.1 17 14

Bitmap Index Scan on index_program_memberships_on_program_and_user (cost=0.00..4.40 rows=16 width=0) (actual time=0.010..0.010 rows=17 loops=14)

  • Index Cond: (program_id = 121)
14. 14.000 212.856 ↑ 1.0 44 196

Hash Right Join (cost=131.35..231.71 rows=45 width=42) (actual time=0.468..1.086 rows=44 loops=196)

  • Hash Cond: (tasks_1.id = tasks.id)
15. 106.191 198.744 ↑ 1.0 44 196

Hash Right Join (cost=65.68..165.91 rows=45 width=29) (actual time=0.465..1.014 rows=44 loops=196)

  • Hash Cond: (date_references.id = tasks_1.date_reference_id)
16. 92.512 92.512 ↓ 1.0 3,918 196

Seq Scan on date_references (cost=0.00..85.44 rows=3,844 width=33) (actual time=0.003..0.472 rows=3,918 loops=196)

17. 0.010 0.041 ↑ 1.0 44 1

Hash (cost=65.11..65.11 rows=45 width=12) (actual time=0.040..0.041 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
18. 0.031 0.031 ↑ 1.0 44 1

Index Scan using index_tasks_on_program_id on tasks tasks_1 (cost=0.28..65.11 rows=45 width=12) (actual time=0.011..0.031 rows=44 loops=1)

  • Index Cond: (program_id = 121)
19. 0.016 0.112 ↑ 1.0 44 1

Hash (cost=65.11..65.11 rows=45 width=17) (actual time=0.111..0.112 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
20. 0.096 0.096 ↑ 1.0 44 1

Index Scan using index_tasks_on_program_id on tasks (cost=0.28..65.11 rows=45 width=17) (actual time=0.030..0.096 rows=44 loops=1)

  • Index Cond: (program_id = 121)
21. 472.458 39,247.824 ↑ 1.0 1 8,624

Hash Join (cost=1,010.35..1,019.71 rows=1 width=384) (actual time=4.061..4.551 rows=1 loops=8,624)

  • 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))
22. 6,223.126 38,585.638 ↓ 1.8 403 5,642

GroupAggregate (cost=1,001.90..1,006.44 rows=226 width=16) (actual time=5.338..6.839 rows=403 loops=5,642)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
23. 11,898.978 32,362.512 ↓ 25.4 5,781 5,642

Sort (cost=1,001.90..1,002.47 rows=228 width=16) (actual time=5.334..5.736 rows=5,781 loops=5,642)

  • Sort Key: task_interactions_1.learner_id, task_interactions_1.task_id
  • Sort Method: quicksort Memory: 463kB
24. 15,696.044 20,463.534 ↓ 25.4 5,781 5,642

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=218.33..992.97 rows=228 width=16) (actual time=0.956..3.627 rows=5,781 loops=5,642)

  • Recheck Cond: ((learner_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::integer[])) AND (program_id = 121))
  • Heap Blocks: exact=5,201,924
25. 236.964 4,767.490 ↓ 0.0 0 5,642

BitmapAnd (cost=218.33..218.33 rows=228 width=0) (actual time=0.845..0.845 rows=0 loops=5,642)

26. 2,296.294 2,296.294 ↓ 1.3 6,003 5,642

Bitmap Index Scan on index_task_interactions_on_learner_id (cost=0.00..96.91 rows=4,682 width=0) (actual time=0.407..0.407 rows=6,003 loops=5,642)

  • Index Cond: (learner_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::integer[]))
27. 2,234.232 2,234.232 ↓ 1.1 5,782 5,642

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..121.05 rows=5,434 width=0) (actual time=0.396..0.396 rows=5,782 loops=5,642)

  • Index Cond: (program_id = 121)
28. 43.120 189.728 ↓ 9.0 9 8,624

Hash (cost=8.44..8.44 rows=1 width=384) (actual time=0.022..0.022 rows=9 loops=8,624)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 146.608 146.608 ↓ 9.0 9 8,624

Index Scan using index_task_interactions_on_task_and_learner on task_interactions (cost=0.42..8.44 rows=1 width=384) (actual time=0.011..0.017 rows=9 loops=8,624)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
30. 0.075 1.746 ↓ 19.9 398 1

Hash (cost=374.43..374.43 rows=20 width=16) (actual time=1.746..1.746 rows=398 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
31. 0.143 1.671 ↓ 19.9 398 1

GroupAggregate (cost=373.83..374.23 rows=20 width=16) (actual time=1.507..1.671 rows=398 loops=1)

  • Group Key: completions.learner_id, completions.task_id
32. 0.160 1.528 ↓ 19.9 398 1

Sort (cost=373.83..373.88 rows=20 width=16) (actual time=1.501..1.528 rows=398 loops=1)

  • Sort Key: completions.learner_id, completions.task_id
  • Sort Method: quicksort Memory: 43kB
33. 1.368 1.368 ↓ 19.9 398 1

Seq Scan on completions (cost=0.00..373.40 rows=20 width=16) (actual time=0.498..1.368 rows=398 loops=1)

  • Filter: ((program_id = 121) AND (learner_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::integer[])))
  • Rows Removed by Filter: 8,574
Planning time : 6.125 ms
Execution time : 39,820.820 ms