explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qgBJ : Optimization for: plan #lOV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 76.478 1,780,101.604 ↓ 20.0 20 1

Hash Join (cost=10,000,242,769.56..10,000,364,088.02 rows=1 width=411) (actual time=2,617.254..1,780,101.604 rows=20 loops=1)

  • Hash Cond: (costr.secondment_costreport_id = ass1.ref_id)
2. 1,117,373.325 1,779,971.551 ↓ 1.4 145,474 1

Nested Loop Left Join (cost=10,000,242,292.40..10,000,362,204.49 rows=102,281 width=908) (actual time=2,218.715..1,779,971.551 rows=145,474 loops=1)

  • Join Filter: (sap_outer.ref_id = costr.secondment_costreport_id)
  • Rows Removed by Join Filter: 21178105074
3.          

CTE sap_sponsors

4. 13.962 19.978 ↑ 1.0 10,694 1

GroupAggregate (cost=10,000,000,912.28..10,000,001,126.93 rows=10,694 width=40) (actual time=3.527..19.978 rows=10,694 loops=1)

  • Group Key: sponsor.secondment_costreport_id
5. 4.544 6.016 ↑ 1.0 10,797 1

Sort (cost=10,000,000,912.28..10,000,000,939.27 rows=10,797 width=13) (actual time=3.510..6.016 rows=10,797 loops=1)

  • Sort Key: sponsor.secondment_costreport_id
  • Sort Method: quicksort Memory: 891kB
6. 1.472 1.472 ↑ 1.0 10,797 1

Seq Scan on secondment_costreport_sponsor sponsor (cost=10,000,000,000.00..10,000,000,188.97 rows=10,797 width=13) (actual time=0.014..1.472 rows=10,797 loops=1)

7. 202.239 1,859.860 ↓ 1.4 145,473 1

Hash Right Join (cost=168,428.76..168,756.19 rows=102,281 width=286) (actual time=1,636.740..1,859.860 rows=145,473 loops=1)

  • Hash Cond: (sap_sponsors.secondment_costreport_id = costr.secondment_costreport_id)
8. 25.522 25.522 ↑ 1.0 10,694 1

CTE Scan on sap_sponsors (cost=0.00..213.88 rows=10,694 width=40) (actual time=3.533..25.522 rows=10,694 loops=1)

9. 117.396 1,632.099 ↓ 1.4 145,473 1

Hash (cost=167,150.25..167,150.25 rows=102,281 width=254) (actual time=1,632.099..1,632.099 rows=145,473 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 2 (originally 1) Memory Usage: 31745kB
10. 59.174 1,514.703 ↓ 1.4 145,473 1

Hash Join (cost=125,562.78..167,150.25 rows=102,281 width=254) (actual time=1,010.901..1,514.703 rows=145,473 loops=1)

  • Hash Cond: (costr.person_id = person.person_id)
11. 56.878 1,191.652 ↑ 1.2 145,473 1

Hash Left Join (cost=93,043.53..132,962.73 rows=172,122 width=246) (actual time=746.726..1,191.652 rows=145,473 loops=1)

  • Hash Cond: (req.secondment_application_id = v_secondment_destination.secondment_application_id)
12. 34.834 563.274 ↓ 1.0 145,473 1

Hash Left Join (cost=31,049.62..70,587.74 rows=145,165 width=222) (actual time=174.272..563.274 rows=145,473 loops=1)

  • Hash Cond: (app.document_type_id = doc.document_type_id)
13. 63.748 528.401 ↓ 1.0 145,473 1

Hash Join (cost=31,036.46..70,149.88 rows=145,165 width=192) (actual time=174.208..528.401 rows=145,473 loops=1)

  • Hash Cond: (req.secondment_application_id = app.secondment_application_id)
14. 44.652 291.225 ↓ 1.0 145,473 1

Merge Join (cost=1.57..38,733.92 rows=145,165 width=122) (actual time=0.038..291.225 rows=145,473 loops=1)

  • Merge Cond: (costr.secondment_request_id = req.secondment_request_id)
15. 135.186 135.186 ↓ 1.0 145,473 1

Index Scan using idx_sec_cost_sec_req on secondment_costreport costr (cost=0.42..9,419.80 rows=145,165 width=122) (actual time=0.024..135.186 rows=145,473 loops=1)

  • Filter: ((status)::text = ANY ('{SUBMITTED,AGREED,APPROVED}'::text[]))
  • Rows Removed by Filter: 3446
16. 111.387 111.387 ↑ 1.0 189,560 1

Index Scan using pk_secondment_request on secondment_request req (cost=0.42..27,028.09 rows=189,573 width=16) (actual time=0.011..111.387 rows=189,560 loops=1)

17. 47.235 173.428 ↑ 1.0 169,557 1

Hash (cost=28,915.43..28,915.43 rows=169,557 width=78) (actual time=173.428..173.428 rows=169,557 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 21128kB
18. 126.193 126.193 ↑ 1.0 169,557 1

Index Scan using pk_secondment on secondment_application app (cost=0.42..28,915.43 rows=169,557 width=78) (actual time=0.014..126.193 rows=169,557 loops=1)

19. 0.010 0.039 ↑ 1.0 37 1

Hash (cost=12.70..12.70 rows=37 width=34) (actual time=0.039..0.039 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.029 0.029 ↑ 1.0 37 1

Index Scan using pk_document_type on document_type doc (cost=0.14..12.70 rows=37 width=34) (actual time=0.021..0.029 rows=37 loops=1)

21. 33.714 571.500 ↑ 1.0 169,355 1

Hash (cost=59,874.46..59,874.46 rows=169,557 width=40) (actual time=571.500..571.500 rows=169,355 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13123kB
22. 14.075 537.786 ↑ 1.0 169,355 1

Subquery Scan on v_secondment_destination (cost=21,160.86..59,874.46 rows=169,557 width=40) (actual time=161.322..537.786 rows=169,355 loops=1)

23. 238.220 523.711 ↑ 1.0 169,355 1

GroupAggregate (cost=21,160.86..58,178.89 rows=169,557 width=40) (actual time=161.321..523.711 rows=169,355 loops=1)

  • Group Key: app_1.secondment_application_id
24. 52.707 285.491 ↑ 1.0 171,153 1

Merge Join (cost=21,160.86..53,064.25 rows=171,153 width=31) (actual time=161.296..285.491 rows=171,153 loops=1)

  • Merge Cond: (dest.secondment_application_id = app_1.secondment_application_id)
25. 48.236 171.974 ↑ 1.0 171,153 1

Sort (cost=21,160.40..21,588.28 rows=171,153 width=31) (actual time=161.247..171.974 rows=171,153 loops=1)

  • Sort Key: dest.secondment_application_id
  • Sort Method: quicksort Memory: 19847kB
26. 38.041 123.738 ↑ 1.0 171,153 1

Hash Join (cost=20.47..6,282.98 rows=171,153 width=31) (actual time=10.491..123.738 rows=171,153 loops=1)

  • Hash Cond: ((dest.country_iso_code)::text = (coun.iso_code)::text)
27. 85.596 85.596 ↑ 1.0 171,153 1

Index Scan using idx_sec_dest_sec_appl on secondment_destination dest (cost=0.42..5,805.92 rows=171,153 width=21) (actual time=10.348..85.596 rows=171,153 loops=1)

28. 0.041 0.101 ↑ 1.0 246 1

Hash (cost=16.97..16.97 rows=246 width=13) (actual time=0.101..0.101 rows=246 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
29. 0.060 0.060 ↑ 1.0 246 1

Index Scan using pk_country on country coun (cost=0.14..16.97 rows=246 width=13) (actual time=0.014..0.060 rows=246 loops=1)

30. 60.810 60.810 ↑ 1.0 169,557 1

Index Only Scan using pk_secondment on secondment_application app_1 (cost=0.42..28,915.43 rows=169,557 width=8) (actual time=0.044..60.810 rows=169,557 loops=1)

  • Heap Fetches: 169557
31. 10.363 263.877 ↓ 1.7 53,547 1

Hash (cost=32,121.50..32,121.50 rows=31,820 width=12) (actual time=263.877..263.877 rows=53,547 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2813kB
32. 8.957 253.514 ↓ 1.7 53,547 1

Gather (cost=9,251.87..32,121.50 rows=31,820 width=12) (actual time=116.522..253.514 rows=53,547 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 38.283 244.557 ↓ 1.3 17,849 3

Nested Loop (cost=8,251.87..27,939.50 rows=13,258 width=12) (actual time=111.782..244.557 rows=17,849 loops=3)

34. 72.407 206.272 ↓ 1.3 17,849 3

Nested Loop (cost=8,251.45..20,983.27 rows=13,258 width=12) (actual time=111.757..206.272 rows=17,849 loops=3)

35. 11.581 133.861 ↓ 1.3 17,849 3

Hash Join (cost=8,251.03..13,237.79 rows=13,258 width=8) (actual time=111.723..133.861 rows=17,849 loops=3)

  • Hash Cond: (typeperson.org_unit_id = person.org_unit_id)
36. 8.212 72.206 ↑ 1.3 43,150 3

Parallel Bitmap Heap Scan on org_unit typeperson (cost=2,680.52..7,525.01 rows=54,199 width=8) (actual time=61.266..72.206 rows=43,150 loops=3)

  • Recheck Cond: ((obj_type)::text = 'PERSON'::text)
  • Heap Blocks: exact=1130
37. 63.994 63.994 ↑ 1.0 129,451 1

Bitmap Index Scan on idx_org_unit_obj_type (cost=0.00..2,648.01 rows=130,078 width=0) (actual time=63.994..63.994 rows=129,451 loops=1)

  • Index Cond: ((obj_type)::text = 'PERSON'::text)
38. 8.108 50.074 ↑ 1.0 53,547 3

Hash (cost=4,901.16..4,901.16 rows=53,548 width=8) (actual time=50.074..50.074 rows=53,547 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 2604kB
39. 41.966 41.966 ↑ 1.0 53,548 3

Index Scan using pk_person on org_person person (cost=0.29..4,901.16 rows=53,548 width=8) (actual time=18.447..41.966 rows=53,548 loops=3)

40. 0.004 0.004 ↑ 1.0 1 53,547

Index Scan using pk_org_unit on org_unit typeposition (cost=0.42..0.58 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=53,547)

  • Index Cond: (org_unit_id = typeperson.parent_org_unit_id)
41. 0.002 0.002 ↑ 1.0 1 53,547

Index Only Scan using pk_org_unit on org_unit typeorgstructure (cost=0.42..0.52 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=53,547)

  • Index Cond: (org_unit_id = typeposition.parent_org_unit_id)
  • Heap Fetches: 14418
42. 659,838.661 660,738.366 ↓ 145,582.0 145,582 145,473

Materialize (cost=72,736.71..188,741.54 rows=1 width=16) (actual time=0.004..4.542 rows=145,582 loops=145,473)

43. 12.415 899.705 ↓ 145,582.0 145,582 1

Gather (cost=72,736.71..188,741.53 rows=1 width=16) (actual time=581.806..899.705 rows=145,582 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
44. 134.488 887.290 ↓ 48,527.0 48,527 3

Hash Join (cost=71,736.71..187,741.43 rows=1 width=16) (actual time=570.388..887.290 rows=48,527 loops=3)

  • Hash Cond: ((sap_outer.ref_id = sap_inner.ref_id) AND (sap_outer.status_mod_dt = sap_inner.status_mod_dt))
45. 182.866 182.866 ↑ 1.3 425,515 3

Parallel Index Scan using idx_sap_transactions on sap_transaction sap_outer (cost=0.43..113,212.71 rows=531,894 width=24) (actual time=0.017..182.866 rows=425,515 loops=3)

46. 25.720 569.936 ↓ 1.1 145,581 3

Hash (cost=69,790.18..69,790.18 rows=129,740 width=16) (actual time=569.936..569.936 rows=145,581 loops=3)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 8873kB
47. 11.158 544.216 ↓ 1.1 145,581 3

Subquery Scan on sap_inner (cost=67,195.38..69,790.18 rows=129,740 width=16) (actual time=503.328..544.216 rows=145,581 loops=3)

48. 84.224 533.058 ↓ 1.1 145,581 3

HashAggregate (cost=67,195.38..68,492.78 rows=129,740 width=16) (actual time=503.327..533.058 rows=145,581 loops=3)

  • Group Key: sapt.ref_id
49. 68.767 448.834 ↑ 1.0 149,885 3

Bitmap Heap Scan on sap_transaction sapt (cost=47,276.44..66,437.11 rows=151,654 width=16) (actual time=382.522..448.834 rows=149,885 loops=3)

  • Recheck Cond: ((ref_type)::text = 'SECONDMENT_COST_REPORT'::text)
  • Heap Blocks: exact=15369
50. 380.067 380.067 ↑ 1.0 149,885 3

Bitmap Index Scan on idx_sap_transactions (cost=0.00..47,238.52 rows=151,654 width=0) (actual time=380.067..380.067 rows=149,885 loops=3)

  • Index Cond: ((ref_type)::text = 'SECONDMENT_COST_REPORT'::text)
51. 0.007 53.575 ↓ 21.0 21 1

Hash (cost=477.14..477.14 rows=1 width=55) (actual time=53.575..53.575 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
52. 0.013 53.568 ↓ 21.0 21 1

Merge Join (cost=476.17..477.14 rows=1 width=55) (actual time=53.558..53.568 rows=21 loops=1)

  • Merge Cond: ((ass1.ref_id = assignment.ref_id) AND ((ass1.ref_type)::text = (assignment.ref_type)::text) AND (ass1.status_mod_dt = (max(assignment.status_mod_dt))))
53. 0.052 53.494 ↑ 1.6 21 1

Sort (cost=228.76..228.85 rows=34 width=70) (actual time=53.493..53.494 rows=21 loops=1)

  • Sort Key: ass1.ref_id, ass1.ref_type, ass1.status_mod_dt
  • Sort Method: quicksort Memory: 27kB
54. 53.442 53.442 ↑ 1.6 21 1

Index Scan using idx_ass_assignee_status_types on assignment ass1 (cost=0.56..227.90 rows=34 width=70) (actual time=36.797..53.442 rows=21 loops=1)

  • Index Cond: ((assignee = 336) AND ((status)::text = 'COMPLETED'::text) AND ((type)::text = ANY ('{COORDINATION_REQUEST,APPROVAL_REQUEST,CONTROL_REQUEST,REVIEW_REQUEST}'::text[])) AND ((type)::text = 'APPROVAL_REQUEST'::text))
  • Filter: ((assignee_org_unit_id IS NULL) AND ((resolution)::text = 'ACCEPTED'::text))
55. 0.015 0.061 ↑ 3.0 21 1

Sort (cost=247.41..247.57 rows=62 width=62) (actual time=0.060..0.061 rows=21 loops=1)

  • Sort Key: assignment.ref_id, assignment.ref_type, (max(assignment.status_mod_dt))
  • Sort Method: quicksort Memory: 27kB
56. 0.009 0.046 ↑ 3.0 21 1

GroupAggregate (cost=243.40..244.95 rows=62 width=62) (actual time=0.039..0.046 rows=21 loops=1)

  • Group Key: assignment.ref_type, assignment.ref_id, assignment.assignee, assignment.type
57. 0.019 0.037 ↑ 3.0 21 1

Sort (cost=243.40..243.55 rows=62 width=62) (actual time=0.036..0.037 rows=21 loops=1)

  • Sort Key: assignment.ref_type, assignment.ref_id
  • Sort Method: quicksort Memory: 27kB
58. 0.018 0.018 ↑ 3.0 21 1

Index Scan using idx_ass_assignee_status_types on assignment (cost=0.56..241.55 rows=62 width=62) (actual time=0.010..0.018 rows=21 loops=1)

  • Index Cond: ((assignee = 336) AND ((type)::text = 'APPROVAL_REQUEST'::text))