explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1B85

Settings
# exclusive inclusive rows x rows loops node
1. 2,356.208 681,322.097 ↑ 7.1 124,775 1

GroupAggregate (cost=52,787,851.29..52,805,718.59 rows=880,000 width=36) (actual time=673,442.061..681,322.097 rows=124,775 loops=1)

  • Group Key: sat_insurance_contract_osago."primaryDrivingArea", lnk_insurance_contract_insurance_company.insurance_company_key, sat_insurance_contract_osago."eosagoIndicator", sat_insurance_contract.closing_date
2. 39,818.895 678,965.889 ↓ 3.6 18,445,644 1

Sort (cost=52,787,851.29..52,790,389.18 rows=5,075,765 width=44) (actual time=673,442.017..678,965.889 rows=18,445,644 loops=1)

  • Sort Key: sat_insurance_contract_osago."primaryDrivingArea", lnk_insurance_contract_insurance_company.insurance_company_key, sat_insurance_contract_osago."eosagoIndicator", sat_insurance_contract.closing_date
  • Sort Method: external merge Disk: 1046864kB
3. 17,882.250 639,146.994 ↓ 3.6 18,445,644 1

Nested Loop (cost=28,451,627.70..52,585,563.65 rows=5,075,765 width=44) (actual time=207,922.848..639,146.994 rows=18,445,644 loops=1)

4. 196,365.527 492,145.236 ↓ 3.6 18,445,644 1

Hash Join (cost=28,451,627.59..45,519,595.16 rows=5,075,765 width=72) (actual time=207,922.806..492,145.236 rows=18,445,644 loops=1)

  • Hash Cond: (lnk_insurance_contract_insurance_company.insurance_contract_key = hub_insurance_contract.insurance_contract_key)
5. 87,865.795 87,865.795 ↑ 1.0 503,241,930 1

Seq Scan on lnk_insurance_contract_insurance_company (cost=0.00..9,252,048.44 rows=503,248,480 width=32) (actual time=0.030..87,865.795 rows=503,241,930 loops=1)

6. 5,581.623 207,913.914 ↓ 3.6 18,445,644 1

Hash (cost=28,394,207.28..28,394,207.28 rows=5,075,802 width=40) (actual time=207,913.914..207,913.914 rows=18,445,644 loops=1)

  • Buckets: 4194304 (originally 4194304) Batches: 8 (originally 2) Memory Usage: 229377kB
7. 1,732.988 202,332.291 ↓ 3.6 18,445,644 1

Nested Loop (cost=20,606,871.98..28,394,207.28 rows=5,075,802 width=40) (actual time=104,824.979..202,332.291 rows=18,445,644 loops=1)

8. 2,949.152 126,816.727 ↓ 3.6 18,445,644 1

Unique (cost=20,606,871.86..20,625,149.44 rows=5,075,802 width=69) (actual time=104,824.910..126,816.727 rows=18,445,644 loops=1)

9. 71,653.588 123,867.575 ↓ 1.1 19,450,774 1

Sort (cost=20,606,871.86..20,616,010.65 rows=18,277,575 width=69) (actual time=104,824.901..123,867.575 rows=19,450,774 loops=1)

  • 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: 1560696kB
10. 0.000 52,213.987 ↓ 1.1 19,450,774 1

Gather (cost=1,000.00..19,737,569.48 rows=18,277,575 width=69) (actual time=0.588..52,213.987 rows=19,450,774 loops=1)

  • Workers Planned: 9
  • Workers Launched: 9
11. 52,741.636 52,741.636 ↑ 1.0 1,945,077 10 / 10

Parallel Seq Scan on sat_insurance_contract (cost=0.00..17,908,811.98 rows=2,030,842 width=69) (actual time=0.474..52,741.636 rows=1,945,077 loops=10)

  • Filter: (((status)::text <> ALL ('{1,3,4}'::text[])) AND (closing_date <= to_date('31.12.2019'::text, 'DD.MM.YYYY'::text)) AND (closing_date >= (to_date('31.12.2019'::text, 'DD.MM.YYYY'::text) - 30)))
  • Rows Removed by Filter: 48379116
12. 73,782.576 73,782.576 ↑ 1.0 1 18,445,644

Index Only Scan using pk_hub_insurance_contract_key on hub_insurance_contract (cost=0.11..1.53 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=18,445,644)

  • Index Cond: (insurance_contract_key = sat_insurance_contract.insurance_contract_key)
  • Heap Fetches: 18445644
13. 129,119.508 129,119.508 ↑ 1.0 1 18,445,644

Index Scan using pk_sat_insurance_contract_osago on sat_insurance_contract_osago (cost=0.11..1.39 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=18,445,644)

  • Index Cond: (insurance_contract_key = hub_insurance_contract.insurance_contract_key)
Planning time : 3.043 ms
Execution time : 681,425.439 ms