explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bTvX : egar_90d

Settings
# exclusive inclusive rows x rows loops node
1. 28,972.220 2,534,439.722 ↑ 2.4 374,315 1

HashAggregate (cost=67,343,774.74..67,346,414.74 rows=880,000 width=36) (actual time=2,534,349.222..2,534,439.722 rows=374,315 loops=1)

  • Output: lnk_insurance_contract_insurance_company.insurance_company_key, sat_insurance_contract_osago."eosagoIndicator", sat_insurance_contract_osago."primaryDrivingArea", count(hub_insurance_contract.insurance_contract_key), sat_insurance_contract_max.closing_date
  • Group Key: sat_insurance_contract_osago."primaryDrivingArea", lnk_insurance_contract_insurance_company.insurance_company_key, sat_insurance_contract_osago."eosagoIndicator", sat_insurance_contract_max.closing_date
  • Buffers: shared hit=551149061 read=26364588, temp read=4070035 written=4070110
  • JIT:
  • Functions: 79
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 15.786 ms, Inlining 478.696 ms, Optimization 1618.594 ms, Emission 934.021 ms, Total 3047.097 ms
2. 3,697.816 2,505,467.502 ↓ 10.6 55,178,861 1

Nested Loop (cost=48,269,543.37..67,278,478.96 rows=5,223,662 width=44) (actual time=423,061.074..2,505,467.502 rows=55,178,861 loops=1)

  • Output: lnk_insurance_contract_insurance_company.insurance_company_key, sat_insurance_contract_osago."eosagoIndicator", sat_insurance_contract_osago."primaryDrivingArea", sat_insurance_contract_max.closing_date, hub_insurance_contract.insurance_contract_key
  • Inner Unique: true
  • Buffers: shared hit=551149061 read=26364588, temp read=4070035 written=4070110
3. 3,446.358 2,115,517.659 ↓ 10.6 55,178,861 1

Nested Loop (cost=48,269,542.80..61,316,238.83 rows=5,223,662 width=72) (actual time=423,061.036..2,115,517.659 rows=55,178,861 loops=1)

  • Output: sat_insurance_contract_max.closing_date, sat_insurance_contract_max.insurance_contract_key, hub_insurance_contract.insurance_contract_key, lnk_insurance_contract_insurance_company.insurance_company_key, lnk_insurance_contract_insurance_company.insurance_contract_key
  • Inner Unique: true
  • Join Filter: (sat_insurance_contract_max.insurance_contract_key = hub_insurance_contract.insurance_contract_key)
  • Buffers: shared hit=276956600 read=24450554, temp read=4070035 written=4070110
4. 97,942.828 622,242.054 ↓ 10.6 55,178,861 1

Hash Join (cost=48,269,542.22..55,380,481.74 rows=5,223,700 width=56) (actual time=423,059.778..622,242.054 rows=55,178,861 loops=1)

  • Output: sat_insurance_contract_max.closing_date, sat_insurance_contract_max.insurance_contract_key, lnk_insurance_contract_insurance_company.insurance_company_key, lnk_insurance_contract_insurance_company.insurance_contract_key
  • Hash Cond: (sat_insurance_contract_max.insurance_contract_key = lnk_insurance_contract_insurance_company.insurance_contract_key)
  • Buffers: shared hit=6496878 read=18803781, temp read=4070035 written=4070110
5. 5,465.484 336,408.321 ↓ 10.6 55,178,861 1

Subquery Scan on sat_insurance_contract_max (cost=32,809,451.14..33,105,866.93 rows=5,223,700 width=24) (actual time=235,145.565..336,408.321 rows=55,178,861 loops=1)

  • Output: sat_insurance_contract_max.closing_date, sat_insurance_contract_max.insurance_contract_key
  • Buffers: shared hit=6496878 read=11061478, temp read=585570 written=585645
6. 7,230.019 330,942.837 ↓ 10.6 55,178,861 1

Unique (cost=32,809,451.14..33,090,195.83 rows=5,223,700 width=69) (actual time=235,145.523..330,942.837 rows=55,178,861 loops=1)

  • Output: sat_insurance_contract.insurance_contract_key, sat_insurance_contract.closing_date, sat_insurance_contract.contract_id, (GREATEST(sat_insurance_contract.effective_date, sat_insurance_contract.revision_date))
  • Buffers: shared hit=6496878 read=11061478, temp read=585570 written=585645
7. 234,351.590 323,712.818 ↓ 1.0 58,378,474 1

Sort (cost=32,809,451.14..32,949,823.49 rows=56,148,938 width=69) (actual time=235,145.517..323,712.818 rows=58,378,474 loops=1)

  • Output: sat_insurance_contract.insurance_contract_key, sat_insurance_contract.closing_date, sat_insurance_contract.contract_id, (GREATEST(sat_insurance_contract.effective_date, sat_insurance_contract.revision_date))
  • Sort Key: sat_insurance_contract.contract_id, (GREATEST(sat_insurance_contract.effective_date, sat_insurance_contract.revision_date)) DESC NULLS LAST
  • Sort Method: external merge Disk: 4684560kB
  • Buffers: shared hit=6496878 read=11061478, temp read=585570 written=585645
8. 0.000 89,361.228 ↓ 1.0 58,378,474 1

Gather (cost=1,000.00..24,266,317.26 rows=56,148,938 width=69) (actual time=1.388..89,361.228 rows=58,378,474 loops=1)

  • Output: sat_insurance_contract.insurance_contract_key, sat_insurance_contract.closing_date, sat_insurance_contract.contract_id, (GREATEST(sat_insurance_contract.effective_date, sat_insurance_contract.revision_date))
  • Workers Planned: 9
  • Workers Launched: 9
  • JIT for worker 0:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.214 ms, Inlining 47.413 ms, Optimization 137.261 ms, Emission 83.220 ms, Total 269.107 ms
  • JIT for worker 1:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.214 ms, Inlining 46.745 ms, Optimization 138.051 ms, Emission 79.449 ms, Total 265.458 ms
  • JIT for worker 2:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.207 ms, Inlining 47.804 ms, Optimization 137.189 ms, Emission 78.550 ms, Total 264.750 ms
  • JIT for worker 3:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.212 ms, Inlining 47.804 ms, Optimization 138.164 ms, Emission 79.061 ms, Total 266.240 ms
  • JIT for worker 4:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.214 ms, Inlining 47.971 ms, Optimization 137.694 ms, Emission 79.014 ms, Total 265.892 ms
  • JIT for worker 5:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.208 ms, Inlining 47.976 ms, Optimization 137.264 ms, Emission 78.994 ms, Total 265.442 ms
  • JIT for worker 6:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.214 ms, Inlining 47.096 ms, Optimization 136.886 ms, Emission 79.123 ms, Total 264.319 ms
  • JIT for worker 7:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.206 ms, Inlining 47.912 ms, Optimization 137.477 ms, Emission 78.763 ms, Total 265.358 ms
  • JIT for worker 8:
  • Functions: 4
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 1.218 ms, Inlining 47.973 ms, Optimization 137.033 ms, Emission 80.566 ms, Total 266.790 ms
  • Buffers: shared hit=6496872 read=11061478
9. 90,784.761 90,784.761 ↑ 1.1 5,837,847 10 / 10

Parallel Seq Scan on public.sat_insurance_contract (cost=0.00..18,650,423.46 rows=6,238,771 width=69) (actual time=258.933..90,784.761 rows=5,837,847 loops=10)

  • Output: sat_insurance_contract.insurance_contract_key, sat_insurance_contract.closing_date, sat_insurance_contract.contract_id, GREATEST(sat_insurance_contract.effective_date, sat_insurance_contract.revision_date)
  • Filter: (((sat_insurance_contract.status)::text <> ALL ('{1,3,4}'::text[])) AND (sat_insurance_contract.closing_date <= to_date('31.12.2019'::text, 'DD.MM.YYYY'::text)) AND (sat_insurance_contract.closing_date >= (to_date('31.12.2019'::text, 'DD.MM.YYYY'::text) - 90)))
  • Rows Removed by Filter: 44486346
  • Buffers: shared hit=6496872 read=11061478
  • Worker 0: actual time=306.137..91824.905 rows=5942093 loops=1
  • Buffers: shared hit=661439 read=1114268
  • Worker 1: actual time=264.566..91965.898 rows=5990828 loops=1
  • Buffers: shared hit=666359 read=1125628
  • Worker 2: actual time=263.893..92788.772 rows=5990092 loops=1
  • Buffers: shared hit=667016 read=1129278
  • Worker 3: actual time=306.124..92654.932 rows=6030602 loops=1
  • Buffers: shared hit=671170 read=1121008
  • Worker 4: actual time=265.030..92054.278 rows=5993357 loops=1
  • Buffers: shared hit=666694 read=1127902
  • Worker 5: actual time=306.119..91850.926 rows=5949592 loops=1
  • Buffers: shared hit=662357 read=1102555
  • Worker 6: actual time=306.127..91964.449 rows=5980136 loops=1
  • Buffers: shared hit=664657 read=1120771
  • Worker 7: actual time=264.506..92899.020 rows=6049438 loops=1
  • Buffers: shared hit=673504 read=1114107
  • Worker 8: actual time=306.129..92787.014 rows=6065294 loops=1
  • Buffers: shared hit=674940 read=1134669
10. 86,316.761 187,890.905 ↑ 1.0 503,241,930 1

Hash (cost=9,252,048.44..9,252,048.44 rows=503,248,480 width=32) (actual time=187,890.905..187,890.905 rows=503,241,930 loops=1)

  • Output: lnk_insurance_contract_insurance_company.insurance_company_key, lnk_insurance_contract_insurance_company.insurance_contract_key
  • Buckets: 2097152 Batches: 512 Memory Usage: 77918kB
  • Buffers: shared read=7742303, temp written=3187907
11. 101,574.144 101,574.144 ↑ 1.0 503,241,930 1

Seq Scan on public.lnk_insurance_contract_insurance_company (cost=0.00..9,252,048.44 rows=503,248,480 width=32) (actual time=650.012..101,574.144 rows=503,241,930 loops=1)

  • Output: lnk_insurance_contract_insurance_company.insurance_company_key, lnk_insurance_contract_insurance_company.insurance_contract_key
  • Buffers: shared read=7742303
12. 1,489,829.247 1,489,829.247 ↑ 1.0 1 55,178,861

Index Only Scan using pk_hub_insurance_contract_key on public.hub_insurance_contract (cost=0.57..1.13 rows=1 width=16) (actual time=0.027..0.027 rows=1 loops=55,178,861)

  • Output: hub_insurance_contract.insurance_contract_key
  • Index Cond: (hub_insurance_contract.insurance_contract_key = lnk_insurance_contract_insurance_company.insurance_contract_key)
  • Heap Fetches: 55178861
  • Buffers: shared hit=270459722 read=5646773
13. 386,252.027 386,252.027 ↑ 1.0 1 55,178,861

Index Scan using pk_sat_insurance_contract_osago on public.sat_insurance_contract_osago (cost=0.57..1.14 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=55,178,861)

  • Output: sat_insurance_contract_osago.insurance_contract_key, sat_insurance_contract_osago.dv_effective_date, sat_insurance_contract_osago.dv_load_date, sat_insurance_contract_osago.dv_change_id, sat_insurance_contract_osago.dv_source_system, sat_insurance_contract_osago."closingMethodCode", sat_insurance_contract_osago."addendumType", sat_insurance_contract_osago."eosagoIndicator", sat_insurance_contract_osago."readyToSignIndicator", sat_insurance_contract_osago."utilisationPeriod1Start", sat_insurance_contract_osago."utilisationPeriod1End", sat_insurance_contract_osago."utilisationPeriod2Start", sat_insurance_contract_osago."utilisationPeriod2End", sat_insurance_contract_osago."utilisationPeriod3Start", sat_insurance_contract_osago."utilisationPeriod3End", sat_insurance_contract_osago."driverLimitIndicator", sat_insurance_contract_osago."registrationTransitIndicator", sat_insurance_contract_osago.closing_from_draft_indicator, sat_insurance_contract_osago."premiumAndDelta", sat_insurance_contract_osago."premiumPaymentRecallDate", sat_insurance_contract_osago."issueDate", sat_insurance_contract_osago."primaryDrivingArea", sat_insurance_contract_osago."trailerIndicator", sat_insurance_contract_osago."vehicleUtilisationIntent", sat_insurance_contract_osago."baseTariff", sat_insurance_contract_osago.kt, sat_insurance_contract_osago.kvs, sat_insurance_contract_osago.ko, sat_insurance_contract_osago.km, sat_insurance_contract_osago.ks, sat_insurance_contract_osago.kn, sat_insurance_contract_osago.kp, sat_insurance_contract_osago.kpr, sat_insurance_contract_osago.kbm_number_provided, sat_insurance_contract_osago.kbm_number_calculated, sat_insurance_contract_osago."kbm_provided_Id", sat_insurance_contract_osago."kbm_calculation_Id", sat_insurance_contract_osago."baseTariffDateTime", sat_insurance_contract_osago."primaryDrivingAreaRegionCode", sat_insurance_contract_osago.protection_currency_code, sat_insurance_contract_osago.risk_type_key, sat_insurance_contract_osago.base_tariff_key, sat_insurance_contract_osago.kt_key, sat_insurance_contract_osago.km_key, sat_insurance_contract_osago.kvs_rf_key, sat_insurance_contract_osago.kvs_no_rf_key, sat_insurance_contract_osago.kn_key, sat_insurance_contract_osago.ko_key, sat_insurance_contract_osago.ks_key, sat_insurance_contract_osago.kp_key, sat_insurance_contract_osago."Kpr_key", sat_insurance_contract_osago.excluded_from_kbm_indicator, sat_insurance_contract_osago.ready_to_sign_date
  • Index Cond: (sat_insurance_contract_osago.insurance_contract_key = hub_insurance_contract.insurance_contract_key)
  • Buffers: shared hit=274192461 read=1914034
Planning time : 9.993 ms
Execution time : 2,534,876.569 ms