explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FClA : slow members page production

Settings
# exclusive inclusive rows x rows loops node
1. 0.076 45,145.183 ↓ 14.0 14 1

Sort (cost=2,335.55..2,335.56 rows=1 width=75) (actual time=45,145.182..45,145.183 rows=14 loops=1)

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

CTE learner_task_count

3. 1.782 45,144.531 ↓ 14.0 14 1

GroupAggregate (cost=1,670.75..2,318.78 rows=1 width=57) (actual time=4,085.020..45,144.531 rows=14 loops=1)

  • Group Key: program_memberships.user_id
4. 5.281 45,142.749 ↓ 616.0 616 1

Nested Loop Left Join (cost=1,670.75..2,318.67 rows=1 width=46) (actual time=12.669..45,142.749 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
5. 0.068 0.068 ↓ 14.0 14 1

Index Scan using index_program_memberships_on_program_and_user on program_memberships (cost=0.28..42.51 rows=1 width=16) (actual time=0.040..0.068 rows=14 loops=1)

  • Index 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
6. 27.896 45,137.400 ↓ 13.7 616 14

Hash Left Join (cost=1,670.47..2,275.37 rows=45 width=54) (actual time=7.226..3,224.100 rows=616 loops=14)

  • Hash Cond: ((program_memberships_1.user_id = completions.learner_id) AND (tasks.id = completions.task_id))
7. 305.942 45,107.706 ↓ 13.7 616 14

Nested Loop Left Join (cost=1,261.77..1,865.31 rows=45 width=452) (actual time=7.093..3,221.979 rows=616 loops=14)

8. 7.168 250.180 ↓ 13.7 616 14

Nested Loop (cost=135.90..261.98 rows=45 width=76) (actual time=0.568..17.870 rows=616 loops=14)

9. 0.280 1.148 ↓ 14.0 14 14

Nested Loop (cost=4.54..29.83 rows=1 width=38) (actual time=0.028..0.082 rows=14 loops=14)

10. 0.126 0.126 ↑ 1.0 1 14

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

  • Index Cond: (id = 121)
11. 0.602 0.742 ↓ 14.0 14 14

Bitmap Heap Scan on program_memberships program_memberships_1 (cost=4.40..21.66 rows=1 width=24) (actual time=0.015..0.053 rows=14 loops=14)

  • 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=28
12. 0.140 0.140 ↓ 1.1 17 14

Bitmap Index Scan on index_program_memberships_on_program_and_user (cost=0.00..4.40 rows=16 width=0) (actual time=0.010..0.010 rows=17 loops=14)

  • Index Cond: (program_id = 121)
13. 13.595 241.864 ↑ 1.0 44 196

Hash Right Join (cost=131.35..231.71 rows=45 width=42) (actual time=0.492..1.234 rows=44 loops=196)

  • Hash Cond: (tasks_1.id = tasks.id)
14. 129.317 228.144 ↑ 1.0 44 196

Hash Right Join (cost=65.68..165.91 rows=45 width=29) (actual time=0.490..1.164 rows=44 loops=196)

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

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

16. 0.012 0.043 ↑ 1.0 44 1

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

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

  • Index Cond: (program_id = 121)
18. 0.016 0.125 ↑ 1.0 44 1

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

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

  • Index Cond: (program_id = 121)
20. 489.720 44,551.584 ↑ 1.0 1 8,624

Hash Join (cost=1,125.87..1,136.47 rows=1 width=384) (actual time=4.618..5.166 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))
21. 7,076.916 43,829.016 ↓ 1.6 407 5,698

GroupAggregate (cost=1,117.42..1,122.57 rows=256 width=16) (actual time=5.994..7.692 rows=407 loops=5,698)

  • Group Key: task_interactions_1.learner_id, task_interactions_1.task_id
22. 13,316.226 36,752.100 ↓ 23.8 6,168 5,698

Sort (cost=1,117.42..1,118.06 rows=259 width=16) (actual time=5.989..6.450 rows=6,168 loops=5,698)

  • Sort Key: task_interactions_1.learner_id, task_interactions_1.task_id
  • Sort Method: quicksort Memory: 482kB
23. 17,903.116 23,435.874 ↓ 23.8 6,168 5,698

Bitmap Heap Scan on task_interactions task_interactions_1 (cost=236.57..1,107.03 rows=259 width=16) (actual time=1.091..4.113 rows=6,168 loops=5,698)

  • Recheck Cond: ((learner_id = ANY ('{677,680,687,682,691,689,685,678,679,686,688,681,829,683,286,690,684}'::integer[])) AND (program_id = 121))
  • Heap Blocks: exact=5,464,382
24. 284.900 5,532.758 ↓ 0.0 0 5,698

BitmapAnd (cost=236.57..236.57 rows=259 width=0) (actual time=0.971..0.971 rows=0 loops=5,698)

25. 2,797.718 2,797.718 ↓ 1.3 6,915 5,698

Bitmap Index Scan on index_task_interactions_on_learner_id (cost=0.00..114.88 rows=5,312 width=0) (actual time=0.491..0.491 rows=6,915 loops=5,698)

  • Index Cond: (learner_id = ANY ('{677,680,687,682,691,689,685,678,679,686,688,681,829,683,286,690,684}'::integer[]))
26. 2,450.140 2,450.140 ↓ 1.1 6,168 5,698

Bitmap Index Scan on index_task_interactions_on_program_id (cost=0.00..121.32 rows=5,470 width=0) (actual time=0.430..0.430 rows=6,168 loops=5,698)

  • Index Cond: (program_id = 121)
27. 68.992 232.848 ↓ 10.0 10 8,624

Hash (cost=8.44..8.44 rows=1 width=384) (actual time=0.027..0.027 rows=10 loops=8,624)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 163.856 163.856 ↓ 10.0 10 8,624

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.012..0.019 rows=10 loops=8,624)

  • Index Cond: ((task_id = tasks.id) AND (learner_id = program_memberships_1.user_id))
29. 0.072 1.798 ↓ 16.7 401 1

Hash (cost=408.35..408.35 rows=24 width=16) (actual time=1.798..1.798 rows=401 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
30. 0.145 1.726 ↓ 16.7 401 1

GroupAggregate (cost=407.63..408.11 rows=24 width=16) (actual time=1.560..1.726 rows=401 loops=1)

  • Group Key: completions.learner_id, completions.task_id
31. 0.212 1.581 ↓ 16.7 401 1

Sort (cost=407.63..407.69 rows=24 width=16) (actual time=1.554..1.581 rows=401 loops=1)

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

Seq Scan on completions (cost=0.00..407.08 rows=24 width=16) (actual time=0.552..1.369 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,606
33. 0.037 45,145.107 ↓ 14.0 14 1

Nested Loop Left Join (cost=0.55..16.77 rows=1 width=75) (actual time=4,085.118..45,145.107 rows=14 loops=1)

34. 0.064 45,144.902 ↓ 14.0 14 1

Nested Loop (cost=0.28..8.33 rows=1 width=71) (actual time=4,085.063..45,144.902 rows=14 loops=1)

35. 45,144.572 45,144.572 ↓ 14.0 14 1

CTE Scan on learner_task_count (cost=0.00..0.02 rows=1 width=57) (actual time=4,085.024..45,144.572 rows=14 loops=1)

36. 0.266 0.266 ↑ 1.0 1 14

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

  • Index Cond: (id = learner_task_count.learner_id)
37. 0.168 0.168 ↑ 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.011..0.012 rows=1 loops=14)

  • Index Cond: ((program_id = 121) AND (learner_id = learner_task_count.learner_id))
  • Heap Fetches: 14
Planning time : 25.521 ms
Execution time : 45,145.810 ms