explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bMQYX : Optimization for: test1; plan #wwbI

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.068 9,641.271 ↑ 3.3 47 1

Hash Join (cost=157,753.09..157,774.76 rows=154 width=74) (actual time=9,641.015..9,641.271 rows=47 loops=1)

  • Hash Cond: (p.id = COALESCE((COALESCE(o.signoff_owner, own.owner_id)), (COALESCE((CASE WHEN pref.outof_office_status THEN pref.alternate_approver_id ELSE pref.profile_id END), (CASE WHEN pref_1.outof_office_status THEN pref_1.alternate_approver_id ELSE pref_1.profile_id END)))))
2. 0.920 2.346 ↑ 1.0 732 1

HashAggregate (cost=52.11..59.43 rows=732 width=134) (actual time=2.134..2.346 rows=732 loops=1)

  • Group Key: p.id, p.profile_name, p.emailid, p.identity2, p.first_name, p.last_name, p.profile_language, p.profile_timezone, p.profile_locale, p.variation_id, p.manager_profileid, p.profile_status, p.gdpr_status
3. 1.426 1.426 ↑ 1.0 732 1

Seq Scan on platformservice_profiles p (cost=0.00..28.32 rows=732 width=134) (actual time=0.280..1.426 rows=732 loops=1)

4. 0.011 9,638.857 ↓ 1.1 48 1

Hash (cost=157,700.46..157,700.46 rows=42 width=40) (actual time=9,638.857..9,638.857 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
5. 0.024 9,638.846 ↓ 1.1 48 1

Hash Full Join (cost=157,698.87..157,700.46 rows=42 width=40) (actual time=9,638.817..9,638.846 rows=48 loops=1)

  • Hash Cond: (COALESCE(o.signoff_owner, own.owner_id) = COALESCE((CASE WHEN pref.outof_office_status THEN pref.alternate_approver_id ELSE pref.profile_id END), (CASE WHEN pref_1.outof_office_status THEN pref_1.alternate_approver_id ELSE pref_1.profile_id END)))
6. 0.029 4.761 ↓ 1.1 45 1

Hash Full Join (cost=73.69..74.96 rows=42 width=28) (actual time=4.745..4.761 rows=45 loops=1)

  • Hash Cond: (own.owner_id = o.signoff_owner)
7. 0.016 0.477 ↑ 1.0 39 1

HashAggregate (cost=17.99..18.38 rows=39 width=12) (actual time=0.472..0.477 rows=39 loops=1)

  • Group Key: count(ps.cm_process_id), own.owner_id
8. 0.043 0.461 ↑ 1.0 39 1

HashAggregate (cost=17.40..17.79 rows=39 width=12) (actual time=0.458..0.461 rows=39 loops=1)

  • Group Key: own.owner_id
9. 0.061 0.418 ↑ 1.0 233 1

Hash Join (cost=8.97..16.24 rows=233 width=8) (actual time=0.315..0.418 rows=233 loops=1)

  • Hash Cond: (own.cm_process_id = ps.cm_process_id)
10. 0.300 0.300 ↑ 1.0 233 1

Seq Scan on cm_process_owners own (cost=0.00..4.33 rows=233 width=8) (actual time=0.246..0.300 rows=233 loops=1)

11. 0.020 0.057 ↑ 1.0 221 1

Hash (cost=6.21..6.21 rows=221 width=4) (actual time=0.057..0.057 rows=221 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
12. 0.037 0.037 ↑ 1.0 221 1

Seq Scan on cm_process ps (cost=0.00..6.21 rows=221 width=4) (actual time=0.014..0.037 rows=221 loops=1)

13. 0.010 4.255 ↑ 1.5 28 1

Hash (cost=55.18..55.18 rows=42 width=12) (actual time=4.255..4.255 rows=28 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 0.095 4.245 ↑ 1.5 28 1

HashAggregate (cost=54.34..54.76 rows=42 width=12) (actual time=4.241..4.245 rows=28 loops=1)

  • Group Key: o.signoff_owner
15. 0.112 4.150 ↑ 1.1 370 1

Hash Join (cost=20.34..52.35 rows=399 width=8) (actual time=3.207..4.150 rows=370 loops=1)

  • Hash Cond: (o.cm_process_id = p_1.cm_process_id)
16. 1.141 1.222 ↑ 1.0 399 1

Bitmap Heap Scan on cm_occurrence o (cost=11.37..38.36 rows=399 width=8) (actual time=0.380..1.222 rows=399 loops=1)

  • Recheck Cond: (status_id = 3)
  • Heap Blocks: exact=22
17. 0.081 0.081 ↑ 1.0 399 1

Bitmap Index Scan on idx_wf_cm_occurrence_status_id_cm_occurrence_id (cost=0.00..11.27 rows=399 width=0) (actual time=0.081..0.081 rows=399 loops=1)

  • Index Cond: (status_id = 3)
18. 0.036 2.816 ↑ 1.0 221 1

Hash (cost=6.21..6.21 rows=221 width=4) (actual time=2.816..2.816 rows=221 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
19. 2.780 2.780 ↑ 1.0 221 1

Seq Scan on cm_process p_1 (cost=0.00..6.21 rows=221 width=4) (actual time=2.524..2.780 rows=221 loops=1)

  • Filter: is_active
20. 0.028 9,634.061 ↑ 1.6 17 1

Hash (cost=157,624.83..157,624.83 rows=28 width=28) (actual time=9,634.061..9,634.061 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.048 9,634.033 ↑ 1.6 17 1

Merge Full Join (cost=157,622.95..157,624.83 rows=28 width=28) (actual time=9,631.120..9,634.033 rows=17 loops=1)

  • Merge Cond: ((CASE WHEN pref.outof_office_status THEN pref.alternate_approver_id ELSE pref.profile_id END) = (CASE WHEN pref_1.outof_office_status THEN pref_1.alternate_approver_id ELSE pref_1.profile_id END))
22. 0.046 6,005.195 ↑ 1.6 17 1

GroupAggregate (cost=80,622.94..80,623.43 rows=28 width=12) (actual time=6,005.162..6,005.195 rows=17 loops=1)

  • Group Key: (CASE WHEN pref.outof_office_status THEN pref.alternate_approver_id ELSE pref.profile_id END)
23. 0.043 6,005.149 ↓ 4.6 129 1

Sort (cost=80,622.94..80,623.01 rows=28 width=8) (actual time=6,005.141..6,005.149 rows=129 loops=1)

  • Sort Key: (CASE WHEN pref.outof_office_status THEN pref.alternate_approver_id ELSE pref.profile_id END)
  • Sort Method: quicksort Memory: 31kB
24. 0.086 6,005.106 ↓ 4.6 129 1

Hash Join (cost=80,619.65..80,622.27 rows=28 width=8) (actual time=6,005.073..6,005.106 rows=129 loops=1)

  • Hash Cond: (pref.profile_id = act.pending_for_profile_id)
25. 0.145 0.145 ↑ 1.0 97 1

Seq Scan on platformservice_profile_preferences pref (cost=0.00..1.97 rows=97 width=9) (actual time=0.137..0.145 rows=97 loops=1)

26. 0.096 6,004.875 ↓ 4.9 137 1

Hash (cost=80,619.30..80,619.30 rows=28 width=8) (actual time=6,004.875..6,004.875 rows=137 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
27. 223.179 6,004.779 ↓ 4.9 137 1

Unique (cost=80,618.81..80,619.02 rows=28 width=8) (actual time=5,405.150..6,004.779 rows=137 loops=1)

28. 1,826.037 5,781.600 ↓ 101,487.6 2,841,652 1

Sort (cost=80,618.81..80,618.88 rows=28 width=8) (actual time=5,405.148..5,781.600 rows=2,841,652 loops=1)

  • Sort Key: occ.cm_occurrence_id, act.pending_for_profile_id
  • Sort Method: external merge Disk: 50,064kB
29. 366.773 3,955.563 ↓ 101,487.6 2,841,652 1

Gather (cost=1,044.61..80,618.14 rows=28 width=8) (actual time=3.273..3,955.563 rows=2,841,652 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
30. 325.844 3,588.790 ↓ 88,801.6 1,420,826 2 / 2

Nested Loop (cost=44.61..79,615.34 rows=16 width=8) (actual time=2.164..3,588.790 rows=1,420,826 loops=2)

31. 22.152 163.921 ↓ 1.4 27,425 2 / 2

Hash Join (cost=44.18..3,669.43 rows=20,002 width=12) (actual time=1.261..163.921 rows=27,425 loops=2)

  • Hash Cond: (ins_ref.caller_group_id = occ.cm_occurrence_id)
32. 140.972 140.972 ↑ 1.2 31,065 2 / 2

Parallel Seq Scan on wf_instance_reference ins_ref (cost=0.00..3,163.83 rows=36,564 width=12) (actual time=0.353..140.972 rows=31,065 loops=2)

  • Filter: ((caller_type)::text = 'certification'::text)
  • Rows Removed by Filter: 612
33. 0.153 0.797 ↑ 1.0 930 2 / 2

Hash (cost=32.55..32.55 rows=930 width=4) (actual time=0.797..0.797 rows=930 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
34. 0.644 0.644 ↑ 1.0 930 2 / 2

Index Only Scan using idx_wf_cm_occurrence_status_id_cm_occurrence_id on cm_occurrence occ (cost=0.28..32.55 rows=930 width=4) (actual time=0.468..0.644 rows=930 loops=2)

  • Index Cond: (status_id = 2)
  • Heap Fetches: 0
35. 3,099.025 3,099.025 ↓ 52.0 52 54,850 / 2

Index Scan using idx_wf_instance_stage_action_wf_instance_stage_id on wf_instance_stage_action act (cost=0.43..3.79 rows=1 width=12) (actual time=0.031..0.113 rows=52 loops=54,850)

  • Index Cond: ((wf_instance_stage_id = ins_ref.current_instance_stage_id) AND (wf_instance_reference_id = ins_ref.wf_instance_reference_id))
36. 2.587 3,628.790 ↑ 1.5 17 1

GroupAggregate (cost=77,000.01..77,000.46 rows=26 width=12) (actual time=3,625.943..3,628.790 rows=17 loops=1)

  • Group Key: (CASE WHEN pref_1.outof_office_status THEN pref_1.alternate_approver_id ELSE pref_1.profile_id END)
37. 4.025 3,626.203 ↓ 578.0 15,029 1

Sort (cost=77,000.01..77,000.07 rows=26 width=8) (actual time=3,625.566..3,626.203 rows=15,029 loops=1)

  • Sort Key: (CASE WHEN pref_1.outof_office_status THEN pref_1.alternate_approver_id ELSE pref_1.profile_id END)
  • Sort Method: quicksort Memory: 1,089kB
38. 3.000 3,622.178 ↓ 578.0 15,029 1

Hash Join (cost=76,996.80..76,999.40 rows=26 width=8) (actual time=3,619.182..3,622.178 rows=15,029 loops=1)

  • Hash Cond: (pref_1.profile_id = act_1.pending_for_profile_id)
39. 0.043 0.043 ↑ 1.0 97 1

Seq Scan on platformservice_profile_preferences pref_1 (cost=0.00..1.97 rows=97 width=9) (actual time=0.026..0.043 rows=97 loops=1)

40. 3.176 3,619.135 ↓ 579.5 15,067 1

Hash (cost=76,996.48..76,996.48 rows=26 width=8) (actual time=3,619.135..3,619.135 rows=15,067 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 717kB
41. 206.768 3,615.959 ↓ 579.5 15,067 1

Unique (cost=76,996.02..76,996.22 rows=26 width=8) (actual time=3,106.180..3,615.959 rows=15,067 loops=1)

42. 1,698.322 3,409.191 ↓ 103,508.6 2,691,224 1

Sort (cost=76,996.02..76,996.09 rows=26 width=8) (actual time=3,106.179..3,409.191 rows=2,691,224 loops=1)

  • Sort Key: ins_ref_1.wf_instance_reference_id, act_1.pending_for_profile_id
  • Sort Method: external merge Disk: 47,424kB
43. 442.372 1,710.869 ↓ 103,508.6 2,691,224 1

Gather (cost=1,044.61..76,995.41 rows=26 width=8) (actual time=1.739..1,710.869 rows=2,691,224 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
44. 279.005 1,268.497 ↓ 89,707.5 1,345,612 2 / 2

Nested Loop (cost=44.61..75,992.81 rows=15 width=8) (actual time=1.833..1,268.497 rows=1,345,612 loops=2)

45. 16.938 70.567 ↓ 1.4 26,255 2 / 2

Hash Join (cost=44.18..3,727.41 rows=18,476 width=8) (actual time=1.589..70.567 rows=26,255 loops=2)

  • Hash Cond: (ins_ref_1.caller_group_id = occ_1.cm_occurrence_id)
46. 52.909 52.909 ↑ 1.2 29,211 2 / 2

Parallel Seq Scan on wf_instance_reference ins_ref_1 (cost=0.00..3,257.00 rows=33,775 width=12) (actual time=0.180..52.909 rows=29,211 loops=2)

  • Filter: (is_active AND ((caller_type)::text = 'certification'::text) AND ((status)::text = 'pending_approval'::text))
  • Rows Removed by Filter: 2,466
47. 0.195 0.720 ↑ 1.0 930 2 / 2

Hash (cost=32.55..32.55 rows=930 width=4) (actual time=0.720..0.720 rows=930 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
48. 0.525 0.525 ↑ 1.0 930 2 / 2

Index Only Scan using idx_wf_cm_occurrence_status_id_cm_occurrence_id on cm_occurrence occ_1 (cost=0.28..32.55 rows=930 width=4) (actual time=0.348..0.525 rows=930 loops=2)

  • Index Cond: (status_id = 2)
  • Heap Fetches: 0
49. 918.925 918.925 ↓ 51.0 51 52,510 / 2

Index Scan using idx_wf_instance_stage_action_wf_instance_stage_id on wf_instance_stage_action act_1 (cost=0.43..3.90 rows=1 width=12) (actual time=0.007..0.035 rows=51 loops=52,510)

  • Index Cond: ((wf_instance_stage_id = ins_ref_1.current_instance_stage_id) AND (wf_instance_reference_id = ins_ref_1.wf_instance_reference_id))