explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KSjl : after release learner_task_count faythe

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

CTE Scan on learner_task_count (cost=1,956.10..1,956.12 rows=1 width=57) (actual time=1,281.580..1,281.862 rows=14 loops=1)

2.          

CTE learner_task_count

3. 0.269 1,281.833 ↓ 14.0 14 1

GroupAggregate (cost=1,955.98..1,956.10 rows=1 width=57) (actual time=1,281.576..1,281.833 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 0.485 1,281.564 ↓ 616.0 616 1

Sort (cost=1,955.98..1,955.99 rows=1 width=46) (actual time=1,281.530..1,281.564 rows=616 loops=1)

  • Sort Key: program_memberships.user_id
  • Sort Method: quicksort Memory: 73kB
5. 1.694 1,281.079 ↓ 616.0 616 1

Nested Loop Left Join (cost=1,458.32..1,955.97 rows=1 width=46) (actual time=7.175..1,281.079 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
6. 0.033 0.051 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships (cost=4.41..19.66 rows=1 width=16) (actual time=0.028..0.051 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
7. 0.018 0.018 ↑ 1.0 17 1

Bitmap Index Scan on index_program_memberships_on_program_id (cost=0.00..4.41 rows=17 width=0) (actual time=0.018..0.018 rows=17 loops=1)

  • Index Cond: (program_id = 121)
8. 14.800 1,279.334 ↓ 14.0 616 14

Hash Left Join (cost=1,453.91..1,935.54 rows=44 width=54) (actual time=1.118..91.381 rows=616 loops=14)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
9. 8.484 1,262.716 ↓ 14.0 616 14

Nested Loop Left Join (cost=1,108.93..1,589.23 rows=44 width=147) (actual time=0.983..90.194 rows=616 loops=14)

10. 5.054 219.352 ↓ 14.0 616 14

Nested Loop (cost=120.92..240.09 rows=44 width=76) (actual time=0.555..15.668 rows=616 loops=14)

11. 0.378 1.442 ↓ 14.0 14 14

Nested Loop (cost=4.55..27.92 rows=1 width=38) (actual time=0.031..0.103 rows=14 loops=14)

12. 0.112 0.112 ↑ 1.0 1 14

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

  • Index Cond: (id = 121)
13. 0.812 0.952 ↓ 14.0 14 14

Bitmap Heap Scan on program_memberships program_memberships_1 (cost=4.41..19.75 rows=1 width=24) (actual time=0.015..0.068 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
14. 0.140 0.140 ↑ 1.0 17 14

Bitmap Index Scan on index_program_memberships_on_program_id (cost=0.00..4.41 rows=17 width=0) (actual time=0.010..0.010 rows=17 loops=14)

  • Index Cond: (program_id = 121)
15. 6.406 212.856 ↑ 1.0 44 196

Hash Right Join (cost=116.37..211.73 rows=44 width=42) (actual time=0.537..1.086 rows=44 loops=196)

  • Hash Cond: (tasks_1.id = tasks.id)
16. 105.416 206.388 ↑ 1.0 44 196

Hash Right Join (cost=58.19..153.43 rows=44 width=29) (actual time=0.536..1.053 rows=44 loops=196)

  • Hash Cond: (date_references.id = tasks_1.date_reference_id)
17. 100.940 100.940 ↑ 1.0 3,918 196

Seq Scan on date_references (cost=0.00..80.18 rows=3,918 width=33) (actual time=0.004..0.515 rows=3,918 loops=196)

18. 0.009 0.032 ↑ 1.0 44 1

Hash (cost=57.64..57.64 rows=44 width=12) (actual time=0.032..0.032 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
19. 0.023 0.023 ↑ 1.0 44 1

Index Scan using index_tasks_on_program_id on tasks tasks_1 (cost=0.28..57.64 rows=44 width=12) (actual time=0.005..0.023 rows=44 loops=1)

  • Index Cond: (program_id = 121)
20. 0.012 0.062 ↑ 1.0 44 1

Hash (cost=57.64..57.64 rows=44 width=17) (actual time=0.062..0.062 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
21. 0.050 0.050 ↑ 1.0 44 1

Index Scan using index_tasks_on_program_id on tasks (cost=0.28..57.64 rows=44 width=17) (actual time=0.018..0.050 rows=44 loops=1)

  • Index Cond: (program_id = 121)
22. 416.248 1,034.880 ↑ 1.0 1 8,624

Hash Join (cost=988.01..996.20 rows=1 width=79) (actual time=0.068..0.120 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))
23. 504.064 506.520 ↓ 1.5 402 5,628

HashAggregate (cost=979.55..982.17 rows=262 width=16) (actual time=0.001..0.090 rows=402 loops=5,628)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
24. 1.841 2.456 ↓ 21.6 5,718 1

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=260.08..977.56 rows=265 width=16) (actual time=0.716..2.456 rows=5,718 loops=1)

  • Recheck Cond: ((program_id = 121) AND (learner_id = ANY ('{682,685,686,684,691,689,679,683,690,678,687,688,680,681}'::integer[])))
  • Heap Blocks: exact=348
25. 0.013 0.615 ↓ 0.0 0 1

BitmapAnd (cost=260.08..260.08 rows=265 width=0) (actual time=0.615..0.615 rows=0 loops=1)

26. 0.305 0.305 ↓ 1.0 5,718 1

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..106.69 rows=5,636 width=0) (actual time=0.305..0.305 rows=5,718 loops=1)

  • Index Cond: (program_id = 121)
27. 0.297 0.297 ↓ 1.1 5,938 1

Bitmap Index Scan on index_task_interactions_on_learner_id (cost=0.00..153.01 rows=5,225 width=0) (actual time=0.296..0.297 rows=5,938 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 86.240 86.240 ↓ 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=79) (actual time=0.005..0.010 rows=9 loops=8,624)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
30. 0.121 1.818 ↓ 19.9 397 1

Hash (cost=344.68..344.68 rows=20 width=16) (actual time=1.818..1.818 rows=397 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
31. 0.152 1.697 ↓ 19.9 397 1

GroupAggregate (cost=344.08..344.48 rows=20 width=16) (actual time=1.496..1.697 rows=397 loops=1)

  • Group Key: completions.learner_id, completions.task_id
32. 0.315 1.545 ↓ 19.9 397 1

Sort (cost=344.08..344.13 rows=20 width=16) (actual time=1.489..1.545 rows=397 loops=1)

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

Seq Scan on completions (cost=0.00..343.65 rows=20 width=16) (actual time=0.426..1.230 rows=397 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,558
Planning time : 5.431 ms
Execution time : 1,282.357 ms