explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Fa8

Settings
# exclusive inclusive rows x rows loops node
1. 108.053 28,679.108 ↑ 8.5 2 1

Unique (cost=184,658.49..184,658.79 rows=17 width=72) (actual time=28,465.584..28,679.108 rows=2 loops=1)

2. 329.164 28,571.055 ↓ 4,743.5 80,640 1

Sort (cost=184,658.49..184,658.53 rows=17 width=72) (actual time=28,465.580..28,571.055 rows=80,640 loops=1)

  • Sort Key: proficiency_workflows.id, proficiency_workflows.objective_id, proficiency_workflows.employee_id, proficiency_workflows.state, proficiency_workflows.created_at, proficiency_workflows.updated_at
  • Sort Method: external merge Disk: 5216kB
3. 14,593.379 28,241.891 ↓ 4,743.5 80,640 1

Merge Right Join (cost=136,056.20..184,658.14 rows=17 width=72) (actual time=384.092..28,241.891 rows=80,640 loops=1)

  • Merge Cond: (demonstration_template_evaluators.employee_role_id = employee_roles.id)
  • Filter: ((((proficiency_workflows.state)::text = ANY ('{initial_signoff,training,final_signoff}'::text[])) AND (signoffs.id IS NULL) AND (((signoff_templates.role_id = 5) AND (roles.id = 5) AND (proficiency_workflows.objective_id = ANY ('{11,12,10,2,15,13,5,8,6,16,4,1,3,14,9,7}'::bigint[]))) OR ((signoff_templates.role_id = 2) AND (proficiency_workflows.employee_id = 4)))) OR (((proficiency_workflows.state)::text = 'training'::text) AND ((trainings.status)::text = 'pending_signoff'::text) AND (signoffs.id IS NULL) AND (((signoff_templates.role_id = 5) AND (roles.id = 5) AND (proficiency_workflows.objective_id = ANY ('{11,12,10,2,15,13,5,8,6,16,4,1,3,14,9,7}'::bigint[]))) OR ((signoff_templates.role_id = 2) AND (roles.id = 2) AND (skill_trainers.objective_id = proficiency_workflows.objective_id)) OR ((signoff_templates.role_id = 2) AND (proficiency_workflows.employee_id = 4)))) OR (((proficiency_workflows.state)::text = 'evaluation'::text) AND ((quizzes.status)::text = 'pending_signoff'::text) AND (quiz_signoffs.id IS NULL) AND (((quiz_signoff_templates.role_id = 5) AND (roles.id = 5) AND (proficiency_workflows.objective_id = ANY ('{11,12,10,2,15,13,5,8,6,16,4,1,3,14,9,7}'::bigint[]))) OR ((quiz_signoff_templates.role_id = 4) AND (roles.id = 4) AND (quiz_template_evaluators.quiz_template_id = quiz_templates.id)) OR ((quiz_signoff_templates.role_id = 2) AND (proficiency_workflows.employee_id = 4)))) OR (((proficiency_workflows.state)::text = 'evaluation'::text) AND ((demonstrations.status)::text = 'pending_signoff'::text) AND (demonstration_signoffs.id IS NULL) AND (((demonstration_signoff_templates.role_id = 5) AND (roles.id = 5) AND (proficiency_workflows.objective_id = ANY ('{11,12,10,2,15,13,5,8,6,16,4,1,3,14,9,7}'::bigint[]))) OR ((demonstration_signoff_templates.role_id = 4) AND (roles.id = 4) AND (demonstration_template_evaluators.demonstration_template_id = demonstration_templates.id)) OR ((demonstration_signoff_templates.role_id = 2) AND (proficiency_workflows.employee_id = 4)))) OR (((trainings.status)::text = 'in_training'::text) AND (roles.id = 2) AND (skill_trainers.objective_id = proficiency_workflows.objective_id)) OR (((quizzes.status)::text = ANY ('{preparation,in_progress}'::text[])) AND (proficiency_workflows.employee_id = 4)) OR (((demonstrations.status)::text = 'ready_to_demo'::text) AND (roles.id = 4) AND (demonstration_template_evaluators.demonstration_template_id = demonstration_templates.id)))
  • Rows Removed by Filter: 8144640
4. 0.142 0.142 ↑ 66.9 16 1

Index Scan using index_demonstration_template_evaluators_on_employee_role_id on demonstration_template_evaluators (cost=0.15..64.20 rows=1,070 width=16) (actual time=0.013..0.142 rows=16 loops=1)

5. 11,419.575 13,648.370 ↓ 23.8 8,225,265 1

Materialize (cost=136,056.05..145,558.33 rows=345,074 width=264) (actual time=140.119..13,648.370 rows=8,225,265 loops=1)

6. 1,338.494 2,228.795 ↓ 1.5 514,080 1

Merge Left Join (cost=136,056.05..144,695.65 rows=345,074 width=264) (actual time=140.114..2,228.795 rows=514,080 loops=1)

  • Merge Cond: (employee_roles.id = quiz_template_evaluators.employee_role_id)
7. 84.301 272.091 ↑ 10.7 32,130 1

Merge Left Join (cost=136,055.90..140,775.01 rows=345,074 width=256) (actual time=140.094..272.091 rows=32,130 loops=1)

  • Merge Cond: (employee_roles.id = skill_trainers.employee_role_id)
8. 106.159 187.698 ↑ 10.7 32,130 1

Sort (cost=135,981.36..136,844.04 rows=345,074 width=248) (actual time=139.975..187.698 rows=32,130 loops=1)

  • Sort Key: employee_roles.id
  • Sort Method: external sort Disk: 4856kB
9. 48.110 81.539 ↑ 10.7 32,130 1

Hash Right Join (cost=17,596.82..24,034.51 rows=345,074 width=248) (actual time=32.910..81.539 rows=32,130 loops=1)

  • Hash Cond: (demonstration_templates.objective_id = objectives.id)
10. 0.381 1.388 ↑ 10.5 81 1

Hash Right Join (cost=78.07..102.59 rows=850 width=64) (actual time=0.770..1.388 rows=81 loops=1)

  • Hash Cond: (demonstration_signoff_templates.demonstration_template_id = demonstration_templates.id)
11. 0.269 0.830 ↑ 2.6 208 1

Hash Right Join (cost=22.38..39.33 rows=550 width=24) (actual time=0.566..0.830 rows=208 loops=1)

  • Hash Cond: (demonstration_signoffs.signoff_template_id = demonstration_signoff_templates.id)
12. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on signoffs demonstration_signoffs (cost=0.00..15.50 rows=550 width=16) (actual time=0.003..0.004 rows=0 loops=1)

13. 0.269 0.557 ↑ 2.6 208 1

Hash (cost=15.50..15.50 rows=550 width=24) (actual time=0.556..0.557 rows=208 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
14. 0.288 0.288 ↑ 2.6 208 1

Seq Scan on signoff_templates demonstration_signoff_templates (cost=0.00..15.50 rows=550 width=24) (actual time=0.012..0.288 rows=208 loops=1)

15. 0.038 0.177 ↑ 31.5 27 1

Hash (cost=45.07..45.07 rows=850 width=48) (actual time=0.176..0.177 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
16. 0.059 0.139 ↑ 31.5 27 1

Hash Right Join (cost=29.12..45.07 rows=850 width=48) (actual time=0.071..0.139 rows=27 loops=1)

  • Hash Cond: (demonstrations.demonstration_template_id = demonstration_templates.id)
17. 0.022 0.022 ↑ 39.2 12 1

Seq Scan on demonstrations (cost=0.00..14.70 rows=470 width=40) (actual time=0.006..0.022 rows=12 loops=1)

18. 0.024 0.058 ↑ 53.1 16 1

Hash (cost=18.50..18.50 rows=850 width=16) (actual time=0.057..0.058 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.034 0.034 ↑ 53.1 16 1

Seq Scan on demonstration_templates (cost=0.00..18.50 rows=850 width=16) (actual time=0.013..0.034 rows=16 loops=1)

20. 2.335 32.041 ↑ 55.2 1,470 1

Hash (cost=14,282.82..14,282.82 rows=81,194 width=200) (actual time=32.040..32.041 rows=1,470 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 275kB
21. 3.508 29.706 ↑ 55.2 1,470 1

Hash Right Join (cost=11,767.07..14,282.82 rows=81,194 width=200) (actual time=26.246..29.706 rows=1,470 loops=1)

  • Hash Cond: (trainings.proficiency_workflow_id = proficiency_workflows.id)
22. 0.029 0.029 ↑ 65.0 8 1

Seq Scan on trainings (cost=0.00..15.20 rows=520 width=40) (actual time=0.013..0.029 rows=8 loops=1)

23. 2.394 26.169 ↑ 55.2 1,470 1

Hash (cost=8,849.15..8,849.15 rows=81,194 width=168) (actual time=26.167..26.169 rows=1,470 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 274kB
24. 3.277 23.775 ↑ 55.2 1,470 1

Hash Right Join (cost=6,009.58..8,849.15 rows=81,194 width=168) (actual time=20.534..23.775 rows=1,470 loops=1)

  • Hash Cond: (quiz_signoffs.signoff_template_id = quiz_signoff_templates.id)
25. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on signoffs quiz_signoffs (cost=0.00..15.50 rows=550 width=16) (actual time=0.005..0.006 rows=0 loops=1)

26. 2.555 20.492 ↑ 55.2 1,470 1

Hash (cost=3,091.66..3,091.66 rows=81,194 width=168) (actual time=20.490..20.492 rows=1,470 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 271kB
27. 3.821 17.937 ↑ 55.2 1,470 1

Merge Right Join (cost=844.57..3,091.66 rows=81,194 width=168) (actual time=3.165..17.937 rows=1,470 loops=1)

  • Merge Cond: (quizzes.quiz_template_id = quiz_templates.id)
28. 0.049 0.049 ↑ 30.5 20 1

Index Scan using index_quizzes_on_quiz_template_id on quizzes (cost=0.15..57.30 rows=610 width=40) (actual time=0.007..0.049 rows=20 loops=1)

29. 2.728 14.067 ↑ 56.0 1,451 1

Materialize (cost=844.42..2,218.39 rows=81,194 width=136) (actual time=3.154..14.067 rows=1,451 loops=1)

30. 3.040 11.339 ↑ 120.8 672 1

Nested Loop Left Join (cost=844.42..2,015.40 rows=81,194 width=136) (actual time=3.150..11.339 rows=672 loops=1)

31. 2.090 6.283 ↑ 13.4 672 1

Merge Left Join (cost=829.54..966.56 rows=9,023 width=120) (actual time=3.097..6.283 rows=672 loops=1)

  • Merge Cond: (quiz_templates.id = quiz_signoff_templates.quiz_template_id)
32. 0.600 2.750 ↑ 40.3 224 1

Sort (cost=789.01..811.57 rows=9,023 width=104) (actual time=2.477..2.750 rows=224 loops=1)

  • Sort Key: quiz_templates.id
  • Sort Method: quicksort Memory: 56kB
33. 0.341 2.150 ↑ 40.3 224 1

Hash Left Join (cost=110.85..196.23 rows=9,023 width=104) (actual time=1.777..2.150 rows=224 loops=1)

  • Hash Cond: (proficiency_workflows.objective_id = objectives.id)
34. 0.049 0.049 ↑ 25.3 32 1

Seq Scan on proficiency_workflows (cost=0.00..18.10 rows=810 width=72) (actual time=0.009..0.049 rows=32 loops=1)

35. 0.146 1.760 ↑ 11.9 112 1

Hash (cost=94.15..94.15 rows=1,336 width=32) (actual time=1.759..1.760 rows=112 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 23kB
36. 0.171 1.614 ↑ 11.9 112 1

Hash Right Join (cost=57.63..94.15 rows=1,336 width=32) (actual time=1.428..1.614 rows=112 loops=1)

  • Hash Cond: (quiz_templates.objective_id = objectives.id)
37. 0.026 0.026 ↑ 50.6 16 1

Seq Scan on quiz_templates (cost=0.00..18.10 rows=810 width=16) (actual time=0.006..0.026 rows=16 loops=1)

38. 0.143 1.417 ↑ 2.9 112 1

Hash (cost=53.51..53.51 rows=330 width=24) (actual time=1.415..1.417 rows=112 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
39. 0.398 1.274 ↑ 2.9 112 1

Hash Right Join (cost=35.08..53.51 rows=330 width=24) (actual time=0.622..1.274 rows=112 loops=1)

  • Hash Cond: (signoff_templates.objective_id = objectives.id)
40. 0.264 0.820 ↑ 2.6 208 1

Hash Right Join (cost=22.38..39.33 rows=550 width=24) (actual time=0.560..0.820 rows=208 loops=1)

  • Hash Cond: (signoffs.signoff_template_id = signoff_templates.id)
41. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on signoffs (cost=0.00..15.50 rows=550 width=16) (actual time=0.003..0.004 rows=0 loops=1)

42. 0.271 0.552 ↑ 2.6 208 1

Hash (cost=15.50..15.50 rows=550 width=24) (actual time=0.550..0.552 rows=208 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
43. 0.281 0.281 ↑ 2.6 208 1

Seq Scan on signoff_templates (cost=0.00..15.50 rows=550 width=24) (actual time=0.012..0.281 rows=208 loops=1)

44. 0.026 0.056 ↑ 7.5 16 1

Hash (cost=11.20..11.20 rows=120 width=8) (actual time=0.055..0.056 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.030 0.030 ↑ 7.5 16 1

Seq Scan on objectives (cost=0.00..11.20 rows=120 width=8) (actual time=0.007..0.030 rows=16 loops=1)

46. 1.137 1.443 ↓ 1.2 673 1

Sort (cost=40.53..41.91 rows=550 width=24) (actual time=0.600..1.443 rows=673 loops=1)

  • Sort Key: quiz_signoff_templates.quiz_template_id
  • Sort Method: quicksort Memory: 36kB
47. 0.306 0.306 ↑ 2.6 208 1

Seq Scan on signoff_templates quiz_signoff_templates (cost=0.00..15.50 rows=550 width=24) (actual time=0.010..0.306 rows=208 loops=1)

48. 1.962 2.016 ↑ 9.0 1 672

Materialize (cost=14.88..33.77 rows=9 width=16) (actual time=0.001..0.003 rows=1 loops=672)

49. 0.019 0.054 ↑ 9.0 1 1

Hash Right Join (cost=14.88..33.73 rows=9 width=16) (actual time=0.043..0.054 rows=1 loops=1)

  • Hash Cond: (roles.id = employee_roles.role_id)
50. 0.013 0.013 ↑ 140.0 5 1

Seq Scan on roles (cost=0.00..17.00 rows=700 width=8) (actual time=0.006..0.013 rows=5 loops=1)

51. 0.005 0.022 ↑ 9.0 1 1

Hash (cost=14.76..14.76 rows=9 width=12) (actual time=0.021..0.022 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.007 0.017 ↑ 9.0 1 1

Bitmap Heap Scan on employee_roles (cost=4.22..14.76 rows=9 width=12) (actual time=0.015..0.017 rows=1 loops=1)

  • Recheck Cond: (employee_id = 4)
  • Heap Blocks: exact=1
53. 0.010 0.010 ↑ 9.0 1 1

Bitmap Index Scan on index_employee_roles_on_employee_id_and_role_id (cost=0.00..4.22 rows=9 width=0) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (employee_id = 4)
54. 0.053 0.092 ↑ 66.9 16 1

Sort (cost=74.54..77.21 rows=1,070 width=16) (actual time=0.072..0.092 rows=16 loops=1)

  • Sort Key: skill_trainers.employee_role_id
  • Sort Method: quicksort Memory: 25kB
55. 0.039 0.039 ↑ 66.9 16 1

Seq Scan on skill_trainers (cost=0.00..20.70 rows=1,070 width=16) (actual time=0.017..0.039 rows=16 loops=1)

56. 618.175 618.210 ↓ 450.4 481,951 1

Materialize (cost=0.15..66.88 rows=1,070 width=16) (actual time=0.014..618.210 rows=481,951 loops=1)

57. 0.035 0.035 ↑ 66.9 16 1

Index Scan using index_quiz_template_evaluators_on_employee_role_id on quiz_template_evaluators (cost=0.15..64.20 rows=1,070 width=16) (actual time=0.010..0.035 rows=16 loops=1)

Planning time : 4.560 ms
Execution time : 28,696.613 ms