explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xttk : Optimization for: Optimization for: Optimization for: plan #iVdh; plan #c8yU; plan #fbAJ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 224.903 2,857.646 ↑ 20,463,484,844,285,552.0 14 1

Merge Right Join (cost=267,746,605,525,650.91..759,464,191,765,444,992.00 rows=286,488,787,819,997,728 width=32) (actual time=2,664.579..2,857.646 rows=14 loops=1)

  • Merge Cond: ((vd.vin_pattern)::text = temp_rr_contracts.plc_vin_pattern)
2. 279.622 295.118 ↑ 1.0 118,520 1

Sort (cost=12,786.95..13,096.35 rows=123,760 width=42) (actual time=280.018..295.118 rows=118,520 loops=1)

  • Sort Key: vd.vin_pattern
  • Sort Method: quicksort Memory: 8,874kB
3. 15.496 15.496 ↑ 1.0 123,760 1

Seq Scan on bi_vin_dtl vd (cost=0.00..2,318.60 rows=123,760 width=42) (actual time=0.029..15.496 rows=123,760 loops=1)

4. 0.018 2,337.625 ↑ 33,069,626,445,193.3 14 1

Materialize (cost=267,746,605,512,045.44..270,061,479,363,208.94 rows=462,974,770,232,706 width=184) (actual time=2,337.604..2,337.625 rows=14 loops=1)

5. 0.028 2,337.607 ↑ 33,069,626,445,193.3 14 1

Sort (cost=267,746,605,512,045.44..268,904,042,437,627.19 rows=462,974,770,232,706 width=184) (actual time=2,337.599..2,337.607 rows=14 loops=1)

  • Sort Key: temp_rr_contracts.plc_vin_pattern
  • Sort Method: quicksort Memory: 26kB
6. 0.008 2,337.579 ↑ 33,069,626,445,193.3 14 1

Nested Loop (cost=162,551.67..34,479,757,989,620.80 rows=462,974,770,232,706 width=184) (actual time=2,335.959..2,337.579 rows=14 loops=1)

  • Join Filter: (temp_rr_contracts.sg_con_saledate <= earnquarter.earnquarter)
7. 46.838 2,337.548 ↑ 106,840,331,592,163.0 1 1

Hash Left Join (cost=162,551.52..13,645,893,329,146.38 rows=106,840,331,592,163 width=180) (actual time=2,335.935..2,337.548 rows=1 loops=1)

  • Hash Cond: (COALESCE(temp_mc_bundle.sg_splitcon_old, temp_rr_contracts.sg_con_contract) = (ng_stg_contract.contract_number)::bpchar)
8. 0.191 1,510.354 ↑ 106,605,668,489,489.0 1 1

Hash Left Join (cost=12,383.53..2,816,408,336,314.14 rows=106,605,668,489,489 width=264) (actual time=1,510.335..1,510.354 rows=1 loops=1)

  • Hash Cond: (((temp_rr_contracts.sg_con_ncic)::text = (bi_rm_modelclass.vehmake)::text) AND ((temp_rr_contracts.sg_con_series)::text = (bi_rm_modelclass.vehmodel)::text))
9. 451.079 1,482.184 ↑ 106,605,668,489,489.0 1 1

Merge Right Join (cost=8,672.73..2,016,465,797,662.04 rows=106,605,668,489,489 width=308) (actual time=1,482.166..1,482.184 rows=1 loops=1)

  • Merge Cond: (bi_jd_pmt_m1.sg_pmt_contract = temp_rr_contracts.sg_con_contract)
10. 849.676 849.676 ↑ 1.0 5,668,659 1

Index Only Scan using bi_jd_pmt_m1_idx on bi_jd_pmt_m1 (cost=0.43..168,918.10 rows=5,782,651 width=12) (actual time=0.020..849.676 rows=5,668,659 loops=1)

  • Heap Fetches: 5,668,659
11. 0.003 181.429 ↑ 27,811,305,362,230.0 1 1

Materialize (cost=8,672.30..486,908,850,350.55 rows=27,811,305,362,230 width=308) (actual time=181.413..181.429 rows=1 loops=1)

12. 10.086 181.426 ↑ 27,811,305,362,230.0 1 1

Merge Left Join (cost=8,672.30..417,380,586,944.98 rows=27,811,305,362,230 width=308) (actual time=181.410..181.426 rows=1 loops=1)

  • Merge Cond: (temp_rr_contracts.sg_con_contract = temp_con_d7.sg_con7_contract)
13. 19.758 155.278 ↑ 12,039,083,134.0 1 1

Merge Left Join (cost=8,671.88..180,896,184.38 rows=12,039,083,134 width=300) (actual time=155.270..155.278 rows=1 loops=1)

  • Merge Cond: (temp_rr_contracts.sg_con_contract = temp_mc_bundle.sg_splitcon_new)
14. 17.241 99.158 ↑ 15,515,682.0 1 1

Merge Join (cost=8,671.46..266,058.95 rows=15,515,682 width=256) (actual time=99.150..99.158 rows=1 loops=1)

  • Merge Cond: (temp_bi_vci_rr_contract_amounts.contract = temp_rr_contracts.sg_con_contract)
15. 53.476 53.476 ↑ 4.6 170,424 1

Index Only Scan using bi_temp_vci_rr_contract_amounts_contract_idx on temp_bi_vci_rr_contract_amounts (cost=0.42..22,683.19 rows=787,798 width=44) (actual time=0.038..53.476 rows=170,424 loops=1)

  • Heap Fetches: 170,424
16. 0.011 28.441 ↑ 3,939.0 1 1

Sort (cost=8,671.03..8,680.88 rows=3,939 width=256) (actual time=28.440..28.441 rows=1 loops=1)

  • Sort Key: temp_rr_contracts.sg_con_contract
  • Sort Method: quicksort Memory: 25kB
17. 0.180 28.430 ↑ 3,939.0 1 1

Hash Join (cost=4,318.78..8,435.80 rows=3,939 width=256) (actual time=28.428..28.430 rows=1 loops=1)

  • Hash Cond: (temp_rr_contracts.sg_con_dealer = sg_dlr_m1.sg_dlr_dealer)
18. 0.006 0.035 ↑ 3,939.0 1 1

Bitmap Heap Scan on temp_rr_contracts (cost=48.55..4,155.24 rows=3,939 width=282) (actual time=0.034..0.035 rows=1 loops=1)

  • Recheck Cond: (rr_contract = '6000027068'::bpchar)
  • Heap Blocks: exact=1
19. 0.029 0.029 ↑ 3,939.0 1 1

Bitmap Index Scan on temp_rr_contracts_rr_contract_idx (cost=0.00..47.57 rows=3,939 width=0) (actual time=0.029..0.029 rows=1 loops=1)

  • Index Cond: (rr_contract = '6000027068'::bpchar)
20. 13.235 28.215 ↓ 1.0 80,463 1

Hash (cost=3,265.10..3,265.10 rows=80,410 width=19) (actual time=28.215..28.215 rows=80,463 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,032kB
21. 14.980 14.980 ↓ 1.0 80,463 1

Seq Scan on sg_dlr_m1 (cost=0.00..3,265.10 rows=80,410 width=19) (actual time=0.010..14.980 rows=80,463 loops=1)

22. 11.493 36.362 ↑ 1.0 155,186 1

Materialize (cost=0.42..5,477.18 rows=155,186 width=88) (actual time=0.030..36.362 rows=155,186 loops=1)

23. 24.869 24.869 ↑ 1.0 155,186 1

Index Scan using temp_mc_bundle_sg_splitcon_new_idx on temp_mc_bundle (cost=0.42..5,089.21 rows=155,186 width=88) (actual time=0.027..24.869 rows=155,186 loops=1)

24. 6.500 16.062 ↑ 6.2 74,857 1

Materialize (cost=0.42..13,783.72 rows=462,017 width=52) (actual time=0.029..16.062 rows=74,857 loops=1)

25. 9.562 9.562 ↑ 6.2 74,857 1

Index Scan using temp_con_d7_sg_con7_contract_idx on temp_con_d7 (cost=0.42..12,628.68 rows=462,017 width=52) (actual time=0.027..9.562 rows=74,857 loops=1)

26. 15.875 27.979 ↑ 1.0 114,632 1

Hash (cost=1,991.32..1,991.32 rows=114,632 width=14) (actual time=27.979..27.979 rows=114,632 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,313kB
27. 12.104 12.104 ↑ 1.0 114,632 1

Seq Scan on bi_rm_modelclass (cost=0.00..1,991.32 rows=114,632 width=14) (actual time=0.014..12.104 rows=114,632 loops=1)

28. 201.216 780.356 ↓ 1.0 1,162,846 1

Hash (cost=129,973.33..129,973.33 rows=1,161,733 width=15) (actual time=780.356..780.356 rows=1,162,846 loops=1)

  • Buckets: 1,048,576 Batches: 4 Memory Usage: 21,740kB
29. 579.140 579.140 ↓ 1.0 1,163,058 1

Seq Scan on ng_stg_contract (cost=0.00..129,973.33 rows=1,161,733 width=15) (actual time=0.012..579.140 rows=1,163,058 loops=1)

30. 0.007 0.023 ↓ 1.1 14 1

Materialize (cost=0.14..2.67 rows=13 width=4) (actual time=0.019..0.023 rows=14 loops=1)

31. 0.016 0.016 ↓ 1.1 14 1

Index Only Scan using earnquarter_idx on earnquarter (cost=0.14..2.60 rows=13 width=4) (actual time=0.014..0.016 rows=14 loops=1)

  • Index Cond: ((earnquarter >= '2017-03-31'::date) AND (earnquarter <= '2020-06-30'::date))
  • Heap Fetches: 14
32.          

SubPlan (for Merge Right Join)

33. 0.000 0.000 ↓ 0.0 0

Unique (cost=0.28..301.29 rows=725 width=9) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sg_cov_d3_pkey on sg_cov_d3 (cost=0.28..299.36 rows=771 width=9) (never executed)

  • Index Cond: (sg_cov3_rstype = 'T'::bpchar)
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0

Unique (cost=0.28..255.62 rows=391 width=9) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sg_cov_d3_pkey on sg_cov_d3 sg_cov_d3_1 (cost=0.28..254.61 rows=404 width=9) (never executed)

  • Index Cond: (sg_cov3_rstype = 'W'::bpchar)
  • Heap Fetches: 0
37. 0.000 0.000 ↓ 0.0 0

Unique (cost=0.28..257.81 rows=397 width=9) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sg_cov_d3_pkey on sg_cov_d3 sg_cov_d3_2 (cost=0.28..256.78 rows=410 width=9) (never executed)

  • Index Cond: (sg_cov3_rstype = 'D'::bpchar)
  • Heap Fetches: 0
Planning time : 1.443 ms
Execution time : 2,858.048 ms