explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y5nO

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Subquery Scan all_claims (cost=2,662,856,920.04..2,673,133,093.47 rows=29,466 width=5,760) (actual rows= loops=)

  • Filter: rn::numeric = 1::numeric
  • Rows out: 3185211 rows with 782940 ms to first row, 2182303 ms to end, start offset by 41936 ms.
2. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=2,662,856,920.04..2,673,133,093.47 rows=29,465,731 width=5,168) (actual rows= loops=)

  • Rows out: 44646595 rows with 782940 ms to first row, 2161768 ms to end, start offset by 41936 ms.
3. 0.000 0.000 ↓ 0.0

Window (cost=2,662,856,920.04..2,663,593,563.29 rows=29,465,731 width=5,168) (actual rows= loops=)

  • Order By: coplan.unnamed_attr_97
  • Rows out: 44646595 rows with 782940 ms to first row, 1021126 ms to end, start offset by 41936 ms.
4. 0.000 0.000 ↓ 0.0

Gather Motion 108:1 (slice36; segments: 108) (cost=2,662,856,920.04..2,663,519,898.97 rows=29,465,731 width=5,168) (actual rows= loops=)

  • Merge Key: coplan.unnamed_attr_97
  • Rows out: 44646595 rows at destination with 189701 ms to first row, 682443 ms to end, start offset by 41936 ms.
5. 0.000 0.000 ↓ 0.0

Sort (cost=2,662,856,920.04..2,662,930,584.36 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.unnamed_attr_97
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 184965 ms to first row, 185391 ms to end, start offset by 42045 ms.
  • Executor memory: 436217K bytes avg, 438265K bytes max (seg0).
  • Work_mem used: 436217K bytes avg, 438265K bytes max (seg0). Workfile: (0 spilling)
6. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=2,418,244,274.99..2,418,759,925.27 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 174919 ms to first row, 180468 ms to end, start offset by 42046 ms.
7. 0.000 0.000 ↓ 0.0

Window (cost=2,418,244,274.99..2,418,465,267.96 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: coplan.claim_number
  • Order By: coplan.transaction_created_date
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 174919 ms to first row, 180321 ms to end, start offset by 42046 ms.
8. 0.000 0.000 ↓ 0.0

Sort (cost=2,418,244,274.99..2,418,317,939.31 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.claim_number, coplan.transaction_created_date
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 174919 ms to first row, 175281 ms to end, start offset by 42046 ms.
  • Executor memory: 468378K bytes avg, 471033K bytes max (seg0).
  • Work_mem used: 468378K bytes avg, 471033K bytes max (seg0). Workfile: (0 spilling)
9. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=2,173,557,965.61..2,174,147,280.21 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 165691 ms to first row, 170427 ms to end, start offset by 42046 ms.
10. 0.000 0.000 ↓ 0.0

Window (cost=2,173,557,965.61..2,173,852,622.91 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: coplan.claim_number
  • Order By: coplan.unnamed_attr_100, coplan.unnamed_attr_101
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 165691 ms to first row, 170289 ms to end, start offset by 42046 ms.
11. 0.000 0.000 ↓ 0.0

Sort (cost=2,173,557,965.61..2,173,631,629.93 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.claim_number, coplan.unnamed_attr_100, coplan.unnamed_attr_101
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 165690 ms to first row, 166009 ms to end, start offset by 42046 ms.
  • Executor memory: 468378K bytes avg, 471033K bytes max (seg0).
  • Work_mem used: 468378K bytes avg, 471033K bytes max (seg0). Workfile: (0 spilling)
12. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=1,928,871,656.23..1,929,460,970.84 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 156504 ms to first row, 161363 ms to end, start offset by 42046 ms.
13. 0.000 0.000 ↓ 0.0

Window (cost=1,928,871,656.23..1,929,166,313.53 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: coplan.claim_number
  • Order By: coplan.unnamed_attr_98, coplan.unnamed_attr_99
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 156504 ms to first row, 161291 ms to end, start offset by 42046 ms.
14. 0.000 0.000 ↓ 0.0

Sort (cost=1,928,871,656.23..1,928,945,320.56 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.claim_number, coplan.unnamed_attr_98, coplan.unnamed_attr_99
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 156503 ms to first row, 156763 ms to end, start offset by 42046 ms.
  • Executor memory: 468378K bytes avg, 471033K bytes max (seg0).
  • Work_mem used: 468378K bytes avg, 471033K bytes max (seg0). Workfile: (0 spilling)
15. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice35; segments: 108) (cost=1,683,596,032.25..1,684,774,661.46 rows=272,831 width=5,168) (actual rows= loops=)

  • Hash Key: coplan.claim_number
  • Rows out: Avg 413394.4 rows x 108 workers at destination. Max 419176 rows (seg107) with 125020 ms to first row, 147432 ms to end, start offset by 42046 ms.
16. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=1,683,596,032.25..1,684,185,346.85 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 133464 ms to first row, 139440 ms to end, start offset by 42221 ms.
17. 0.000 0.000 ↓ 0.0

Window (cost=1,683,596,032.25..1,683,890,689.55 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: coplan.claim_number, coplan.risk_code
  • Order By: coplan.unnamed_attr_106
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 133464 ms to first row, 139347 ms to end, start offset by 42221 ms.
18. 0.000 0.000 ↓ 0.0

Sort (cost=1,683,596,032.25..1,683,669,696.57 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.claim_number, coplan.risk_code, coplan.unnamed_attr_106
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 133464 ms to first row, 133764 ms to end, start offset by 42221 ms.
  • Executor memory: 468833K bytes avg, 479225K bytes max (seg12).
  • Work_mem used: 468833K bytes avg, 479225K bytes max (seg12). Workfile: (0 spilling)
19. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=1,438,909,722.87..1,439,499,037.47 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 125847 ms to first row, 129692 ms to end, start offset by 42222 ms.
20. 0.000 0.000 ↓ 0.0

Window (cost=1,438,909,722.87..1,439,204,380.17 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: coplan.claim_number, coplan.risk_code
  • Order By: coplan.transaction_created_date
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 125847 ms to first row, 129600 ms to end, start offset by 42222 ms.
21. 0.000 0.000 ↓ 0.0

Sort (cost=1,438,909,722.87..1,438,983,387.19 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.claim_number, coplan.risk_code, coplan.transaction_created_date
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 125847 ms to first row, 126207 ms to end, start offset by 42222 ms.
  • Executor memory: 468833K bytes avg, 479225K bytes max (seg12).
  • Work_mem used: 468833K bytes avg, 479225K bytes max (seg12). Workfile: (0 spilling)
22. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=1,194,149,749.17..1,194,812,728.10 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 117115 ms to first row, 121608 ms to end, start offset by 42222 ms.
23. 0.000 0.000 ↓ 0.0

Window (cost=1,194,149,749.17..1,194,518,070.79 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: coplan.claim_number, coplan.risk_code
  • Order By: coplan.unnamed_attr_105, coplan.claimant_date_of_birth
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 117115 ms to first row, 121529 ms to end, start offset by 42222 ms.
24. 0.000 0.000 ↓ 0.0

Sort (cost=1,194,149,749.17..1,194,223,413.49 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.claim_number, coplan.risk_code, coplan.unnamed_attr_105, coplan.claimant_date_of_birth
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 117115 ms to first row, 117238 ms to end, start offset by 42222 ms.
  • Executor memory: 468757K bytes avg, 479225K bytes max (seg65).
  • Work_mem used: 468757K bytes avg, 479225K bytes max (seg65). Workfile: (0 spilling)
25. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=949,389,775.46..950,052,754.39 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 108395 ms to first row, 112895 ms to end, start offset by 42223 ms.
26. 0.000 0.000 ↓ 0.0

Window (cost=949,389,775.46..949,758,097.09 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: coplan.claim_number, coplan.risk_code
  • Order By: coplan.unnamed_attr_104, coplan.driver_date_of_birth
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 108395 ms to first row, 112807 ms to end, start offset by 42223 ms.
27. 0.000 0.000 ↓ 0.0

Sort (cost=949,389,775.46..949,463,439.79 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.claim_number, coplan.risk_code, coplan.unnamed_attr_104, coplan.driver_date_of_birth
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 108394 ms to first row, 108807 ms to end, start offset by 42223 ms.
  • Executor memory: 468757K bytes avg, 479225K bytes max (seg65).
  • Work_mem used: 468757K bytes avg, 479225K bytes max (seg65). Workfile: (0 spilling)
28. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=704,703,466.08..705,292,780.69 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 99087 ms to first row, 103381 ms to end, start offset by 42223 ms.
29. 0.000 0.000 ↓ 0.0

Window (cost=704,703,466.08..704,998,123.39 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: coplan.claim_number, coplan.risk_code
  • Order By: coplan.unnamed_attr_103
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 99087 ms to first row, 102708 ms to end, start offset by 42223 ms.
30. 0.000 0.000 ↓ 0.0

Sort (cost=704,703,466.08..704,777,130.41 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: coplan.claim_number, coplan.risk_code, coplan.unnamed_attr_103
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 99087 ms to first row, 99342 ms to end, start offset by 42223 ms.
  • Executor memory: 468757K bytes avg, 479225K bytes max (seg65).
  • Work_mem used: 468757K bytes avg, 479225K bytes max (seg65). Workfile: (0 spilling)
31. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=459,280,513.45..460,606,471.31 rows=272,831 width=5,168) (actual rows= loops=)

  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 88517 ms to first row, 94124 ms to end, start offset by 42223 ms.
32. 0.000 0.000 ↓ 0.0

Window (cost=459,280,513.45..460,311,814.01 rows=272,831 width=5,168) (actual rows= loops=)

  • Partition By: clm_trn_det.claim_number, exp_risk.risk_code
  • Order By: "?column102?
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 88517 ms to first row, 93993 ms to end, start offset by 42223 ms.
33. 0.000 0.000 ↓ 0.0

Sort (cost=459,280,513.45..459,354,177.78 rows=272,831 width=5,168) (actual rows= loops=)

  • Sort Key: clm_trn_det.claim_number, exp_risk.risk_code, "?column102?
  • Rows out: Avg 413394.4 rows x 108 workers. Max 421158 rows (seg90) with 88517 ms to first row, 89862 ms to end, start offset by 42223 ms.
  • Executor memory: 300192K bytes avg, 300192K bytes max (seg0).
  • Work_mem used: 300192K bytes avg, 300192K bytes max (seg0). Workfile: (108 spilling)
  • Work_mem wanted: 460212K bytes avg, 468855K bytes max (seg90) to lessen workfile I/O affecting 108 workers.
34. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice34; segments: 108) (cost=192,935,545.97..215,183,518.68 rows=272,831 width=5,168) (actual rows= loops=)

  • Hash Key: clm_trn_det.claim_number, exp_risk.risk_code
  • Rows out: Avg 413394.4 rows x 108 workers at destination. Max 421158 rows (seg90) with 56352 ms to first row, 77380 ms to end, start offset by 42223 ms.
35. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=192,935,545.97..214,594,204.07 rows=272,831 width=5,168) (actual rows= loops=)

  • Hash Cond: clm_trn_det.claim_number::text = claim_details.claim_number AND clm_trn_det.claim_exposure_id = claim_details.claim_exposure_id
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 59253 ms to first row, 76816 ms to end, start offset by 42295 ms.
  • Executor memory: 3380K bytes avg, 3412K bytes max (seg86).
  • Work_mem used: 3380K bytes avg, 3412K bytes max (seg86). Workfile: (0 spilling)
  • (seg107) Hash chain length 1.2 avg, 5 max, using 65759 of 262151 buckets.
36. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=192,603,480.50..213,083,315.74 rows=272,831 width=5,200) (actual rows= loops=)

  • Hash Cond: clm_trn_det.claim_type_conformed::text = "*VALUES*".column1
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 58809 ms to first row, 73875 ms to end, start offset by 42736 ms.
  • Executor memory: 4K bytes avg, 4K bytes max (seg0).
  • Work_mem used: 4K bytes avg, 4K bytes max (seg0). Workfile: (0 spilling)
  • (seg107) Hash chain length 1.0 avg, 1 max, using 68 of 65539 buckets.
37. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=192,603,478.80..212,709,983.24 rows=272,831 width=5,200) (actual rows= loops=)

  • Hash Cond: clm_trn_det.claim_number::text = motor.claim_number::text AND clm_trn_det.source_system = motor.datasource::text
  • Rows out: Avg 413394.4 rows x 108 workers. Max 419176 rows (seg107) with 58804 ms to first row, 72897 ms to end, start offset by 42740 ms.
  • Executor memory: 1968K bytes avg, 2012K bytes max (seg4).
  • Work_mem used: 1968K bytes avg, 2012K bytes max (seg4). Workfile: (0 spilling)
  • (seg107) Hash chain length 1.1 avg, 4 max, using 9478 of 65539 buckets.
38. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=192,471,803.90..212,136,169.45 rows=272,831 width=5,074) (actual rows= loops=)

  • Hash Cond: clm_trn_det.claim_number::text = aggregates_by_risk.claim_number::text AND exp_risk.risk_code = aggregates_by_risk.risk_code
  • Rows out: Avg 413391.1 rows x 108 workers. Max 419176 rows (seg107) with 57837 ms to first row, 70955 ms to end, start offset by 43706 ms.
  • Executor memory: 6238K bytes avg, 6330K bytes max (seg7).
  • Work_mem used: 6238K bytes avg, 6330K bytes max (seg7). Workfile: (0 spilling)
  • (seg107) Hash chain length 1.2 avg, 6 max, using 23978 of 65539 buckets.
39. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=112,705,508.45..131,780,559.40 rows=272,831 width=4,466) (actual rows= loops=)

  • Hash Cond: clm_trn_det.claim_number::text = motor_exp.claim_number AND clm_trn_det.source_system = motor_exp.source_system_code
  • Rows out: Avg 413391.1 rows x 108 workers. Max 419176 rows (seg107) with 10731 ms to first row, 22262 ms to end, start offset by 90811 ms.
  • Executor memory: 2716K bytes avg, 2751K bytes max (seg67).
  • Work_mem used: 2716K bytes avg, 2751K bytes max (seg67). Workfile: (0 spilling)
  • (seg107) Hash chain length 1.1 avg, 6 max, using 29957 of 131111 buckets.
40. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=112,391,655.93..131,024,562.41 rows=272,831 width=4,439) (actual rows= loops=)

  • Hash Cond: clm_trn_det.claim_number::text = est_type.claim_number::text
  • Rows out: Avg 413387.3 rows x 108 workers. Max 419176 rows (seg107) with 10459 ms to first row, 21030 ms to end, start offset by 91083 ms.
  • Executor memory: 1399K bytes avg, 1419K bytes max (seg39).
  • Work_mem used: 1399K bytes avg, 1419K bytes max (seg39). Workfile: (0 spilling)
  • (seg107) Hash chain length 1.5 avg, 7 max, using 19363 of 32779 buckets.
41. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=59,406,095.44..77,597,015.96 rows=272,831 width=4,343) (actual rows= loops=)

  • Hash Cond: clm_trn_det.claim_number::text = exp_risk.claim_number::text AND clm_trn_det.source_system = exp_risk.source_system
  • Join Filter: clm_trn_det.claim_exposure_id = exp_risk.claim_exposure_id OR (clm_trn_det.claim_exposure_id IS NULL AND exp_risk.risk_default_exposure_flag = 'Y'::text)
  • Rows out: Avg 413387.3 rows x 108 workers. Max 419176 rows (seg107) with 239 ms to first row, 9927 ms to end, start offset by 101302 ms.
  • Executor memory: 5138K bytes avg, 5210K bytes max (seg39).
  • Work_mem used: 5138K bytes avg, 5210K bytes max (seg39). Workfile: (0 spilling)
  • (seg107) Hash chain length 1.7 avg, 11 max, using 23709 of 65539 buckets.
42. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..16,433,018.92 rows=272,831 width=380) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_CI,CC_PI}'::text[])) AND (transaction_source = ANY ('{EXPOSURE,PAYMENT,RESERVE,RECOVERY,RECOVERABLE,CLAIM_HISTORY}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIO,SGIC}'::text[])) AND line_of_business_code::text <> 'CTP'::text
  • Rows out: Avg 412991.4 rows x 108 workers. Max 418781 rows (seg107) with 174 ms to first row, 8084 ms to end, start offset by 101367 ms.
43. 0.000 0.000 ↓ 0.0

Hash (cost=59,339,106.65..59,339,106.65 rows=41,352 width=424) (actual rows= loops=)

  • Rows in: Avg 38689.8 rows x 108 workers. Max 39233 rows (seg39) with 56 ms to end, start offset by 100021 ms.
44. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice15; segments: 108) (cost=59,048,821.92..59,339,106.65 rows=41,352 width=424) (actual rows= loops=)

  • Hash Key: exp_risk.claim_number::text
  • Rows out: Avg 38689.8 rows x 108 workers at destination. Max 39233 rows (seg39) with 0.196 ms to first row, 29 ms to end, start offset by 100021 ms.
45. 0.000 0.000 ↓ 0.0

Subquery Scan exp_risk (cost=59,048,821.92..59,249,788.27 rows=41,352 width=424) (actual rows= loops=)

  • Rows out: Avg 38689.8 rows x 108 workers. Max 38974 rows (seg48) with 26546 ms to first row, 26835 ms to end, start offset by 43088 ms.
46. 0.000 0.000 ↓ 0.0

Window (cost=59,048,821.92..59,205,129.08 rows=41,352 width=602) (actual rows= loops=)

  • Partition By: t.source_system, t.claim_id, t.claim_exposure_id
  • Order By: t.risk_proportion, t.risk_code
  • Rows out: Avg 38689.8 rows x 108 workers. Max 38974 rows (seg48) with 26546 ms to first row, 26824 ms to end, start offset by 43088 ms.
47. 0.000 0.000 ↓ 0.0

Sort (cost=59,048,821.92..59,059,986.72 rows=41,352 width=602) (actual rows= loops=)

  • Sort Key: t.source_system, t.claim_id, t.claim_exposure_id, t.risk_proportion, t.risk_code
  • Rows out: Avg 38689.8 rows x 108 workers. Max 38974 rows (seg48) with 26545 ms to first row, 26557 ms to end, start offset by 43088 ms.
  • Executor memory: 17913K bytes avg, 17913K bytes max (seg0).
  • Work_mem used: 17913K bytes avg, 17913K bytes max (seg0). Workfile: (0 spilling)
48. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice14; segments: 108) (cost=37,990,677.70..58,555,549.43 rows=41,352 width=602) (actual rows= loops=)

  • Hash Key: t.source_system, t.claim_id, t.claim_exposure_id
  • Rows out: Avg 38689.8 rows x 108 workers at destination. Max 38974 rows (seg48) with 11779 ms to first row, 26417 ms to end, start offset by 43088 ms.
49. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=37,990,677.70..58,466,231.05 rows=41,352 width=602) (actual rows= loops=)

  • Hash Cond: t.source_system = e.source_system AND t.claim_number::text = e.claim_number::text AND t.claim_exposure_id = e.claim_exposure_id
  • Rows out: Avg 38689.8 rows x 108 workers. Max 39233 rows (seg39) with 12392 ms to first row, 26852 ms to end, start offset by 42392 ms.
  • Executor memory: 2115K bytes avg, 2145K bytes max (seg39).
  • Work_mem used: 2115K bytes avg, 2145K bytes max (seg39). Workfile: (0 spilling)
  • (seg39) Hash chain length 1.7 avg, 7 max, using 22826 of 32779 buckets.
50. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice13; segments: 108) (cost=20,919,049.05..41,316,368.06 rows=41,352 width=570) (actual rows= loops=)

  • Hash Key: t.claim_number::text
  • Rows out: Avg 38689.8 rows x 108 workers at destination. Max 39233 rows (seg39) with 3545 ms to first row, 17915 ms to end, start offset by 51238 ms.
51. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=20,919,049.05..41,227,049.68 rows=41,352 width=570) (actual rows= loops=)

  • Hash Cond: t.source_system = si.source_system AND t.claim_id = si.claim_id AND t.claim_exposure_id::bigint = si.claim_exposure_id
  • Rows out: Avg 38689.8 rows x 108 workers. Max 38948 rows (seg100) with 12385 ms to first row, 27028 ms to end, start offset by 42199 ms.
  • Executor memory: 4200K bytes avg, 4223K bytes max (seg100).
  • Work_mem used: 4200K bytes avg, 4223K bytes max (seg100). Workfile: (0 spilling)
  • (seg100) Hash chain length 1.7 avg, 9 max, using 34155 of 65539 buckets.
52. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice8; segments: 108) (cost=18,865,756.35..39,095,326.48 rows=41,352 width=442) (actual rows= loops=)

  • Hash Key: t.claim_exposure_id::bigint, t.claim_id, t.source_system
  • Rows out: Avg 38689.8 rows x 108 workers at destination. Max 38948 rows (seg100) with 6977 ms to first row, 21495 ms to end, start offset by 47606 ms.
53. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,865,756.35..39,006,008.10 rows=41,352 width=344) (actual rows= loops=)

  • Hash Cond: tr.instype2_code = rcm.instype2_code
  • Rows out: Avg 38689.8 rows x 108 workers. Max 39113 rows (seg41) with 12731 ms to first row, 22253 ms to end, start offset by 42331 ms.
  • Executor memory: 1K bytes avg, 1K bytes max (seg0).
  • Work_mem used: 1K bytes avg, 1K bytes max (seg0). Workfile: (0 spilling)
  • (seg41) Hash chain length 1.0 avg, 1 max, using 16 of 65539 buckets.
54. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,865,717.11..38,813,621.73 rows=41,352 width=1,038) (actual rows= loops=)

  • Hash Cond: trc.source_system = ca.source_system AND trc.claim_id = ca.claim_id AND trc.claim_exposure_id = ca.claim_exposure_id::text
  • Rows out: Avg 38689.8 rows x 108 workers. Max 39113 rows (seg41) with 12511 ms to first row, 21918 ms to end, start offset by 42550 ms.
  • Executor memory: 13896K bytes avg, 13896K bytes max (seg0).
  • Work_mem used: 13896K bytes avg, 13896K bytes max (seg0). Workfile: (0 spilling)
  • (seg41) Hash chain length 2.0 avg, 10 max, using 107852 of 131111 buckets.
55. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,024,901.14..37,536,279.91 rows=41,352 width=1,018) (actual rows= loops=)

  • Hash Cond: trc.source_system = ll.source_system AND trc.policy_id::bigint = ll.policy_id
  • Rows out: Avg 38672.5 rows x 108 workers. Max 39093 rows (seg41) with 10733 ms to first row, 19758 ms to end, start offset by 44328 ms.
  • Executor memory: 2232K bytes avg, 2232K bytes max (seg0).
  • Work_mem used: 2232K bytes avg, 2232K bytes max (seg0). Workfile: (0 spilling)
  • (seg41) Hash chain length 1.2 avg, 6 max, using 40348 of 131111 buckets.
56. 0.000 0.000 ↓ 0.0

Append (cost=17,451,364.29..36,742,072.84 rows=41,352 width=2,450) (actual rows= loops=)

  • Rows out: Avg 38672.5 rows x 108 workers. Max 39093 rows (seg41) with 10682 ms to first row, 19656 ms to end, start offset by 44379 ms.
57. 0.000 0.000 ↓ 0.0

Subquery Scan mv_claim_transaction_details_all (cost=17,451,364.29..17,713,422.17 rows=1 width=1,496) (actual rows= loops=)

  • Rows out: Avg 38591.9 rows x 108 workers. Max 39008 rows (seg41) with 10682 ms to first row, 10842 ms to end, start offset by 44379 ms.
58. 0.000 0.000 ↓ 0.0

Unique (cost=17,451,364.29..17,713,422.12 rows=0 width=139) (actual rows= loops=)

  • Group By: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.policy_brand, pub.mv_claim_transaction_details_all.claim_id, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.policy_id, pub.mv_claim_transaction_details_all.policy_number, pub.mv_claim_transaction_details_all.claim_loss_type_name, pub.mv_claim_transaction_details_all.claim_exposure_id, claim_exposure_type_id, coverage_type, coverage_subtype, transaction_source, sap_product_class_code, line_of_business_code
  • Rows out: Avg 38591.9 rows x 108 workers. Max 39008 rows (seg41) with 10681 ms to first row, 10763 ms to end, start offset by 44379 ms.
59. 0.000 0.000 ↓ 0.0

Sort (cost=17,451,364.29..17,468,834.81 rows=64,706 width=139) (actual rows= loops=)

  • Sort Key (Distinct): pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.policy_brand, pub.mv_claim_transaction_details_all.claim_id, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.policy_id, pub.mv_claim_transaction_details_all.policy_number, pub.mv_claim_transaction_details_all.claim_loss_type_name, pub.mv_claim_transaction_details_all.claim_exposure_id, claim_exposure_type_id, coverage_type, coverage_subtype, transaction_source, sap_product_class_code, line_of_business_code
  • Rows out: Avg 38591.9 rows x 108 workers. Max 39008 rows (seg41) with 10681 ms to first row, 10722 ms to end, start offset by 44379 ms.
  • Executor memory: 35833K bytes avg, 35833K bytes max (seg0).
  • Work_mem used: 35833K bytes avg, 35833K bytes max (seg0). Workfile: (0 spilling)
60. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice1; segments: 108) (cost=16,412,340.23..16,656,927.54 rows=64,706 width=139) (actual rows= loops=)

  • Hash Key: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.policy_brand, pub.mv_claim_transaction_details_all.claim_id, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.policy_id, pub.mv_claim_transaction_details_all.policy_number, pub.mv_claim_transaction_details_all.claim_loss_type_name, pub.mv_claim_transaction_details_all.claim_exposure_id, claim_exposure_type_id, coverage_type, coverage_subtype, transaction_source, sap_product_class_code, line_of_business_code
  • Rows out: Avg 91062.5 rows x 108 workers at destination. Max 92244 rows (seg97) with 4345 ms to first row, 9183 ms to end, start offset by 44479 ms.
61. 0.000 0.000 ↓ 0.0

Window (cost=16,412,340.23..16,517,163.36 rows=64,706 width=139) (actual rows= loops=)

  • Partition By: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.claim_exposure_id
  • Order By: pub.mv_claim_transaction_details_all.transaction_source, pub.mv_claim_transaction_details_all.transaction_date
  • Rows out: Avg 91062.5 rows x 108 workers. Max 92468 rows (seg8) with 7831 ms to first row, 8775 ms to end, start offset by 42061 ms.
62. 0.000 0.000 ↓ 0.0

Sort (cost=16,412,340.23..16,429,810.75 rows=64,706 width=139) (actual rows= loops=)

  • Sort Key: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.claim_exposure_id, pub.mv_claim_transaction_details_all.transaction_source, pub.mv_claim_transaction_details_all.transaction_date
  • Rows out: Avg 91062.5 rows x 108 workers. Max 92468 rows (seg8) with 7831 ms to first row, 7878 ms to end, start offset by 42061 ms.
  • Executor memory: 44025K bytes avg, 44025K bytes max (seg0).
  • Work_mem used: 44025K bytes avg, 44025K bytes max (seg0). Workfile: (0 spilling)
63. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..15,617,903.48 rows=64,706 width=139) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_PI,CC_CI}'::text[])) AND (transaction_source = ANY ('{EXPOSURE,EXPOSURE_HISTORY}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIO,SGIC}'::text[])) AND line_of_business_code::text <> 'CTP'::text
  • Rows out: Avg 91062.5 rows x 108 workers. Max 92468 rows (seg8) with 1032 ms to first row, 6216 ms to end, start offset by 42062 ms.
64. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=17,540,969.13..19,028,650.66 rows=41,352 width=2,450) (actual rows= loops=)

  • Rows out: Avg 80.6 rows x 108 workers. Max 100 rows (seg29) with 11586 ms to first row, 11624 ms to end, start offset by 55450 ms.
65. 0.000 0.000 ↓ 0.0

HashAggregate (cost=17,540,969.13..18,983,991.48 rows=41,352 width=998) (actual rows= loops=)

  • Filter: min(pub.mv_claim_transaction_details_all.claim_exposure_id::integer) IS NULL
  • Group By: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.policy_brand, pub.mv_claim_transaction_details_all.claim_id, pub.mv_claim_transaction_details_all.claim_number
  • Rows out: Avg 80.6 rows x 108 workers. Max 100 rows (seg29) with 11586 ms to first row, 11624 ms to end, start offset by 55450 ms.
  • Executor memory: 7483K bytes avg, 7565K bytes max (seg0).
66. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..15,210,345.76 rows=413,511 width=77) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_PI,CC_CI}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIC,SGIO}'::text[])) AND line_of_business_code::text <> 'CTP'::text
  • Rows out: Avg 465956.8 rows x 108 workers. Max 472454 rows (seg107) with 28 ms to first row, 3966 ms to end, start offset by 55676 ms.
67. 0.000 0.000 ↓ 0.0

Hash (cost=504,712.74..504,712.74 rows=42,485 width=60) (actual rows= loops=)

  • Rows in: Avg 47616.0 rows x 108 workers. Max 47616 rows (seg0) with 69 ms to end, start offset by 44349 ms.
68. 0.000 0.000 ↓ 0.0

Broadcast Motion 108:108 (slice4; segments: 108) (cost=457,449.28..504,712.74 rows=42,485 width=60) (actual rows= loops=)

  • Rows out: Avg 47616.0 rows x 108 workers at destination. Max 47616 rows (seg0) with 0.101 ms to first row, 47 ms to end, start offset by 44349 ms.
69. 0.000 0.000 ↓ 0.0

Subquery Scan landlords (cost=457,449.28..458,405.17 rows=394 width=80) (actual rows= loops=)

  • Rows out: Avg 440.9 rows x 108 workers. Max 495 rows (seg92) with 1927 ms to first row, 1929 ms to end, start offset by 42279 ms.
70. 0.000 0.000 ↓ 0.0

Unique (cost=457,449.28..457,980.33 rows=394 width=14) (actual rows= loops=)

  • Group By: source_system, policy_id, risk_type, risk_count
  • Rows out: Avg 440.9 rows x 108 workers. Max 495 rows (seg92) with 1927 ms to first row, 1928 ms to end, start offset by 42279 ms.
71. 0.000 0.000 ↓ 0.0

Sort (cost=457,449.28..457,555.49 rows=394 width=14) (actual rows= loops=)

  • Sort Key (Distinct): source_system, policy_id, risk_type, risk_count
  • Rows out: Avg 440.9 rows x 108 workers. Max 495 rows (seg92) with 1927 ms to end, start offset by 42279 ms.
  • Executor memory: 137K bytes avg, 145K bytes max (seg0).
  • Work_mem used: 137K bytes avg, 145K bytes max (seg0). Workfile: (0 spilling)
72. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice3; segments: 108) (cost=0.00..454,183.40 rows=394 width=14) (actual rows= loops=)

  • Hash Key: source_system, policy_id, risk_type, risk_count
  • Rows out: Avg 440.9 rows x 108 workers at destination. Max 495 rows (seg92) with 1906 ms to first row, 1926 ms to end, start offset by 42280 ms.
73. 0.000 0.000 ↓ 0.0

Append (cost=0.00..454,183.40 rows=394 width=14) (actual rows= loops=)

  • Rows out: Avg 440.9 rows x 108 workers. Max 501 rows (seg82) with 61 ms to first row, 1832 ms to end, start offset by 42357 ms.
74. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=0.00..91,280.93 rows=394 width=13) (actual rows= loops=)

  • Rows out: Avg 440.3 rows x 108 workers. Max 500 rows (seg82) with 61 ms to first row, 162 ms to end, start offset by 42357 ms.
75. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_pi_policy (cost=0.00..90,856.12 rows=394 width=13) (actual rows= loops=)

  • Filter: policy_risk_type_code::text = ANY ('{LAND,LANDB,LANDC}'::text[])
  • Rows out: Avg 440.3 rows x 108 workers. Max 500 rows (seg82) with 61 ms to first row, 162 ms to end, start offset by 42357 ms.
76. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=362,902.39..362,902.46 rows=1 width=80) (actual rows= loops=)

  • Rows out: Avg 1.2 rows x 54 workers. Max 3 rows (seg19) with 1742 ms to first row, 1743 ms to end, start offset by 42442 ms.
77. 0.000 0.000 ↓ 0.0

HashAggregate (cost=362,902.39..362,902.43 rows=1 width=80) (actual rows= loops=)

  • Group By: ctx.mv_cc_ci_coverage.policy_id
  • Rows out: Avg 1.2 rows x 54 workers. Max 3 rows (seg19) with 1742 ms to first row, 1743 ms to end, start offset by 42442 ms.
  • Executor memory: 2101K bytes avg, 2137K bytes max (seg0).
78. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice2; segments: 108) (cost=362,902.26..362,902.34 rows=1 width=48) (actual rows= loops=)

  • Hash Key: ctx.mv_cc_ci_coverage.policy_id
  • Rows out: Avg 1.4 rows x 54 workers at destination. Max 4 rows (seg19) with 53 ms to first row, 1741 ms to end, start offset by 42444 ms.
79. 0.000 0.000 ↓ 0.0

HashAggregate (cost=362,902.26..362,902.28 rows=1 width=48) (actual rows= loops=)

  • Group By: ctx.mv_cc_ci_coverage.policy_id
  • Rows out: Avg 1.4 rows x 56 workers. Max 4 rows (seg37) with 467 ms to end, start offset by 42418 ms.
  • Executor memory: 2102K bytes avg, 2137K bytes max (seg1).
80. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_ci_coverage (cost=0.00..362,901.84 rows=1 width=13) (actual rows= loops=)

  • Filter: coverage_source_type_code::text = 'PolicySource'::text AND cgu_productclass::text = 'ILL'::text AND (coverage_type_code::text = ANY ('{building_cov,contents_cov}'::text[]))
  • Rows out: Avg 1.4 rows x 56 workers. Max 4 rows (seg37) with 453 ms to first row, 465 ms to end, start offset by 42419 ms.
81. 0.000 0.000 ↓ 0.0

Hash (cost=410,266.41..410,266.41 rows=227,804 width=68) (actual rows= loops=)

  • Rows in: Avg 220273.0 rows x 108 workers. Max 220273 rows (seg0) with 1816 ms to end, start offset by 42531 ms.
82. 0.000 0.000 ↓ 0.0

Broadcast Motion 108:108 (slice6; segments: 108) (cost=154,271.67..410,266.41 rows=227,804 width=68) (actual rows= loops=)

  • Rows out: Avg 220273.0 rows x 108 workers at destination. Max 220273 rows (seg0) with 972 ms to first row, 1459 ms to end, start offset by 42531 ms.
83. 0.000 0.000 ↓ 0.0

HashAggregate (cost=154,271.67..161,960.05 rows=2,110 width=76) (actual rows= loops=)

  • Filter: sum(sor_cc_pi.ccx_coverageapportionment_ext.percentage) > 0::numeric
  • Group By: 'CC_PI'::text, e.claim_id, e.exposure_id, e.claim_order, CASE WHEN sor_cc_pi.ccx_coverageapportionment_ext.claimcentercover::text = ANY ('{"Portable Clothes and Luggage","Portable Sport and Recreational","Portable Electronic Devices","Portable Mobility Scooters","Portable Jewellery and Watches","Portable Glasses and Medical","Unspecified Portable Valuables"}'::text[]) THEN 'HE'::text ELSE NULL::text END::character(2)
  • Rows out: Avg 2039.6 rows x 108 workers. Max 2148 rows (seg85) with 1455 ms to first row, 1467 ms to end, start offset by 42056 ms.
  • Executor memory: 1275K bytes avg, 1289K bytes max (seg1).
84. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,768.71..150,285.08 rows=2,110 width=45) (actual rows= loops=)

  • Hash Cond: e.exposure_id = sor_cc_pi.ccx_coverageapportionment_ext.exposureid
  • Rows out: Avg 2116.1 rows x 108 workers. Max 2221 rows (seg85) with 1412 ms to first row, 1451 ms to end, start offset by 42056 ms.
  • Executor memory: 143K bytes avg, 150K bytes max (seg51).
  • Work_mem used: 143K bytes avg, 150K bytes max (seg51). Workfile: (0 spilling)
  • (seg85) Hash chain length 1.0 avg, 3 max, using 2123 of 131111 buckets.
85. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_pi_exposure e (cost=0.00..123,674.60 rows=38,635 width=24) (actual rows= loops=)

  • Rows out: Avg 38603.0 rows x 108 workers. Max 38746 rows (seg30) with 0.118 ms to first row, 91 ms to end, start offset by 43459 ms.
86. 0.000 0.000 ↓ 0.0

Hash (cost=7,921.15..7,921.15 rows=2,110 width=29) (actual rows= loops=)

  • Rows in: Avg 2116.1 rows x 108 workers. Max 2221 rows (seg85) with 1397 ms to end, start offset by 42058 ms.
87. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice5; segments: 108) (cost=0.00..7,921.15 rows=2,110 width=29) (actual rows= loops=)

  • Hash Key: sor_cc_pi.ccx_coverageapportionment_ext.exposureid
  • Rows out: Avg 2116.1 rows x 108 workers at destination. Max 2221 rows (seg85) with 0.045 ms to first row, 1395 ms to end, start offset by 42058 ms.
88. 0.000 0.000 ↓ 0.0

Seq Scan on ccx_coverageapportionment_ext (cost=0.00..3,365.05 rows=2,110 width=29) (actual rows= loops=)

  • Rows out: Avg 2116.1 rows x 108 workers. Max 2193 rows (seg41) with 31 ms to first row, 54 ms to end, start offset by 42037 ms.
89. 0.000 0.000 ↓ 0.0

Hash (cost=17.64..17.64 rows=16 width=94) (actual rows= loops=)

  • Rows in: Avg 16.0 rows x 108 workers. Max 16 rows (seg0) with 203 ms to end, start offset by 42326 ms.
90. 0.000 0.000 ↓ 0.0

Broadcast Motion 1:108 (slice7; segments: 1) (cost=0.00..17.64 rows=1,728 width=94) (actual rows= loops=)

  • Rows out: Avg 16.0 rows x 108 workers at destination. Max 16 rows (seg0) with 203 ms to end, start offset by 42326 ms.
91. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*" (cost=0.00..0.20 rows=1 width=94) (actual rows= loops=)

  • Rows out: 16 rows with 0.018 ms to first row, 0.070 ms to end, start offset by 42526 ms.
92. 0.000 0.000 ↓ 0.0

Hash (cost=1,944,779.94..1,944,779.94 rows=57,415 width=176) (actual rows= loops=)

  • Rows in: Avg 57376.5 rows x 108 workers. Max 57685 rows (seg52) with 5292 ms to end, start offset by 42317 ms.
93. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice12; segments: 108) (cost=1,696,750.78..1,944,779.94 rows=57,415 width=176) (actual rows= loops=)

  • Hash Key: si.claim_exposure_id, si.claim_id, si.source_system
  • Rows out: Avg 57376.5 rows x 108 workers at destination. Max 57685 rows (seg52) with 4414 ms to first row, 5251 ms to end, start offset by 42317 ms.
94. 0.000 0.000 ↓ 0.0

Unique (cost=1,696,750.78..1,820,765.36 rows=57,415 width=55) (actual rows= loops=)

  • Group By: "?column1?", "?column2?", "?column3?", "?column4?", "?column5?", "?column6?", "?column7?
  • Rows out: Avg 57376.5 rows x 108 workers. Max 57969 rows (seg5) with 4323 ms to first row, 4373 ms to end, start offset by 42492 ms.
95. 0.000 0.000 ↓ 0.0

Sort (cost=1,696,750.78..1,712,252.60 rows=57,415 width=55) (actual rows= loops=)

  • Sort Key (Distinct): source_system, claim_id, claim_exposure_id, bldg_sum_insured, cont_sum_insured, pval_sum_insured, total_sum_insured
  • Rows out: Avg 57376.5 rows x 108 workers. Max 57969 rows (seg5) with 4323 ms to first row, 4338 ms to end, start offset by 42492 ms.
  • Executor memory: 9721K bytes avg, 9721K bytes max (seg0).
  • Work_mem used: 9721K bytes avg, 9721K bytes max (seg0). Workfile: (0 spilling)
96. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice11; segments: 108) (cost=126,151.04..997,184.33 rows=57,415 width=55) (actual rows= loops=)

  • Hash Key: source_system, claim_id, claim_exposure_id, bldg_sum_insured, cont_sum_insured, pval_sum_insured, total_sum_insured
  • Rows out: Avg 57376.5 rows x 108 workers at destination. Max 57969 rows (seg5) with 0.113 ms to first row, 4112 ms to end, start offset by 42493 ms.
97. 0.000 0.000 ↓ 0.0

Append (cost=126,151.04..997,184.33 rows=57,415 width=55) (actual rows= loops=)

  • Rows out: Avg 57376.5 rows x 108 workers. Max 57892 rows (seg9) with 447 ms to first row, 870 ms to end, start offset by 43214 ms.
98. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=126,151.04..521,042.04 rows=38,635 width=70) (actual rows= loops=)

  • Rows out: Avg 38602.7 rows x 108 workers. Max 38937 rows (seg24) with 248 ms to first row, 1528 ms to end, start offset by 42280 ms.
99. 0.000 0.000 ↓ 0.0

Hash Join (cost=126,151.04..479,316.44 rows=38,635 width=70) (actual rows= loops=)

  • Hash Cond: e.coverage_id = c.coverage_id
  • Rows out: Avg 38602.7 rows x 108 workers. Max 38937 rows (seg24) with 248 ms to first row, 1514 ms to end, start offset by 42280 ms.
  • Executor memory: 2500K bytes avg, 2509K bytes max (seg49).
  • Work_mem used: 2500K bytes avg, 2509K bytes max (seg49). Workfile: (0 spilling)
  • (seg24) Hash chain length 1.1 avg, 5 max, using 32914 of 131111 buckets.
100. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice9; segments: 108) (cost=0.00..217,557.20 rows=38,635 width=24) (actual rows= loops=)

  • Hash Key: e.coverage_id
  • Rows out: Avg 38602.7 rows x 108 workers at destination. Max 38937 rows (seg24) with 0.093 ms to first row, 1193 ms to end, start offset by 42528 ms.
101. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_pi_exposure e (cost=0.00..134,106.00 rows=38,635 width=24) (actual rows= loops=)

  • Filter: retired = 0
  • Rows out: Avg 38602.7 rows x 108 workers. Max 38745 rows (seg30) with 0.148 ms to first row, 172 ms to end, start offset by 42125 ms.
102. 0.000 0.000 ↓ 0.0

Hash (cost=76,038.24..76,038.24 rows=37,121 width=62) (actual rows= loops=)

  • Rows in: Avg 37069.1 rows x 108 workers. Max 37174 rows (seg9) with 443 ms to end, start offset by 43218 ms.
103. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_pi_coverage c (cost=0.00..76,038.24 rows=37,121 width=62) (actual rows= loops=)

  • Rows out: Avg 37069.1 rows x 108 workers. Max 37174 rows (seg9) with 85 ms to first row, 398 ms to end, start offset by 43218 ms.
104. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=133,834.60..476,142.29 rows=18,780 width=23) (actual rows= loops=)

  • Rows out: Avg 18773.8 rows x 108 workers. Max 19073 rows (seg63) with 18 ms to first row, 334 ms to end, start offset by 43853 ms.
105. 0.000 0.000 ↓ 0.0

Hash Join (cost=133,834.60..455,860.60 rows=18,780 width=23) (actual rows= loops=)

  • Hash Cond: c.coverage_id = e.coverage_id
  • Rows out: Avg 18773.8 rows x 108 workers. Max 19073 rows (seg63) with 18 ms to first row, 327 ms to end, start offset by 43853 ms.
  • Executor memory: 1027K bytes avg, 1044K bytes max (seg63).
  • Work_mem used: 1027K bytes avg, 1044K bytes max (seg63). Workfile: (0 spilling)
  • (seg63) Hash chain length 1.2 avg, 7 max, using 15588 of 131111 buckets.
106. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_ci_coverage c (cost=0.00..263,559.91 rows=122,645 width=15) (actual rows= loops=)

  • Rows out: Avg 122651.4 rows x 108 workers. Max 122795 rows (seg61) with 0.091 ms to first row, 111 ms to end, start offset by 43899 ms.
107. 0.000 0.000 ↓ 0.0

Hash (cost=108,482.49..108,482.49 rows=18,780 width=24) (actual rows= loops=)

  • Rows in: Avg 18773.8 rows x 108 workers. Max 19073 rows (seg63) with 17 ms to end, start offset by 43854 ms.
108. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice10; segments: 108) (cost=0.00..108,482.49 rows=18,780 width=24) (actual rows= loops=)

  • Hash Key: e.coverage_id
  • Rows out: Avg 18773.8 rows x 108 workers at destination. Max 19073 rows (seg63) with 0.123 ms to first row, 9.558 ms to end, start offset by 43854 ms.
109. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_ci_exposure e (cost=0.00..67,919.11 rows=18,780 width=24) (actual rows= loops=)

  • Filter: retired = 0
  • Rows out: Avg 18773.8 rows x 108 workers. Max 18877 rows (seg7) with 42 ms to first row, 302 ms to end, start offset by 42120 ms.
110. 0.000 0.000 ↓ 0.0

Hash (cost=17,029,434.56..17,029,434.56 rows=22,325 width=194) (actual rows= loops=)

  • Rows in: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8845 ms to end, start offset by 42393 ms.
111. 0.000 0.000 ↓ 0.0

Subquery Scan e (cost=16,999,295.92..17,029,434.56 rows=22,325 width=194) (actual rows= loops=)

  • Rows out: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8737 ms to first row, 8821 ms to end, start offset by 42393 ms.
112. 0.000 0.000 ↓ 0.0

Window (cost=16,999,295.92..17,029,434.56 rows=22,325 width=194) (actual rows= loops=)

  • Partition By: exposure_reserve.source_system, exposure_reserve.claim_number
  • Order By: exposure_reserve.sum_reserve
  • Rows out: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8737 ms to first row, 8812 ms to end, start offset by 42393 ms.
113. 0.000 0.000 ↓ 0.0

Sort (cost=16,999,295.92..17,005,323.65 rows=22,325 width=194) (actual rows= loops=)

  • Sort Key: exposure_reserve.source_system, exposure_reserve.claim_number, exposure_reserve.sum_reserve
  • Rows out: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8737 ms to first row, 8743 ms to end, start offset by 42393 ms.
  • Executor memory: 9721K bytes avg, 9721K bytes max (seg0).
  • Work_mem used: 9721K bytes avg, 9721K bytes max (seg0). Workfile: (0 spilling)
114. 0.000 0.000 ↓ 0.0

Subquery Scan exposure_reserve (cost=16,683,367.61..16,743,705.16 rows=22,325 width=194) (actual rows= loops=)

  • Filter: claim_exposure_id IS NOT NULL OR count_exposure_ids = 1
  • Rows out: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8582 ms to first row, 8683 ms to end, start offset by 42393 ms.
115. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=16,683,367.61..16,743,705.16 rows=22,348 width=194) (actual rows= loops=)

  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 7167 ms to first row, 7422 ms to end, start offset by 42300 ms.
116. 0.000 0.000 ↓ 0.0

Window (cost=16,683,367.61..16,707,502.63 rows=22,348 width=194) (actual rows= loops=)

  • Partition By: coplan.source_system, coplan.claim_number
  • Order By: coplan.claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 7167 ms to first row, 7364 ms to end, start offset by 42300 ms.
117. 0.000 0.000 ↓ 0.0

Sort (cost=16,683,367.61..16,689,401.37 rows=22,348 width=194) (actual rows= loops=)

  • Sort Key: coplan.source_system, coplan.claim_number, coplan.claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 7167 ms to first row, 7187 ms to end, start offset by 42300 ms.
  • Executor memory: 17913K bytes avg, 17913K bytes max (seg0).
  • Work_mem used: 17913K bytes avg, 17913K bytes max (seg0). Workfile: (0 spilling)
118. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=16,379,233.82..16,427,503.86 rows=22,348 width=194) (actual rows= loops=)

  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 6709 ms to first row, 6996 ms to end, start offset by 42301 ms.
119. 0.000 0.000 ↓ 0.0

Window (cost=16,379,233.82..16,403,368.84 rows=22,348 width=194) (actual rows= loops=)

  • Partition By: "Window".source_system, "Window".claim_number
  • Order By: "Window".claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 6709 ms to first row, 6984 ms to end, start offset by 42301 ms.
120. 0.000 0.000 ↓ 0.0

Sort (cost=16,379,233.82..16,385,267.57 rows=22,348 width=194) (actual rows= loops=)

  • Sort Key: "Window".source_system, "Window".claim_number, "Window".claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 6709 ms to first row, 6727 ms to end, start offset by 42301 ms.
  • Executor memory: 17913K bytes avg, 17913K bytes max (seg0).
  • Work_mem used: 17913K bytes avg, 17913K bytes max (seg0). Workfile: (0 spilling)
121. 0.000 0.000 ↓ 0.0

HashAggregate (cost=16,063,032.51..16,099,235.04 rows=22,348 width=194) (actual rows= loops=)

  • Group By: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 6295 ms to first row, 6467 ms to end, start offset by 42301 ms.
  • Executor memory: 7590K bytes avg, 7628K bytes max (seg0).
122. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..15,821,682.34 rows=223,473 width=43) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_PI,CC_CI}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIC,SGIO}'::text[])) AND (transaction_source = ANY ('{RESERVE,RECOVERY,RECOVERABLE,EXPOSURE,EXPOSURE_HISTORY}'::text[]))
  • Rows out: Avg 331465.7 rows x 108 workers. Max 336080 rows (seg8) with 421 ms to first row, 5527 ms to end, start offset by 42302 ms.
123. 0.000 0.000 ↓ 0.0

Hash (cost=52,985,548.00..52,985,548.00 rows=10 width=194) (actual rows= loops=)

  • Rows in: Avg 29020.4 rows x 108 workers. Max 29440 rows (seg39) with 8710 ms to end, start offset by 91311 ms.
124. 0.000 0.000 ↓ 0.0

Subquery Scan t2 (cost=52,690,845.69..52,985,548.00 rows=10 width=162) (actual rows= loops=)

  • Rows out: Avg 29020.4 rows x 108 workers. Max 29440 rows (seg39) with 6904 ms to first row, 8653 ms to end, start offset by 91311 ms.
125. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=52,690,845.69..52,985,538.00 rows=10 width=162) (actual rows= loops=)

  • Group By: claim_transaction_details_all.claim_number
  • Rows out: Avg 29020.4 rows x 108 workers. Max 29440 rows (seg39) with 6904 ms to first row, 8617 ms to end, start offset by 91311 ms.
  • Executor memory: 1860043K bytes avg, 1886936K bytes max (seg39).
  • Work_mem used: 33K bytes avg, 49K bytes max (seg5).
126. 0.000 0.000 ↓ 0.0

Sort (cost=52,690,845.69..52,764,510.02 rows=272,831 width=678) (actual rows= loops=)

  • Sort Key: claim_transaction_details_all.claim_number
  • Rows out: Avg 412991.4 rows x 108 workers. Max 418781 rows (seg107) with 8604 ms to first row, 8798 ms to end, start offset by 91083 ms.
  • Executor memory: 77817K bytes avg, 77817K bytes max (seg0).
  • Work_mem used: 77817K bytes avg, 77817K bytes max (seg0). Workfile: (0 spilling)
127. 0.000 0.000 ↓ 0.0

Subquery Scan claim_transaction_details_all (cost=0.00..16,433,018.92 rows=272,831 width=678) (actual rows= loops=)

  • Rows out: Avg 412991.4 rows x 108 workers. Max 418781 rows (seg107) with 69 ms to first row, 7273 ms to end, start offset by 91085 ms.
128. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..16,433,018.92 rows=272,831 width=380) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_CI,CC_PI}'::text[])) AND (transaction_source = ANY ('{EXPOSURE,PAYMENT,RESERVE,RECOVERY,RECOVERABLE,CLAIM_HISTORY}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIO,SGIC}'::text[])) AND line_of_business_code::text <> 'CTP'::text
  • Rows out: Avg 412991.4 rows x 108 workers. Max 418781 rows (seg107) with 69 ms to first row, 7134 ms to end, start offset by 91085 ms.
129. 0.000 0.000 ↓ 0.0

Hash (cost=258,903.88..258,903.88 rows=33,919 width=45) (actual rows= loops=)

  • Rows in: Avg 33905.3 rows x 108 workers. Max 34343 rows (seg78) with 237 ms to end, start offset by 90814 ms.
130. 0.000 0.000 ↓ 0.0

Seq Scan on mv_motor_exposure motor_exp (cost=0.00..258,903.88 rows=33,919 width=45) (actual rows= loops=)

  • Filter: incident_vehicle_loss_party_name = ANY ('{"Insured''s loss",Insured,insured,""}'::text[])
  • Rows out: Avg 33905.3 rows x 108 workers. Max 34343 rows (seg78) with 10 ms to first row, 208 ms to end, start offset by 90814 ms.
131. 0.000 0.000 ↓ 0.0

Hash (cost=79,751,295.45..79,751,295.45 rows=9,260 width=714) (actual rows= loops=)

  • Rows in: Avg 29485.8 rows x 108 workers. Max 29924 rows (seg7) with 47097 ms to end, start offset by 43714 ms.
132. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice32; segments: 108) (cost=79,666,295.45..79,751,295.45 rows=9,260 width=714) (actual rows= loops=)

  • Hash Key: aggregates_by_risk.claim_number::text
  • Rows out: Avg 29492.7 rows x 108 workers at destination. Max 29929 rows (seg7) with 46708 ms to first row, 47074 ms to end, start offset by 43714 ms.
133. 0.000 0.000 ↓ 0.0

HashAggregate (cost=79,666,295.45..79,731,295.45 rows=9,260 width=746) (actual rows= loops=)

  • Group By: clm_trn_det.claim_number, exp_risk.risk_code
  • Rows out: Avg 29492.7 rows x 108 workers. Max 29901 rows (seg90) with 47957 ms to first row, 48047 ms to end, start offset by 42462 ms.
  • Executor memory: 16975K bytes avg, 17172K bytes max (seg1).
134. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice31; segments: 108) (cost=79,291,295.45..79,598,795.45 rows=9,260 width=682) (actual rows= loops=)

  • Hash Key: clm_trn_det.claim_number, exp_risk.risk_code
  • Rows out: Avg 29492.7 rows x 108 workers at destination. Max 29901 rows (seg90) with 41698 ms to first row, 47835 ms to end, start offset by 42463 ms.
135. 0.000 0.000 ↓ 0.0

HashAggregate (cost=79,291,295.45..79,578,795.45 rows=9,260 width=682) (actual rows= loops=)

  • Group By: clm_trn_det.claim_number, exp_risk.risk_code
  • Rows out: Avg 29492.7 rows x 108 workers. Max 29929 rows (seg7) with 44920 ms to first row, 45003 ms to end, start offset by 42467 ms.
  • Executor memory: 22591K bytes avg, 22872K bytes max (seg7).
136. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=59,406,095.44..77,597,015.96 rows=272,831 width=1,533) (actual rows= loops=)

  • Hash Cond: clm_trn_det.claim_number::text = exp_risk.claim_number::text AND clm_trn_det.source_system = exp_risk.source_system
  • Join Filter: clm_trn_det.claim_exposure_id = exp_risk.claim_exposure_id OR (clm_trn_det.claim_exposure_id IS NULL AND exp_risk.risk_default_exposure_flag = 'Y'::text)
  • Rows out: Avg 413387.3 rows x 108 workers. Max 419176 rows (seg107) with 30657 ms to first row, 38263 ms to end, start offset by 42150 ms.
  • Executor memory: 2721K bytes avg, 2759K bytes max (seg39).
  • Work_mem used: 2721K bytes avg, 2759K bytes max (seg39). Workfile: (0 spilling)
  • (seg107) Hash chain length 1.5 avg, 9 max, using 26304 of 131111 buckets.
137. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..16,433,018.92 rows=272,831 width=380) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_CI,CC_PI}'::text[])) AND (transaction_source = ANY ('{EXPOSURE,PAYMENT,RESERVE,RECOVERY,RECOVERABLE,CLAIM_HISTORY}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIO,SGIC}'::text[])) AND line_of_business_code::text <> 'CTP'::text
  • Rows out: Avg 412991.4 rows x 108 workers. Max 418781 rows (seg107) with 94 ms to first row, 6552 ms to end, start offset by 72713 ms.
138. 0.000 0.000 ↓ 0.0

Hash (cost=59,339,106.65..59,339,106.65 rows=41,352 width=202) (actual rows= loops=)

  • Rows in: Avg 38689.8 rows x 108 workers. Max 39233 rows (seg39) with 30436 ms to end, start offset by 42273 ms.
139. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice30; segments: 108) (cost=59,048,821.92..59,339,106.65 rows=41,352 width=202) (actual rows= loops=)

  • Hash Key: exp_risk.claim_number::text
  • Rows out: Avg 38689.8 rows x 108 workers at destination. Max 39233 rows (seg39) with 29340 ms to first row, 30411 ms to end, start offset by 42273 ms.
140. 0.000 0.000 ↓ 0.0

Subquery Scan exp_risk (cost=59,048,821.92..59,249,788.27 rows=41,352 width=202) (actual rows= loops=)

  • Rows out: Avg 38689.8 rows x 108 workers. Max 38974 rows (seg48) with 28768 ms to first row, 29583 ms to end, start offset by 43043 ms.
141. 0.000 0.000 ↓ 0.0

Window (cost=59,048,821.92..59,205,129.08 rows=41,352 width=602) (actual rows= loops=)

  • Partition By: t.source_system, t.claim_id, t.claim_exposure_id
  • Order By: t.risk_proportion, t.risk_code
  • Rows out: Avg 38689.8 rows x 108 workers. Max 38974 rows (seg48) with 28768 ms to first row, 29551 ms to end, start offset by 43043 ms.
142. 0.000 0.000 ↓ 0.0

Sort (cost=59,048,821.92..59,059,986.72 rows=41,352 width=602) (actual rows= loops=)

  • Sort Key: t.source_system, t.claim_id, t.claim_exposure_id, t.risk_proportion, t.risk_code
  • Rows out: Avg 38689.8 rows x 108 workers. Max 38974 rows (seg48) with 28767 ms to first row, 28780 ms to end, start offset by 43043 ms.
  • Executor memory: 17913K bytes avg, 17913K bytes max (seg0).
  • Work_mem used: 17913K bytes avg, 17913K bytes max (seg0). Workfile: (0 spilling)
143. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice29; segments: 108) (cost=37,990,677.70..58,555,549.43 rows=41,352 width=602) (actual rows= loops=)

  • Hash Key: t.source_system, t.claim_id, t.claim_exposure_id
  • Rows out: Avg 38689.8 rows x 108 workers at destination. Max 38974 rows (seg48) with 12693 ms to first row, 28396 ms to end, start offset by 43044 ms.
144. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=37,990,677.70..58,466,231.05 rows=41,352 width=602) (actual rows= loops=)

  • Hash Cond: t.source_system = e.source_system AND t.claim_number::text = e.claim_number::text AND t.claim_exposure_id = e.claim_exposure_id
  • Rows out: Avg 38689.8 rows x 108 workers. Max 39233 rows (seg39) with 13172 ms to first row, 28869 ms to end, start offset by 42482 ms.
  • Executor memory: 2115K bytes avg, 2145K bytes max (seg39).
  • Work_mem used: 2115K bytes avg, 2145K bytes max (seg39). Workfile: (0 spilling)
  • (seg39) Hash chain length 1.7 avg, 7 max, using 22826 of 32779 buckets.
145. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice28; segments: 108) (cost=20,919,049.05..41,316,368.06 rows=41,352 width=570) (actual rows= loops=)

  • Hash Key: t.claim_number::text
  • Rows out: Avg 38689.8 rows x 108 workers at destination. Max 39233 rows (seg39) with 4526 ms to first row, 20140 ms to end, start offset by 51128 ms.
146. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=20,919,049.05..41,227,049.68 rows=41,352 width=570) (actual rows= loops=)

  • Hash Cond: t.source_system = si.source_system AND t.claim_id = si.claim_id AND t.claim_exposure_id::bigint = si.claim_exposure_id
  • Rows out: Avg 38689.8 rows x 108 workers. Max 38948 rows (seg100) with 13049 ms to first row, 29011 ms to end, start offset by 42332 ms.
  • Executor memory: 4200K bytes avg, 4223K bytes max (seg100).
  • Work_mem used: 4200K bytes avg, 4223K bytes max (seg100). Workfile: (0 spilling)
  • (seg100) Hash chain length 1.7 avg, 9 max, using 34155 of 65539 buckets.
147. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice23; segments: 108) (cost=18,865,756.35..39,095,326.48 rows=41,352 width=442) (actual rows= loops=)

  • Hash Key: t.claim_exposure_id::bigint, t.claim_id, t.source_system
  • Rows out: Avg 38689.8 rows x 108 workers at destination. Max 38948 rows (seg100) with 7783 ms to first row, 23634 ms to end, start offset by 47597 ms.
148. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,865,756.35..39,006,008.10 rows=41,352 width=344) (actual rows= loops=)

  • Hash Cond: tr.instype2_code = rcm.instype2_code
  • Rows out: Avg 38689.8 rows x 108 workers. Max 39113 rows (seg41) with 13489 ms to first row, 25379 ms to end, start offset by 42163 ms.
  • Executor memory: 1K bytes avg, 1K bytes max (seg0).
  • Work_mem used: 1K bytes avg, 1K bytes max (seg0). Workfile: (0 spilling)
  • (seg41) Hash chain length 1.0 avg, 1 max, using 16 of 65539 buckets.
149. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,865,717.11..38,813,621.73 rows=41,352 width=1,038) (actual rows= loops=)

  • Hash Cond: trc.source_system = ca.source_system AND trc.claim_id = ca.claim_id AND trc.claim_exposure_id = ca.claim_exposure_id::text
  • Rows out: Avg 38689.8 rows x 108 workers. Max 39113 rows (seg41) with 13332 ms to first row, 25104 ms to end, start offset by 42319 ms.
  • Executor memory: 13896K bytes avg, 13896K bytes max (seg0).
  • Work_mem used: 13896K bytes avg, 13896K bytes max (seg0). Workfile: (0 spilling)
  • (seg41) Hash chain length 2.0 avg, 10 max, using 107852 of 131111 buckets.
150. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,024,901.14..37,536,279.91 rows=41,352 width=1,018) (actual rows= loops=)

  • Hash Cond: trc.source_system = ll.source_system AND trc.policy_id::bigint = ll.policy_id
  • Rows out: Avg 38672.5 rows x 108 workers. Max 39093 rows (seg41) with 11445 ms to first row, 22961 ms to end, start offset by 44206 ms.
  • Executor memory: 2232K bytes avg, 2232K bytes max (seg0).
  • Work_mem used: 2232K bytes avg, 2232K bytes max (seg0). Workfile: (0 spilling)
  • (seg41) Hash chain length 1.2 avg, 6 max, using 40348 of 131111 buckets.
151. 0.000 0.000 ↓ 0.0

Append (cost=17,451,364.29..36,742,072.84 rows=41,352 width=2,450) (actual rows= loops=)

  • Rows out: Avg 38672.5 rows x 108 workers. Max 39093 rows (seg41) with 11283 ms to first row, 22681 ms to end, start offset by 44367 ms.
152. 0.000 0.000 ↓ 0.0

Subquery Scan mv_claim_transaction_details_all (cost=17,451,364.29..17,713,422.17 rows=1 width=1,496) (actual rows= loops=)

  • Rows out: Avg 38591.9 rows x 108 workers. Max 39008 rows (seg41) with 11283 ms to first row, 11493 ms to end, start offset by 44367 ms.
153. 0.000 0.000 ↓ 0.0

Unique (cost=17,451,364.29..17,713,422.12 rows=0 width=139) (actual rows= loops=)

  • Group By: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.policy_brand, pub.mv_claim_transaction_details_all.claim_id, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.policy_id, pub.mv_claim_transaction_details_all.policy_number, pub.mv_claim_transaction_details_all.claim_loss_type_name, pub.mv_claim_transaction_details_all.claim_exposure_id, claim_exposure_type_id, coverage_type, coverage_subtype, transaction_source, sap_product_class_code, line_of_business_code
  • Rows out: Avg 38591.9 rows x 108 workers. Max 39008 rows (seg41) with 11283 ms to first row, 11376 ms to end, start offset by 44367 ms.
154. 0.000 0.000 ↓ 0.0

Sort (cost=17,451,364.29..17,468,834.81 rows=64,706 width=139) (actual rows= loops=)

  • Sort Key (Distinct): pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.policy_brand, pub.mv_claim_transaction_details_all.claim_id, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.policy_id, pub.mv_claim_transaction_details_all.policy_number, pub.mv_claim_transaction_details_all.claim_loss_type_name, pub.mv_claim_transaction_details_all.claim_exposure_id, claim_exposure_type_id, coverage_type, coverage_subtype, transaction_source, sap_product_class_code, line_of_business_code
  • Rows out: Avg 38591.9 rows x 108 workers. Max 39008 rows (seg41) with 11283 ms to first row, 11315 ms to end, start offset by 44367 ms.
  • Executor memory: 35833K bytes avg, 35833K bytes max (seg0).
  • Work_mem used: 35833K bytes avg, 35833K bytes max (seg0). Workfile: (0 spilling)
155. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice16; segments: 108) (cost=16,412,340.23..16,656,927.54 rows=64,706 width=139) (actual rows= loops=)

  • Hash Key: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.policy_brand, pub.mv_claim_transaction_details_all.claim_id, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.policy_id, pub.mv_claim_transaction_details_all.policy_number, pub.mv_claim_transaction_details_all.claim_loss_type_name, pub.mv_claim_transaction_details_all.claim_exposure_id, claim_exposure_type_id, coverage_type, coverage_subtype, transaction_source, sap_product_class_code, line_of_business_code
  • Rows out: Avg 91062.5 rows x 108 workers at destination. Max 92244 rows (seg97) with 4294 ms to first row, 10075 ms to end, start offset by 44385 ms.
156. 0.000 0.000 ↓ 0.0

Window (cost=16,412,340.23..16,517,163.36 rows=64,706 width=139) (actual rows= loops=)

  • Partition By: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.claim_exposure_id
  • Order By: pub.mv_claim_transaction_details_all.transaction_source, pub.mv_claim_transaction_details_all.transaction_date
  • Rows out: Avg 91062.5 rows x 108 workers. Max 92468 rows (seg8) with 7206 ms to first row, 7821 ms to end, start offset by 42010 ms.
157. 0.000 0.000 ↓ 0.0

Sort (cost=16,412,340.23..16,429,810.75 rows=64,706 width=139) (actual rows= loops=)

  • Sort Key: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.claim_exposure_id, pub.mv_claim_transaction_details_all.transaction_source, pub.mv_claim_transaction_details_all.transaction_date
  • Rows out: Avg 91062.5 rows x 108 workers. Max 92468 rows (seg8) with 7206 ms to first row, 7255 ms to end, start offset by 42010 ms.
  • Executor memory: 44025K bytes avg, 44025K bytes max (seg0).
  • Work_mem used: 44025K bytes avg, 44025K bytes max (seg0). Workfile: (0 spilling)
158. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..15,617,903.48 rows=64,706 width=139) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_PI,CC_CI}'::text[])) AND (transaction_source = ANY ('{EXPOSURE,EXPOSURE_HISTORY}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIO,SGIC}'::text[])) AND line_of_business_code::text <> 'CTP'::text
  • Rows out: Avg 91062.5 rows x 108 workers. Max 92468 rows (seg8) with 1047 ms to first row, 6224 ms to end, start offset by 42010 ms.
159. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=17,540,969.13..19,028,650.66 rows=41,352 width=2,450) (actual rows= loops=)

  • Rows out: Avg 80.6 rows x 108 workers. Max 100 rows (seg29) with 10758 ms to first row, 10782 ms to end, start offset by 56457 ms.
160. 0.000 0.000 ↓ 0.0

HashAggregate (cost=17,540,969.13..18,983,991.48 rows=41,352 width=998) (actual rows= loops=)

  • Filter: min(pub.mv_claim_transaction_details_all.claim_exposure_id::integer) IS NULL
  • Group By: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.policy_brand, pub.mv_claim_transaction_details_all.claim_id, pub.mv_claim_transaction_details_all.claim_number
  • Rows out: Avg 80.6 rows x 108 workers. Max 100 rows (seg29) with 10758 ms to first row, 10782 ms to end, start offset by 56457 ms.
  • Executor memory: 7483K bytes avg, 7565K bytes max (seg0).
161. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..15,210,345.76 rows=413,511 width=77) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_PI,CC_CI}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIC,SGIO}'::text[])) AND line_of_business_code::text <> 'CTP'::text
  • Rows out: Avg 465956.8 rows x 108 workers. Max 472454 rows (seg107) with 32 ms to first row, 5452 ms to end, start offset by 57929 ms.
162. 0.000 0.000 ↓ 0.0

Hash (cost=504,712.74..504,712.74 rows=42,485 width=60) (actual rows= loops=)

  • Rows in: Avg 47616.0 rows x 108 workers. Max 47616 rows (seg0) with 146 ms to end, start offset by 44207 ms.
163. 0.000 0.000 ↓ 0.0

Broadcast Motion 108:108 (slice19; segments: 108) (cost=457,449.28..504,712.74 rows=42,485 width=60) (actual rows= loops=)

  • Rows out: Avg 47616.0 rows x 108 workers at destination. Max 47616 rows (seg0) with 75 ms to first row, 123 ms to end, start offset by 44207 ms.
164. 0.000 0.000 ↓ 0.0

Subquery Scan landlords (cost=457,449.28..458,405.17 rows=394 width=80) (actual rows= loops=)

  • Rows out: Avg 440.9 rows x 108 workers. Max 495 rows (seg92) with 2224 ms to first row, 2225 ms to end, start offset by 42065 ms.
165. 0.000 0.000 ↓ 0.0

Unique (cost=457,449.28..457,980.33 rows=394 width=14) (actual rows= loops=)

  • Group By: source_system, policy_id, risk_type, risk_count
  • Rows out: Avg 440.9 rows x 108 workers. Max 495 rows (seg92) with 2224 ms to end, start offset by 42065 ms.
166. 0.000 0.000 ↓ 0.0

Sort (cost=457,449.28..457,555.49 rows=394 width=14) (actual rows= loops=)

  • Sort Key (Distinct): source_system, policy_id, risk_type, risk_count
  • Rows out: Avg 440.9 rows x 108 workers. Max 495 rows (seg92) with 2224 ms to end, start offset by 42065 ms.
  • Executor memory: 137K bytes avg, 145K bytes max (seg0).
  • Work_mem used: 137K bytes avg, 145K bytes max (seg0). Workfile: (0 spilling)
167. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice18; segments: 108) (cost=0.00..454,183.40 rows=394 width=14) (actual rows= loops=)

  • Hash Key: source_system, policy_id, risk_type, risk_count
  • Rows out: Avg 440.9 rows x 108 workers at destination. Max 495 rows (seg92) with 2141 ms to first row, 2214 ms to end, start offset by 42074 ms.
168. 0.000 0.000 ↓ 0.0

Append (cost=0.00..454,183.40 rows=394 width=14) (actual rows= loops=)

  • Rows out: Avg 440.9 rows x 108 workers. Max 501 rows (seg82) with 79 ms to first row, 2109 ms to end, start offset by 42108 ms.
169. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=0.00..91,280.93 rows=394 width=13) (actual rows= loops=)

  • Rows out: Avg 440.3 rows x 108 workers. Max 500 rows (seg82) with 79 ms to first row, 205 ms to end, start offset by 42108 ms.
170. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_pi_policy (cost=0.00..90,856.12 rows=394 width=13) (actual rows= loops=)

  • Filter: policy_risk_type_code::text = ANY ('{LAND,LANDB,LANDC}'::text[])
  • Rows out: Avg 440.3 rows x 108 workers. Max 500 rows (seg82) with 79 ms to first row, 204 ms to end, start offset by 42108 ms.
171. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=362,902.39..362,902.46 rows=1 width=80) (actual rows= loops=)

  • Rows out: Avg 1.2 rows x 54 workers. Max 3 rows (seg19) with 1774 ms to end, start offset by 42440 ms.
172. 0.000 0.000 ↓ 0.0

HashAggregate (cost=362,902.39..362,902.43 rows=1 width=80) (actual rows= loops=)

  • Group By: ctx.mv_cc_ci_coverage.policy_id
  • Rows out: Avg 1.2 rows x 54 workers. Max 3 rows (seg19) with 1774 ms to end, start offset by 42440 ms.
  • Executor memory: 2101K bytes avg, 2137K bytes max (seg0).
173. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice17; segments: 108) (cost=362,902.26..362,902.34 rows=1 width=48) (actual rows= loops=)

  • Hash Key: ctx.mv_cc_ci_coverage.policy_id
  • Rows out: Avg 1.4 rows x 54 workers at destination. Max 4 rows (seg19) with 49 ms to first row, 1772 ms to end, start offset by 42441 ms.
174. 0.000 0.000 ↓ 0.0

HashAggregate (cost=362,902.26..362,902.28 rows=1 width=48) (actual rows= loops=)

  • Group By: ctx.mv_cc_ci_coverage.policy_id
  • Rows out: Avg 1.4 rows x 56 workers. Max 4 rows (seg37) with 329 ms to end, start offset by 42561 ms.
  • Executor memory: 2102K bytes avg, 2137K bytes max (seg1).
175. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_ci_coverage (cost=0.00..362,901.84 rows=1 width=13) (actual rows= loops=)

  • Filter: coverage_source_type_code::text = 'PolicySource'::text AND cgu_productclass::text = 'ILL'::text AND (coverage_type_code::text = ANY ('{building_cov,contents_cov}'::text[]))
  • Rows out: Avg 1.4 rows x 56 workers. Max 4 rows (seg37) with 306 ms to first row, 327 ms to end, start offset by 42563 ms.
176. 0.000 0.000 ↓ 0.0

Hash (cost=410,266.41..410,266.41 rows=227,804 width=68) (actual rows= loops=)

  • Rows in: Avg 220273.0 rows x 108 workers. Max 220273 rows (seg0) with 1884 ms to end, start offset by 42321 ms.
177. 0.000 0.000 ↓ 0.0

Broadcast Motion 108:108 (slice21; segments: 108) (cost=154,271.67..410,266.41 rows=227,804 width=68) (actual rows= loops=)

  • Rows out: Avg 220273.0 rows x 108 workers at destination. Max 220273 rows (seg0) with 956 ms to first row, 1559 ms to end, start offset by 42321 ms.
178. 0.000 0.000 ↓ 0.0

HashAggregate (cost=154,271.67..161,960.05 rows=2,110 width=76) (actual rows= loops=)

  • Filter: sum(sor_cc_pi.ccx_coverageapportionment_ext.percentage) > 0::numeric
  • Group By: 'CC_PI'::text, e.claim_id, e.exposure_id, e.claim_order, CASE WHEN sor_cc_pi.ccx_coverageapportionment_ext.claimcentercover::text = ANY ('{"Portable Clothes and Luggage","Portable Sport and Recreational","Portable Electronic Devices","Portable Mobility Scooters","Portable Jewellery and Watches","Portable Glasses and Medical","Unspecified Portable Valuables"}'::text[]) THEN 'HE'::text ELSE NULL::text END::character(2)
  • Rows out: Avg 2039.6 rows x 108 workers. Max 2148 rows (seg85) with 1023 ms to first row, 1028 ms to end, start offset by 42338 ms.
  • Executor memory: 1275K bytes avg, 1289K bytes max (seg1).
179. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,768.71..150,285.08 rows=2,110 width=45) (actual rows= loops=)

  • Hash Cond: e.exposure_id = sor_cc_pi.ccx_coverageapportionment_ext.exposureid
  • Rows out: Avg 2116.1 rows x 108 workers. Max 2221 rows (seg85) with 945 ms to first row, 1016 ms to end, start offset by 42340 ms.
  • Executor memory: 143K bytes avg, 150K bytes max (seg51).
  • Work_mem used: 143K bytes avg, 150K bytes max (seg51). Workfile: (0 spilling)
  • (seg85) Hash chain length 1.0 avg, 3 max, using 2123 of 131111 buckets.
180. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_pi_exposure e (cost=0.00..123,674.60 rows=38,635 width=24) (actual rows= loops=)

  • Rows out: Avg 38603.0 rows x 108 workers. Max 38746 rows (seg30) with 0.148 ms to first row, 58 ms to end, start offset by 43242 ms.
181. 0.000 0.000 ↓ 0.0

Hash (cost=7,921.15..7,921.15 rows=2,110 width=29) (actual rows= loops=)

  • Rows in: Avg 2116.1 rows x 108 workers. Max 2221 rows (seg85) with 886 ms to end, start offset by 42342 ms.
182. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice20; segments: 108) (cost=0.00..7,921.15 rows=2,110 width=29) (actual rows= loops=)

  • Hash Key: sor_cc_pi.ccx_coverageapportionment_ext.exposureid
  • Rows out: Avg 2116.1 rows x 108 workers at destination. Max 2221 rows (seg85) with 0.054 ms to first row, 884 ms to end, start offset by 42342 ms.
183. 0.000 0.000 ↓ 0.0

Seq Scan on ccx_coverageapportionment_ext (cost=0.00..3,365.05 rows=2,110 width=29) (actual rows= loops=)

  • Rows out: Avg 2116.1 rows x 108 workers. Max 2193 rows (seg41) with 0.127 ms to first row, 13 ms to end, start offset by 42089 ms.
184. 0.000 0.000 ↓ 0.0

Hash (cost=17.64..17.64 rows=16 width=94) (actual rows= loops=)

  • Rows in: Avg 16.0 rows x 108 workers. Max 16 rows (seg0) with 79 ms to end, start offset by 42240 ms.
185. 0.000 0.000 ↓ 0.0

Broadcast Motion 1:108 (slice22; segments: 1) (cost=0.00..17.64 rows=1,728 width=94) (actual rows= loops=)

  • Rows out: Avg 16.0 rows x 108 workers at destination. Max 16 rows (seg0) with 79 ms to end, start offset by 42240 ms.
186. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*" (cost=0.00..0.20 rows=1 width=94) (actual rows= loops=)

  • Rows out: 16 rows with 0.025 ms to first row, 0.082 ms to end, start offset by 42318 ms.
187. 0.000 0.000 ↓ 0.0

Hash (cost=1,944,779.94..1,944,779.94 rows=57,415 width=176) (actual rows= loops=)

  • Rows in: Avg 57376.5 rows x 108 workers. Max 57685 rows (seg52) with 5178 ms to end, start offset by 42407 ms.
188. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice27; segments: 108) (cost=1,696,750.78..1,944,779.94 rows=57,415 width=176) (actual rows= loops=)

  • Hash Key: si.claim_exposure_id, si.claim_id, si.source_system
  • Rows out: Avg 57376.5 rows x 108 workers at destination. Max 57685 rows (seg52) with 4204 ms to first row, 5141 ms to end, start offset by 42407 ms.
189. 0.000 0.000 ↓ 0.0

Unique (cost=1,696,750.78..1,820,765.36 rows=57,415 width=55) (actual rows= loops=)

  • Group By: "?column1?", "?column2?", "?column3?", "?column4?", "?column5?", "?column6?", "?column7?
  • Rows out: Avg 57376.5 rows x 108 workers. Max 57969 rows (seg5) with 3992 ms to first row, 4072 ms to end, start offset by 42614 ms.
190. 0.000 0.000 ↓ 0.0

Sort (cost=1,696,750.78..1,712,252.60 rows=57,415 width=55) (actual rows= loops=)

  • Sort Key (Distinct): source_system, claim_id, claim_exposure_id, bldg_sum_insured, cont_sum_insured, pval_sum_insured, total_sum_insured
  • Rows out: Avg 57376.5 rows x 108 workers. Max 57969 rows (seg5) with 3991 ms to first row, 4010 ms to end, start offset by 42614 ms.
  • Executor memory: 9721K bytes avg, 9721K bytes max (seg0).
  • Work_mem used: 9721K bytes avg, 9721K bytes max (seg0). Workfile: (0 spilling)
191. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice26; segments: 108) (cost=126,151.04..997,184.33 rows=57,415 width=55) (actual rows= loops=)

  • Hash Key: source_system, claim_id, claim_exposure_id, bldg_sum_insured, cont_sum_insured, pval_sum_insured, total_sum_insured
  • Rows out: Avg 57376.5 rows x 108 workers at destination. Max 57969 rows (seg5) with 0.103 ms to first row, 3885 ms to end, start offset by 42615 ms.
192. 0.000 0.000 ↓ 0.0

Append (cost=126,151.04..997,184.33 rows=57,415 width=55) (actual rows= loops=)

  • Rows out: Avg 57376.5 rows x 108 workers. Max 57892 rows (seg9) with 409 ms to first row, 825 ms to end, start offset by 43226 ms.
193. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=126,151.04..521,042.04 rows=38,635 width=70) (actual rows= loops=)

  • Rows out: Avg 38602.7 rows x 108 workers. Max 38937 rows (seg24) with 495 ms to first row, 1636 ms to end, start offset by 42037 ms.
194. 0.000 0.000 ↓ 0.0

Hash Join (cost=126,151.04..479,316.44 rows=38,635 width=70) (actual rows= loops=)

  • Hash Cond: e.coverage_id = c.coverage_id
  • Rows out: Avg 38602.7 rows x 108 workers. Max 38937 rows (seg24) with 495 ms to first row, 1613 ms to end, start offset by 42037 ms.
  • Executor memory: 2500K bytes avg, 2509K bytes max (seg49).
  • Work_mem used: 2500K bytes avg, 2509K bytes max (seg49). Workfile: (0 spilling)
  • (seg24) Hash chain length 1.1 avg, 5 max, using 32914 of 131111 buckets.
195. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice24; segments: 108) (cost=0.00..217,557.20 rows=38,635 width=24) (actual rows= loops=)

  • Hash Key: e.coverage_id
  • Rows out: Avg 38602.7 rows x 108 workers at destination. Max 38937 rows (seg24) with 0.067 ms to first row, 1049 ms to end, start offset by 42532 ms.
196. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_pi_exposure e (cost=0.00..134,106.00 rows=38,635 width=24) (actual rows= loops=)

  • Filter: retired = 0
  • Rows out: Avg 38602.7 rows x 108 workers. Max 38745 rows (seg30) with 41 ms to first row, 252 ms to end, start offset by 42068 ms.
197. 0.000 0.000 ↓ 0.0

Hash (cost=76,038.24..76,038.24 rows=37,121 width=62) (actual rows= loops=)

  • Rows in: Avg 37069.1 rows x 108 workers. Max 37174 rows (seg9) with 407 ms to end, start offset by 43228 ms.
198. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_pi_coverage c (cost=0.00..76,038.24 rows=37,121 width=62) (actual rows= loops=)

  • Rows out: Avg 37069.1 rows x 108 workers. Max 37174 rows (seg9) with 76 ms to first row, 318 ms to end, start offset by 43228 ms.
199. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=133,834.60..476,142.29 rows=18,780 width=23) (actual rows= loops=)

  • Rows out: Avg 18773.8 rows x 108 workers. Max 19073 rows (seg63) with 23 ms to first row, 195 ms to end, start offset by 43743 ms.
200. 0.000 0.000 ↓ 0.0

Hash Join (cost=133,834.60..455,860.60 rows=18,780 width=23) (actual rows= loops=)

  • Hash Cond: c.coverage_id = e.coverage_id
  • Rows out: Avg 18773.8 rows x 108 workers. Max 19073 rows (seg63) with 23 ms to first row, 188 ms to end, start offset by 43743 ms.
  • Executor memory: 1027K bytes avg, 1044K bytes max (seg63).
  • Work_mem used: 1027K bytes avg, 1044K bytes max (seg63). Workfile: (0 spilling)
  • (seg63) Hash chain length 1.2 avg, 7 max, using 15588 of 131111 buckets.
201. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_ci_coverage c (cost=0.00..263,559.91 rows=122,645 width=15) (actual rows= loops=)

  • Rows out: Avg 122651.4 rows x 108 workers. Max 122795 rows (seg61) with 0.108 ms to first row, 186 ms to end, start offset by 43760 ms.
202. 0.000 0.000 ↓ 0.0

Hash (cost=108,482.49..108,482.49 rows=18,780 width=24) (actual rows= loops=)

  • Rows in: Avg 18773.8 rows x 108 workers. Max 19073 rows (seg63) with 22 ms to end, start offset by 43744 ms.
203. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice25; segments: 108) (cost=0.00..108,482.49 rows=18,780 width=24) (actual rows= loops=)

  • Hash Key: e.coverage_id
  • Rows out: Avg 18773.8 rows x 108 workers at destination. Max 19073 rows (seg63) with 0.091 ms to first row, 14 ms to end, start offset by 43744 ms.
204. 0.000 0.000 ↓ 0.0

Seq Scan on mv_cc_ci_exposure e (cost=0.00..67,919.11 rows=18,780 width=24) (actual rows= loops=)

  • Filter: retired = 0
  • Rows out: Avg 18773.8 rows x 108 workers. Max 18877 rows (seg7) with 0.139 ms to first row, 44 ms to end, start offset by 42397 ms.
205. 0.000 0.000 ↓ 0.0

Hash (cost=17,029,434.56..17,029,434.56 rows=22,325 width=194) (actual rows= loops=)

  • Rows in: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8645 ms to end, start offset by 42482 ms.
206. 0.000 0.000 ↓ 0.0

Subquery Scan e (cost=16,999,295.92..17,029,434.56 rows=22,325 width=194) (actual rows= loops=)

  • Rows out: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8488 ms to first row, 8605 ms to end, start offset by 42482 ms.
207. 0.000 0.000 ↓ 0.0

Window (cost=16,999,295.92..17,029,434.56 rows=22,325 width=194) (actual rows= loops=)

  • Partition By: exposure_reserve.source_system, exposure_reserve.claim_number
  • Order By: exposure_reserve.sum_reserve
  • Rows out: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8487 ms to first row, 8595 ms to end, start offset by 42482 ms.
208. 0.000 0.000 ↓ 0.0

Sort (cost=16,999,295.92..17,005,323.65 rows=22,325 width=194) (actual rows= loops=)

  • Sort Key: exposure_reserve.source_system, exposure_reserve.claim_number, exposure_reserve.sum_reserve
  • Rows out: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8487 ms to first row, 8499 ms to end, start offset by 42482 ms.
  • Executor memory: 9721K bytes avg, 9721K bytes max (seg0).
  • Work_mem used: 9721K bytes avg, 9721K bytes max (seg0). Workfile: (0 spilling)
209. 0.000 0.000 ↓ 0.0

Subquery Scan exposure_reserve (cost=16,683,367.61..16,743,705.16 rows=22,325 width=194) (actual rows= loops=)

  • Filter: claim_exposure_id IS NOT NULL OR count_exposure_ids = 1
  • Rows out: Avg 38672.3 rows x 108 workers. Max 39221 rows (seg39) with 8201 ms to first row, 8365 ms to end, start offset by 42483 ms.
210. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=16,683,367.61..16,743,705.16 rows=22,348 width=194) (actual rows= loops=)

  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 7030 ms to first row, 7301 ms to end, start offset by 42428 ms.
211. 0.000 0.000 ↓ 0.0

Window (cost=16,683,367.61..16,707,502.63 rows=22,348 width=194) (actual rows= loops=)

  • Partition By: coplan.source_system, coplan.claim_number
  • Order By: coplan.claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 7030 ms to first row, 7237 ms to end, start offset by 42428 ms.
212. 0.000 0.000 ↓ 0.0

Sort (cost=16,683,367.61..16,689,401.37 rows=22,348 width=194) (actual rows= loops=)

  • Sort Key: coplan.source_system, coplan.claim_number, coplan.claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 7030 ms to first row, 7040 ms to end, start offset by 42428 ms.
  • Executor memory: 17913K bytes avg, 17913K bytes max (seg0).
  • Work_mem used: 17913K bytes avg, 17913K bytes max (seg0). Workfile: (0 spilling)
213. 0.000 0.000 ↓ 0.0

Subquery Scan coplan (cost=16,379,233.82..16,427,503.86 rows=22,348 width=194) (actual rows= loops=)

  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 6537 ms to first row, 6780 ms to end, start offset by 42428 ms.
214. 0.000 0.000 ↓ 0.0

Window (cost=16,379,233.82..16,403,368.84 rows=22,348 width=194) (actual rows= loops=)

  • Partition By: "Window".source_system, "Window".claim_number
  • Order By: "Window".claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 6537 ms to first row, 6769 ms to end, start offset by 42428 ms.
215. 0.000 0.000 ↓ 0.0

Sort (cost=16,379,233.82..16,385,267.57 rows=22,348 width=194) (actual rows= loops=)

  • Sort Key: "Window".source_system, "Window".claim_number, "Window".claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 6537 ms to first row, 6555 ms to end, start offset by 42428 ms.
  • Executor memory: 17913K bytes avg, 17913K bytes max (seg0).
  • Work_mem used: 17913K bytes avg, 17913K bytes max (seg0). Workfile: (0 spilling)
216. 0.000 0.000 ↓ 0.0

HashAggregate (cost=16,063,032.51..16,099,235.04 rows=22,348 width=194) (actual rows= loops=)

  • Group By: pub.mv_claim_transaction_details_all.source_system, pub.mv_claim_transaction_details_all.claim_number, pub.mv_claim_transaction_details_all.claim_exposure_id
  • Rows out: Avg 63244.7 rows x 108 workers. Max 64082 rows (seg8) with 6242 ms to first row, 6335 ms to end, start offset by 42428 ms.
  • Executor memory: 7590K bytes avg, 7628K bytes max (seg0).
217. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_transaction_details_all (cost=0.00..15,821,682.34 rows=223,473 width=43) (actual rows= loops=)

  • Filter: (source_system = ANY ('{CC_PI,CC_CI}'::text[])) AND (policy_brand::text = ANY ('{NRMA,RACV,SGIC,SGIO}'::text[])) AND (transaction_source = ANY ('{RESERVE,RECOVERY,RECOVERABLE,EXPOSURE,EXPOSURE_HISTORY}'::text[]))
  • Rows out: Avg 331465.7 rows x 108 workers. Max 336080 rows (seg8) with 328 ms to first row, 5527 ms to end, start offset by 42429 ms.
218. 0.000 0.000 ↓ 0.0

Hash (cost=115,183.62..115,183.62 rows=10,180 width=144) (actual rows= loops=)

  • Rows in: Avg 10152.3 rows x 108 workers. Max 10381 rows (seg4) with 885 ms to end, start offset by 42787 ms.
219. 0.000 0.000 ↓ 0.0

Redistribute Motion 108:108 (slice33; segments: 108) (cost=0.00..115,183.62 rows=10,180 width=144) (actual rows= loops=)

  • Hash Key: motor.claim_number::text
  • Rows out: Avg 10153.7 rows x 108 workers at destination. Max 10381 rows (seg4) with 0.072 ms to first row, 879 ms to end, start offset by 42787 ms.
220. 0.000 0.000 ↓ 0.0

Seq Scan on mv_motor_assessing motor (cost=0.00..93,195.25 rows=10,180 width=144) (actual rows= loops=)

  • Filter: claimant_type_name = ANY ('{insured,"Insured''s loss"}'::text[])
  • Rows out: Avg 10153.7 rows x 108 workers. Max 10297 rows (seg105) with 0.174 ms to first row, 58 ms to end, start offset by 42019 ms.
221. 0.000 0.000 ↓ 0.0

Hash (cost=0.85..0.85 rows=1 width=64) (actual rows= loops=)

  • Rows in: Avg 68.0 rows x 108 workers. Max 68 rows (seg0) with 0.083 ms to end, start offset by 42848 ms.
222. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*" (cost=0.00..0.85 rows=1 width=64) (actual rows= loops=)

  • Rows out: Avg 68.0 rows x 108 workers. Max 68 rows (seg0) with 0.012 ms to first row, 0.053 ms to end, start offset by 42848 ms.
223. 0.000 0.000 ↓ 0.0

Hash (cost=208,820.39..208,820.39 rows=76,078 width=14) (actual rows= loops=)

  • Rows in: Avg 75588.7 rows x 108 workers. Max 76291 rows (seg86) with 379 ms to end, start offset by 42437 ms.
224. 0.000 0.000 ↓ 0.0

Seq Scan on mv_claim_details claim_details (cost=0.00..208,820.39 rows=76,078 width=14) (actual rows= loops=)