explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2sys : learner_task_count huthwaite program 121

Settings
# exclusive inclusive rows x rows loops node
1. 45,550.264 45,550.264 ↓ 14.0 14 1

CTE Scan on learner_task_count (cost=7,053.62..7,053.64 rows=1 width=57) (actual time=3,756.986..45,550.264 rows=14 loops=1)

2.          

CTE learner_task_count

3. 1.908 45,550.205 ↓ 14.0 14 1

GroupAggregate (cost=6,592.63..7,053.62 rows=1 width=57) (actual time=3,756.982..45,550.205 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 5.607 45,548.297 ↓ 616.0 616 1

Nested Loop Left Join (cost=6,592.63..7,053.51 rows=1 width=46) (actual time=11.427..45,548.297 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.074 0.074 ↓ 14.0 14 1

Index Scan using index_program_memberships_on_program_and_user on program_memberships (cost=0.28..42.45 rows=1 width=16) (actual time=0.023..0.074 rows=14 loops=1)

  • Index 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: 2
6. 21.397 45,542.616 ↓ 14.0 616 14

Hash Left Join (cost=6,592.35..7,010.29 rows=44 width=54) (actual time=6.374..3,253.044 rows=616 loops=14)

  • Hash Cond: (tasks.id = tasks_1.id)
7. 307.594 45,519.656 ↓ 14.0 616 14

Nested Loop Left Join (cost=6,427.50..6,844.16 rows=44 width=83) (actual time=6.259..3,251.404 rows=616 loops=14)

8. 12.152 65.422 ↓ 14.0 616 14

Nested Loop (cost=455.15..464.76 rows=44 width=59) (actual time=0.155..4.673 rows=616 loops=14)

9. 24.276 44.646 ↓ 14.0 616 14

Merge Left Join (cost=455.01..456.05 rows=44 width=45) (actual time=0.152..3.189 rows=616 loops=14)

  • Merge Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
10. 4.984 5.404 ↓ 14.0 616 14

Sort (cost=88.33..88.44 rows=44 width=37) (actual time=0.056..0.386 rows=616 loops=14)

  • Sort Key: program_memberships_1.user_id, tasks.id
  • Sort Method: quicksort Memory: 73kB
11. 0.165 0.420 ↓ 14.0 616 1

Nested Loop (cost=4.68..87.13 rows=44 width=37) (actual time=0.022..0.420 rows=616 loops=1)

12. 0.013 0.017 ↓ 14.0 14 1

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

  • 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: 2
  • Heap Blocks: exact=1
13. 0.004 0.004 ↑ 1.0 16 1

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

  • Index Cond: (program_id = 121)
14. 0.238 0.238 ↑ 1.0 44 14

Index Scan using index_tasks_on_program_id on tasks (cost=0.28..65.10 rows=44 width=17) (actual time=0.003..0.017 rows=44 loops=14)

  • Index Cond: (program_id = 121)
15. 10.178 14.966 ↓ 19.6 392 14

GroupAggregate (cost=366.67..367.07 rows=20 width=16) (actual time=0.093..1.069 rows=392 loops=14)

  • Group Key: completions.learner_id, completions.task_id
16. 3.680 4.788 ↓ 19.6 392 14

Sort (cost=366.67..366.72 rows=20 width=16) (actual time=0.091..0.342 rows=392 loops=14)

  • Sort Key: completions.learner_id, completions.task_id
  • Sort Method: quicksort Memory: 43kB
17. 1.108 1.108 ↓ 19.6 392 1

Seq Scan on completions (cost=0.00..366.24 rows=20 width=16) (actual time=0.426..1.108 rows=392 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,368
18. 8.603 8.624 ↑ 1.0 1 8,624

Materialize (cost=0.14..8.17 rows=1 width=18) (actual time=0.000..0.001 rows=1 loops=8,624)

19. 0.021 0.021 ↑ 1.0 1 1

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

  • Index Cond: (id = 121)
20. 516.208 45,146.640 ↑ 1.0 1 8,624

Hash Join (cost=5,972.35..5,981.57 rows=1 width=384) (actual time=4.726..5.235 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))
21. 6,288.240 44,414.832 ↓ 1.8 394 5,516

GroupAggregate (cost=5,963.89..5,968.37 rows=223 width=16) (actual time=6.485..8.052 rows=394 loops=5,516)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
22. 12,725.412 38,126.592 ↓ 23.8 5,361 5,516

Sort (cost=5,963.89..5,964.45 rows=225 width=16) (actual time=6.477..6.912 rows=5,361 loops=5,516)

  • Sort Key: task_interactions_1.learner_id, task_interactions_1.task_id
  • Sort Method: quicksort Memory: 444kB
23. 25,401.180 25,401.180 ↓ 23.8 5,361 5,516

Index Scan using index_task_interactions_on_program_id on task_interactions task_interactions_1 (cost=0.29..5,955.10 rows=225 width=16) (actual time=0.010..4.605 rows=5,361 loops=5,516)

  • Index Cond: (program_id = 121)
  • Filter: (learner_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::integer[]))
24. 43.120 215.600 ↓ 9.0 9 8,624

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 172.480 172.480 ↓ 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.014..0.020 rows=9 loops=8,624)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
26. 0.018 1.563 ↑ 1.0 44 1

Hash (cost=164.30..164.30 rows=44 width=29) (actual time=1.563..1.563 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
27. 0.702 1.545 ↑ 1.0 44 1

Hash Right Join (cost=65.65..164.30 rows=44 width=29) (actual time=0.862..1.545 rows=44 loops=1)

  • Hash Cond: (date_references.id = tasks_1.date_reference_id)
28. 0.778 0.778 ↓ 1.0 3,791 1

Seq Scan on date_references (cost=0.00..84.30 rows=3,730 width=33) (actual time=0.017..0.778 rows=3,791 loops=1)

29. 0.011 0.065 ↑ 1.0 44 1

Hash (cost=65.10..65.10 rows=44 width=12) (actual time=0.065..0.065 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
30. 0.054 0.054 ↑ 1.0 44 1

Index Scan using index_tasks_on_program_id on tasks tasks_1 (cost=0.28..65.10 rows=44 width=12) (actual time=0.022..0.054 rows=44 loops=1)

  • Index Cond: (program_id = 121)
Planning time : 8.107 ms
Execution time : 45,550.746 ms