explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XpGP : LTC - task_interactions.learner_id indexed

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.189 526.449 ↓ 14.0 14 1

GroupAggregate (cost=1,744.79..1,744.91 rows=1 width=57) (actual time=526.266..526.449 rows=14 loops=1)

  • Group Key: program_memberships.user_id
  • loops=14)
  • rows=44 loops=1)
  • rows=395 loops=5,530)
2. 0.135 526.260 ↓ 616.0 616 1

Sort (cost=1,744.79..1,744.79 rows=1 width=46) (actual time=526.241..526.260 rows=616 loops=1)

  • Sort Key: program_memberships.user_id
  • Sort Method: quicksort Memory: 73kB
3. 0.771 526.125 ↓ 616.0 616 1

Nested Loop Left Join (cost=1,303.49..1,744.78 rows=1 width=46) (actual time=3.338..526.125 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
4. 0.009 0.018 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships (cost=4.41..19.66 rows=1 width=16) (actual time=0.013..0.018 rows=14 loops=1)

  • Recheck Cond: (program_id = 121)
  • Filter: ((learner IS TRUE) AND (user_id = ANY ('{678,679,680,681,682,683,684,685,686,687,688,689,690,691}'::bigint[])))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=2
5. 0.009 0.009 ↑ 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.009..0.009 rows=17 loops=1)

  • Index Cond: (program_id = 121)
6. 4.248 525.336 ↓ 14.0 616 14

Hash Left Join (cost=1,299.09..1,724.34 rows=44 width=54) (actual time=0.488..37.524 rows=616 loops=14)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
7. 2.520 520.170 ↓ 14.0 616 14

Nested Loop Left Join (cost=956.15..1,380.07 rows=44 width=147) (actual time=0.421..37.155 rows=616 loops=14)

  • -> Nested Loop (cost=120.78..239.94 rows=44 width=76) (actual time=0.237..6.306 rows=616
8. 0.056 0.210 ↓ 14.0 14 14

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

9. 0.028 0.028 ↑ 1.0 1 14

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

  • Index Cond: (id = 121)
10. 0.098 0.126 ↓ 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.004..0.009 rows=14 loops=14)

  • Recheck Cond: (program_id = 121)
  • Filter: (learner AND ((program_id)::integer = 121) AND (user_id = ANY ('{678,679,680,681,682,683,684,685,686,687,688,689,690,691}'::bigint[])))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=28
11. 0.028 0.028 ↑ 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.002..0.002 rows=17 loops=14)

  • Index Cond: (program_id = 121)
12. 1.930 86.240 ↑ 1.0 44 196

Hash Right Join (cost=116.23..211.58 rows=44 width=42) (actual time=0.224..0.440 rows=44 loops=196)

  • Hash Cond: (tasks_1.id = tasks.id)
13. 45.472 84.280 ↑ 1.0 44 196

Hash Right Join (cost=58.11..153.36 rows=44 width=29) (actual time=0.223..0.430 rows=44 loops=196)

  • Hash Cond: (date_references.id = tasks_1.date_reference_id)
  • -> Hash (cost=57.56..57.56 rows=44 width=12) (actual time=0.020..0.020
14. 38.794 38.808 ↑ 1.0 3,918 196

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
15. 0.014 0.014 ↑ 1.0 44 1

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

  • Index Cond: (program_id = 121)
16. 0.008 0.030 ↑ 1.0 44 1

Hash (cost=57.56..57.56 rows=44 width=17) (actual time=0.030..0.030 rows=44 loops=1)

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

Index Scan using index_tasks_on_program_id on tasks (cost=0.28..57.56 rows=44 width=17) (actual time=0.008..0.022 rows=44 loops=1)

  • Index Cond: (program_id = 121)
18. 395.639 431.200 ↑ 1.0 1 8,624

Hash Join (cost=835.37..842.29 rows=1 width=79) (actual time=0.028..0.050 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))
  • -> HashAggregate (cost=826.92..829.13 rows=221 width=16) (actual time=0.001..0.041
  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
19. 0.683 1.065 ↓ 24.0 5,363 1

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=197.48..825.24 rows=223 width=16) (actual time=0.409..1.065 rows=5,363 loops=1)

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

BitmapAnd (cost=197.48..197.48 rows=223 width=0) (actual time=0.382..0.382 rows=0 loops=1)

21. 0.200 0.200 ↓ 1.2 5,581 1

Bitmap Index Scan on index_task_interactions_on_learner_id (cost=0.00..96.18 rows=4,576 width=0) (actual time=0.200..0.200 rows=5,581 loops=1)

  • Index Cond: (learner_id = ANY ('{678,679,680,681,682,683,684,685,686,687,688,689,690,691}'::integer[]))
22. 0.171 0.171 ↑ 1.0 5,363 1

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..100.94 rows=5,403 width=0) (actual time=0.171..0.171 rows=5,363 loops=1)

  • Index Cond: (program_id = 121)
23. 8.624 34.496 ↓ 9.0 9 8,624

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 25.872 25.872 ↓ 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.001..0.003 rows=9 loops=8,624)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
25. 0.049 0.918 ↓ 19.6 392 1

Hash (cost=342.64..342.64 rows=20 width=16) (actual time=0.918..0.918 rows=392 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
26. 0.081 0.869 ↓ 19.6 392 1

GroupAggregate (cost=342.04..342.44 rows=20 width=16) (actual time=0.778..0.869 rows=392 loops=1)

  • Group Key: completions.learner_id, completions.task_id
27. 0.106 0.788 ↓ 19.6 392 1

Sort (cost=342.04..342.09 rows=20 width=16) (actual time=0.776..0.788 rows=392 loops=1)

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

Seq Scan on completions (cost=0.00..341.61 rows=20 width=16) (actual time=0.244..0.682 rows=392 loops=1)

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