explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 39k2r : slow members page faythe

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 112.442 ↓ 14.0 14 1

Sort (cost=2,124.05..2,124.05 rows=1 width=74) (actual time=112.441..112.442 rows=14 loops=1)

  • Sort Key: users.first_name, users.last_name
  • Sort Method: quicksort Memory: 26kB
2.          

CTE learner_task_count

3. 0.427 112.037 ↓ 14.0 14 1

GroupAggregate (cost=2,107.15..2,107.27 rows=1 width=57) (actual time=111.601..112.037 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 0.618 111.610 ↓ 616.0 616 1

Sort (cost=2,107.15..2,107.16 rows=1 width=46) (actual time=111.538..111.610 rows=616 loops=1)

  • Sort Key: program_memberships.user_id
  • Sort Method: quicksort Memory: 73kB
5. 0.507 110.992 ↓ 616.0 616 1

Hash Right Join (cost=1,567.46..2,107.14 rows=1 width=46) (actual time=9.668..110.992 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.617 110.422 ↓ 14.0 616 1

Hash Left Join (cost=1,547.72..2,087.17 rows=44 width=54) (actual time=9.587..110.422 rows=616 loops=1)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
7. 1.163 106.641 ↓ 14.0 616 1

Nested Loop Left Join (cost=1,167.75..1,705.86 rows=44 width=147) (actual time=7.373..106.641 rows=616 loops=1)

8. 0.385 17.390 ↓ 14.0 616 1

Nested Loop (cost=121.01..240.24 rows=44 width=76) (actual time=0.931..17.390 rows=616 loops=1)

9. 0.031 0.121 ↓ 14.0 14 1

Nested Loop (cost=4.55..27.99 rows=1 width=38) (actual time=0.037..0.121 rows=14 loops=1)

10. 0.018 0.018 ↑ 1.0 1 1

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

  • Index Cond: (id = 121)
11. 0.062 0.072 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships program_memberships_1 (cost=4.41..19.81 rows=1 width=24) (actual time=0.016..0.072 rows=14 loops=1)

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

  • Index Cond: (program_id = 121)
13. 0.520 16.884 ↑ 1.0 44 14

Hash Right Join (cost=116.46..211.82 rows=44 width=42) (actual time=0.596..1.206 rows=44 loops=14)

  • Hash Cond: (tasks_1.id = tasks.id)
14. 8.387 16.254 ↑ 1.0 44 14

Hash Right Join (cost=58.23..153.47 rows=44 width=29) (actual time=0.585..1.161 rows=44 loops=14)

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

Seq Scan on date_references (cost=0.00..80.18 rows=3,918 width=33) (actual time=0.005..0.558 rows=3,918 loops=14)

16. 0.018 0.055 ↑ 1.0 44 1

Hash (cost=57.68..57.68 rows=44 width=12) (actual time=0.055..0.055 rows=44 loops=1)

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

Index Scan using index_tasks_on_program_id on tasks tasks_1 (cost=0.28..57.68 rows=44 width=12) (actual time=0.009..0.037 rows=44 loops=1)

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

Hash (cost=57.68..57.68 rows=44 width=17) (actual time=0.110..0.110 rows=44 loops=1)

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

Index Scan using index_tasks_on_program_id on tasks (cost=0.28..57.68 rows=44 width=17) (actual time=0.034..0.092 rows=44 loops=1)

  • Index Cond: (program_id = 121)
20. 32.648 88.088 ↑ 1.0 1 616

Hash Join (cost=1,046.74..1,056.25 rows=1 width=79) (actual time=0.087..0.143 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. 41.958 45.584 ↓ 1.3 407 407

HashAggregate (cost=1,038.28..1,041.32 rows=304 width=16) (actual time=0.016..0.112 rows=407 loops=407)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
22. 2.706 3.626 ↓ 19.4 5,974 1

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=232.02..1,035.97 rows=308 width=16) (actual time=0.982..3.626 rows=5,974 loops=1)

  • Recheck Cond: ((program_id = 121) AND (learner_id = ANY ('{677,680,687,682,691,689,685,678,679,686,688,681,829,683,286,690,684}'::integer[])))
  • Heap Blocks: exact=356
23. 0.020 0.920 ↓ 0.0 0 1

BitmapAnd (cost=232.02..232.02 rows=308 width=0) (actual time=0.920..0.920 rows=0 loops=1)

24. 0.426 0.426 ↓ 1.0 5,974 1

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..112.02 rows=5,814 width=0) (actual time=0.426..0.426 rows=5,974 loops=1)

  • Index Cond: (program_id = 121)
25. 0.474 0.474 ↓ 1.1 6,721 1

Bitmap Index Scan on index_task_interactions_on_learner_id (cost=0.00..119.59 rows=5,936 width=0) (actual time=0.474..0.474 rows=6,721 loops=1)

  • Index Cond: (learner_id = ANY ('{677,680,687,682,691,689,685,678,679,686,688,681,829,683,286,690,684}'::integer[]))
26. 3.080 9.856 ↓ 10.0 10 616

Hash (cost=8.44..8.44 rows=1 width=79) (actual time=0.016..0.016 rows=10 loops=616)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 6.776 6.776 ↓ 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=79) (actual time=0.006..0.011 rows=10 loops=616)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
28. 0.150 2.164 ↓ 16.7 401 1

Hash (cost=379.62..379.62 rows=24 width=16) (actual time=2.164..2.164 rows=401 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
29. 0.208 2.014 ↓ 16.7 401 1

GroupAggregate (cost=378.90..379.38 rows=24 width=16) (actual time=1.772..2.014 rows=401 loops=1)

  • Group Key: completions.learner_id, completions.task_id
30. 0.326 1.806 ↓ 16.7 401 1

Sort (cost=378.90..378.96 rows=24 width=16) (actual time=1.763..1.806 rows=401 loops=1)

  • Sort Key: completions.learner_id, completions.task_id
  • Sort Method: quicksort Memory: 43kB
31. 1.480 1.480 ↓ 16.7 401 1

Seq Scan on completions (cost=0.00..378.35 rows=24 width=16) (actual time=0.558..1.480 rows=401 loops=1)

  • Filter: ((program_id = 121) AND (learner_id = ANY ('{677,680,687,682,691,689,685,678,679,686,688,681,829,683,286,690,684}'::integer[])))
  • Rows Removed by Filter: 8,587
32. 0.008 0.063 ↓ 14.0 14 1

Hash (cost=19.73..19.73 rows=1 width=16) (actual time=0.063..0.063 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.021 0.055 ↓ 14.0 14 1

Bitmap Heap Scan on program_memberships (cost=4.41..19.73 rows=1 width=16) (actual time=0.047..0.055 rows=14 loops=1)

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

  • Index Cond: (program_id = 121)
35. 0.018 112.377 ↓ 14.0 14 1

Nested Loop Left Join (cost=0.55..16.77 rows=1 width=74) (actual time=111.764..112.377 rows=14 loops=1)

36. 0.032 112.247 ↓ 14.0 14 1

Nested Loop (cost=0.28..8.33 rows=1 width=70) (actual time=111.698..112.247 rows=14 loops=1)

37. 112.075 112.075 ↓ 14.0 14 1

CTE Scan on learner_task_count (cost=0.00..0.02 rows=1 width=57) (actual time=111.624..112.075 rows=14 loops=1)

38. 0.140 0.140 ↑ 1.0 1 14

Index Scan using users_pkey on users (cost=0.28..8.29 rows=1 width=17) (actual time=0.010..0.010 rows=1 loops=14)

  • Index Cond: (id = learner_task_count.learner_id)
39. 0.112 0.112 ↑ 1.0 1 14

Index Only Scan using index_mentorings on mentorings mentoring (cost=0.28..8.43 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=14)

  • Index Cond: ((program_id = 121) AND (learner_id = learner_task_count.learner_id))
  • Heap Fetches: 14
Planning time : 8.393 ms
Execution time : 113.173 ms