explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wxeS : learner_task_count faythe program 121

Settings
# exclusive inclusive rows x rows loops node
1. 2,344.226 2,344.226 ↓ 14.0 14 1

CTE Scan on learner_task_count (cost=2,235.05..2,235.07 rows=1 width=57) (actual time=2,343.929..2,344.226 rows=14 loops=1)

2.          

CTE learner_task_count

3. 0.287 2,344.201 ↓ 14.0 14 1

GroupAggregate (cost=2,234.94..2,235.05 rows=1 width=57) (actual time=2,343.925..2,344.201 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 0.924 2,343.914 ↓ 616.0 616 1

Sort (cost=2,234.94..2,234.94 rows=1 width=46) (actual time=2,343.874..2,343.914 rows=616 loops=1)

  • Sort Key: program_memberships.user_id
  • Sort Method: quicksort Memory: 73kB
5. 1.548 2,342.990 ↓ 616.0 616 1

Hash Right Join (cost=1,663.75..2,234.93 rows=1 width=46) (actual time=6.235..2,342.990 rows=616 loops=1)

  • Hash Cond: (((program_memberships_1.program_id)::integer = program_memberships.program_id) AND (program_memberships_1.user_id = program_memberships.user_id))
6. 3.063 2,341.394 ↓ 14.0 616 1

Hash Left Join (cost=1,644.10..2,215.05 rows=44 width=54) (actual time=6.173..2,341.394 rows=616 loops=1)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
7. 28.095 2,336.984 ↓ 14.0 616 1

Nested Loop Left Join (cost=1,310.33..1,879.94 rows=44 width=116) (actual time=4.805..2,336.984 rows=616 loops=1)

8. 0.808 18.601 ↓ 14.0 616 1

Nested Loop (cost=122.60..239.90 rows=44 width=76) (actual time=0.775..18.601 rows=616 loops=1)

9. 0.018 0.181 ↓ 14.0 14 1

Nested Loop (cost=4.40..27.43 rows=1 width=38) (actual time=0.032..0.181 rows=14 loops=1)

10. 0.044 0.044 ↑ 1.0 1 1

Seq Scan on programs (cost=0.00..7.71 rows=1 width=18) (actual time=0.025..0.044 rows=1 loops=1)

  • Filter: (id = 121)
  • Rows Removed by Filter: 136
11. 0.117 0.119 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships program_memberships_1 (cost=4.40..19.71 rows=1 width=24) (actual time=0.005..0.119 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
12. 0.002 0.002 ↑ 1.0 16 1

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

  • Index Cond: (program_id = 121)
13. 1.207 17.612 ↑ 1.0 44 14

Hash Right Join (cost=118.21..212.03 rows=44 width=42) (actual time=0.552..1.258 rows=44 loops=14)

  • Hash Cond: (tasks_1.id = tasks.id)
14. 8.521 16.352 ↑ 1.0 44 14

Hash Right Join (cost=59.10..152.81 rows=44 width=29) (actual time=0.546..1.168 rows=44 loops=14)

  • Hash Cond: (date_references.id = tasks_1.date_reference_id)
15. 7.798 7.798 ↑ 1.0 3,879 14

Seq Scan on date_references (cost=0.00..78.79 rows=3,879 width=33) (actual time=0.004..0.557 rows=3,879 loops=14)

16. 0.011 0.033 ↑ 1.0 44 1

Hash (cost=58.55..58.55 rows=44 width=12) (actual time=0.033..0.033 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.022 0.022 ↑ 1.0 44 1

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

  • Index Cond: (program_id = 121)
18. 0.018 0.053 ↑ 1.0 44 1

Hash (cost=58.55..58.55 rows=44 width=17) (actual time=0.053..0.053 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.035 0.035 ↑ 1.0 44 1

Index Scan using index_tasks_on_program_id on tasks (cost=0.28..58.55 rows=44 width=17) (actual time=0.011..0.035 rows=44 loops=1)

  • Index Cond: (program_id = 121)
20. 39.160 2,290.288 ↑ 1.0 1 616

Hash Join (cost=1,187.73..1,198.00 rows=1 width=48) (actual time=3.234..3.718 rows=1 loops=616)

  • 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. 429.066 2,236.344 ↓ 1.6 394 394

GroupAggregate (cost=1,179.27..1,184.26 rows=248 width=16) (actual time=4.193..5.676 rows=394 loops=394)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
22. 833.310 1,807.278 ↓ 21.4 5,361 394

Sort (cost=1,179.27..1,179.90 rows=251 width=16) (actual time=4.186..4.587 rows=5,361 loops=394)

  • Sort Key: task_interactions_1.learner_id, task_interactions_1.task_id
  • Sort Method: quicksort Memory: 444kB
23. 870.740 973.968 ↓ 21.4 5,361 394

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=100.84..1,169.27 rows=251 width=16) (actual time=0.286..2.472 rows=5,361 loops=394)

  • Recheck Cond: (program_id = 121)
  • Filter: (learner_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::integer[]))
  • Heap Blocks: exact=83,134
24. 103.228 103.228 ↑ 1.0 5,361 394

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..100.78 rows=5,381 width=0) (actual time=0.262..0.262 rows=5,361 loops=394)

  • Index Cond: (program_id = 121)
25. 3.080 14.784 ↓ 9.0 9 616

Hash (cost=8.44..8.44 rows=1 width=48) (actual time=0.024..0.024 rows=9 loops=616)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 11.704 11.704 ↓ 9.0 9 616

Index Scan using index_task_interactions_on_task_and_learner on task_interactions (cost=0.42..8.44 rows=1 width=48) (actual time=0.015..0.019 rows=9 loops=616)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
27. 0.073 1.347 ↓ 19.6 392 1

Hash (cost=333.47..333.47 rows=20 width=16) (actual time=1.347..1.347 rows=392 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
28. 0.135 1.274 ↓ 19.6 392 1

GroupAggregate (cost=332.87..333.27 rows=20 width=16) (actual time=1.115..1.274 rows=392 loops=1)

  • Group Key: completions.learner_id, completions.task_id
29. 0.192 1.139 ↓ 19.6 392 1

Sort (cost=332.87..332.92 rows=20 width=16) (actual time=1.111..1.139 rows=392 loops=1)

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

Seq Scan on completions (cost=0.00..332.44 rows=20 width=16) (actual time=0.372..0.947 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,256
31. 0.006 0.048 ↓ 14.0 14 1

Hash (cost=19.63..19.63 rows=1 width=16) (actual time=0.048..0.048 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.013 0.042 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships (cost=4.40..19.63 rows=1 width=16) (actual time=0.038..0.042 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: 2
  • Heap Blocks: exact=1
33. 0.029 0.029 ↑ 1.0 16 1

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

  • Index Cond: (program_id = 121)
Planning time : 4.293 ms
Execution time : 2,344.837 ms