explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UkoL : LTC

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.466 15,894.681 ↓ 14.0 14 1

GroupAggregate (cost=2,425.40..3,078.22 rows=1 width=57) (actual time=1,212.611..15,894.681 rows=14 loops=1)

  • Group Key: program_memberships.user_id
2. 15,894.191 15,894.191 ↓ 616.0 616 1

Nested Loop Left Join (cost=2,425.40..3,078.11 rows=1 width=46) (actual time=3.928..15,894.191 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
  • -> Index Scan using index_program_memberships_on_program_and_user on program_memberships (cost=0.28..41.98
3. 0.000 0.024 ↓ 0.0 14 1

rows=1 width=16) (cost=0..0 rows=0 width=0) (actual time=0.009..0.024 rows=14 loops=1)

  • Index 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
4. 24.220 15,893.052 ↓ 14.0 616 14

Nested Loop Left Join (cost=2,425.12..3,035.37 rows=44 width=54) (actual time=2.248..1,135.218 rows=616 loops=14)

  • Join Filter: (tasks_1.id = tasks.id)
  • Rows Removed by Join Filter: 26,488
5. 4.350 15,851.584 ↓ 14.0 616 14

Hash Left Join (cost=2,367.01..2,851.76 rows=44 width=83) (actual time=2.220..1,132.256 rows=616 loops=14)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
6. 14.546 15,846.362 ↓ 14.0 616 14

Nested Loop Left Join (cost=2,024.07..2,508.04 rows=44 width=122) (actual time=2.156..1,131.883 rows=616 loops=14)

7. 2.450 6.776 ↓ 14.0 616 14

Nested Loop (cost=4.83..85.92 rows=44 width=51) (actual time=0.015..0.484 rows=616 loops=14)

8. 0.112 0.406 ↓ 14.0 14 14

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

9. 0.042 0.042 ↑ 1.0 1 14

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

  • Index Cond: (id = 121)
10. 0.196 0.252 ↓ 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.006..0.018 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.056 0.056 ↑ 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.004..0.004 rows=17 loops=14)

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

Index Scan using index_tasks_on_program_id on tasks (cost=0.28..57.56 rows=44 width=17) (actual time=0.003..0.020 rows=44 loops=196)

  • Index Cond: (program_id = 121)
13. 176.260 15,825.040 ↑ 1.0 1 8,624

Hash Join (cost=2,019.24..2,028.39 rows=1 width=79) (actual time=1.595..1.835 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))
14. 3,163.160 15,605.660 ↓ 1.8 395 5,530

GroupAggregate (cost=2,010.78..2,015.22 rows=221 width=16) (actual time=2.078..2.822 rows=395 loops=5,530)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
15. 5,574.240 12,442.500 ↓ 24.0 5,363 5,530

Sort (cost=2,010.78..2,011.34 rows=223 width=16) (actual time=2.077..2.250 rows=5,363 loops=5,530)

  • Sort Key: task_interactions_1.learner_id, task_interactions_1.task_id
  • Sort Method: quicksort Memory: 444kB
16. 6,000.050 6,868.260 ↓ 24.0 5,363 5,530

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=101.00..2,002.09 rows=223 width=16) (actual time=0.180..1.242 rows=5,363 loops=5,530)

  • Recheck Cond: (program_id = 121)
  • Filter: (learner_id = ANY ('{678,679,680,681,682,683,684,685,686,687,688,689,690,691}'::integer[]))
  • Heap Blocks: exact=1,869,140
17. 868.210 868.210 ↑ 1.0 5,363 5,530

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

  • Index Cond: (program_id = 121)
18. 17.248 43.120 ↓ 9.0 9 8,624

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

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

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
20. 0.042 0.872 ↓ 19.6 392 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
21. 0.083 0.830 ↓ 19.6 392 1

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

  • Group Key: completions.learner_id, completions.task_id
22. 0.097 0.747 ↓ 19.6 392 1

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

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

Seq Scan on completions (cost=0.00..341.61 rows=20 width=16) (actual time=0.246..0.650 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
24. 16.717 17.248 ↑ 1.0 44 8,624

Materialize (cost=58.11..153.58 rows=44 width=29) (actual time=0.000..0.002 rows=44 loops=8,624)

25. 0.238 0.531 ↑ 1.0 44 1

Hash Right Join (cost=58.11..153.36 rows=44 width=29) (actual time=0.314..0.531 rows=44 loops=1)

  • Hash Cond: (date_references.id = tasks_1.date_reference_id)
26. 0.268 0.268 ↑ 1.0 3,918 1

Seq Scan on date_references (cost=0.00..80.18 rows=3,918 width=33) (actual time=0.003..0.268 rows=3,918 loops=1)

27. 0.007 0.025 ↑ 1.0 44 1

Hash (cost=57.56..57.56 rows=44 width=12) (actual time=0.025..0.025 rows=44 loops=1)

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

  • Index Cond: (program_id = 121)
Planning time : 4.607 ms
Execution time : 15,894.883 ms