explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NBrS : learner_task_count after waiting a few hours

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

CTE Scan on learner_task_count (cost=2,097.02..2,097.04 rows=1 width=57) (actual time=2,847.853..2,848.124 rows=14 loops=1)

2.          

CTE learner_task_count

3. 0.268 2,848.112 ↓ 14.0 14 1

GroupAggregate (cost=2,096.90..2,097.02 rows=1 width=57) (actual time=2,847.849..2,848.112 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 0.657 2,847.844 ↓ 616.0 616 1

Sort (cost=2,096.90..2,096.91 rows=1 width=46) (actual time=2,847.810..2,847.844 rows=616 loops=1)

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

Hash Right Join (cost=1,546.09..2,096.89 rows=1 width=46) (actual time=15.525..2,847.187 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. 1.940 2,845.871 ↓ 13.7 616 1

Hash Left Join (cost=1,524.56..2,075.12 rows=45 width=54) (actual time=15.419..2,845.871 rows=616 loops=1)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
7. 8.177 2,840.505 ↓ 13.7 616 1

Nested Loop Left Join (cost=1,149.83..1,699.02 rows=45 width=452) (actual time=11.965..2,840.505 rows=616 loops=1)

8. 0.487 15.976 ↓ 13.7 616 1

Nested Loop (cost=135.90..261.92 rows=45 width=76) (actual time=0.929..15.976 rows=616 loops=1)

9. 0.018 0.089 ↓ 14.0 14 1

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

10. 0.026 0.026 ↑ 1.0 1 1

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

  • Index Cond: (id = 121)
11. 0.037 0.045 ↓ 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.014..0.045 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: 3
  • Heap Blocks: exact=2
12. 0.008 0.008 ↓ 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.008..0.008 rows=17 loops=1)

  • Index Cond: (program_id = 121)
13. 0.962 15.400 ↑ 1.0 44 14

Hash Right Join (cost=131.35..231.71 rows=45 width=42) (actual time=0.499..1.100 rows=44 loops=14)

  • Hash Cond: (tasks_1.id = tasks.id)
14. 7.584 14.364 ↑ 1.0 44 14

Hash Right Join (cost=65.68..165.91 rows=45 width=29) (actual time=0.491..1.026 rows=44 loops=14)

  • Hash Cond: (date_references.id = tasks_1.date_reference_id)
15. 6.720 6.720 ↓ 1.0 3,918 14

Seq Scan on date_references (cost=0.00..85.44 rows=3,844 width=33) (actual time=0.004..0.480 rows=3,918 loops=14)

16. 0.024 0.060 ↑ 1.0 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.036 0.036 ↑ 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.006..0.036 rows=44 loops=1)

  • Index Cond: (program_id = 121)
18. 0.020 0.074 ↑ 1.0 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.054 0.054 ↑ 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.012..0.054 rows=44 loops=1)

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

Hash Join (cost=1,013.93..1,023.32 rows=1 width=384) (actual time=4.070..4.572 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. 459.503 2,769.228 ↓ 1.8 407 407

GroupAggregate (cost=1,005.48..1,010.04 rows=227 width=16) (actual time=5.280..6.804 rows=407 loops=407)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
22. 807.895 2,309.725 ↓ 26.1 5,974 407

Sort (cost=1,005.48..1,006.05 rows=229 width=16) (actual time=5.275..5.675 rows=5,974 loops=407)

  • Sort Key: task_interactions_1.learner_id, task_interactions_1.task_id
  • Sort Method: quicksort Memory: 473kB
23. 1,150.996 1,501.830 ↓ 26.1 5,974 407

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=218.53..996.50 rows=229 width=16) (actual time=0.974..3.690 rows=5,974 loops=407)

  • 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=381,359
24. 17.908 350.834 ↓ 0.0 0 407

BitmapAnd (cost=218.53..218.53 rows=229 width=0) (actual time=0.862..0.862 rows=0 loops=407)

25. 172.161 172.161 ↓ 1.3 6,195 407

Bitmap Index Scan on index_task_interactions_on_learner_id (cost=0.00..97.02 rows=4,694 width=0) (actual time=0.423..0.423 rows=6,195 loops=407)

  • Index Cond: (learner_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::integer[]))
26. 160.765 160.765 ↓ 1.1 5,974 407

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..121.14 rows=5,447 width=0) (actual time=0.395..0.395 rows=5,974 loops=407)

  • Index Cond: (program_id = 121)
27. 3.696 13.552 ↓ 10.0 10 616

Hash (cost=8.44..8.44 rows=1 width=384) (actual time=0.022..0.022 rows=10 loops=616)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 9.856 9.856 ↓ 10.0 10 616

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.010..0.016 rows=10 loops=616)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
29. 0.159 3.426 ↓ 20.1 401 1

Hash (cost=374.43..374.43 rows=20 width=16) (actual time=3.426..3.426 rows=401 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
30. 0.331 3.267 ↓ 20.1 401 1

GroupAggregate (cost=373.83..374.23 rows=20 width=16) (actual time=2.885..3.267 rows=401 loops=1)

  • Group Key: completions.learner_id, completions.task_id
31. 0.364 2.936 ↓ 20.1 401 1

Sort (cost=373.83..373.88 rows=20 width=16) (actual time=2.876..2.936 rows=401 loops=1)

  • Sort Key: completions.learner_id, completions.task_id
  • Sort Method: quicksort Memory: 43kB
32. 2.572 2.572 ↓ 20.1 401 1

Seq Scan on completions (cost=0.00..373.40 rows=20 width=16) (actual time=0.990..2.572 rows=401 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,578
33. 0.013 0.085 ↓ 14.0 14 1

Hash (cost=21.52..21.52 rows=1 width=16) (actual time=0.085..0.085 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
34. 0.039 0.072 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships (cost=4.40..21.52 rows=1 width=16) (actual time=0.065..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
35. 0.033 0.033 ↓ 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.033..0.033 rows=17 loops=1)

  • Index Cond: (program_id = 121)
Planning time : 5.197 ms
Execution time : 2,848.455 ms