explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 207.869 2,822.545 ↑ 15,352,078,032,784,266.0 14 1

Hash Left Join (cost=180,009.21..566,375,108,852,543,936.00 rows=214,929,092,458,979,712 width=32) (actual time=2,628.257..2,822.545 rows=14 loops=1)

  • Hash Cond: (CASE WHEN ((temp_rr_contracts.sg_con_plc)::text = ANY ('{DUVS,DUTW,DUTP,DUGP,DUPL,QMGP,QMPL,QMVS}'::text[])) THEN ("left"((temp_rr_contracts.sg_con_vin)::text, 8) || "substring"((temp_rr_contracts.sg_con_vin)::text, 10, 1)) ELSE ("left"((temp_rr_contracts.sg_con_vin)::text, 8) || "substring"((temp_rr_contracts.sg_con_vin)::text, 10, 2)) END = (vd.vin_pattern)::text)
2. 0.026 2,329.239 ↑ 33,069,626,445,193.3 14 1

Nested Loop (cost=162,543.10..35,104,400,578,418.23 rows=462,974,770,232,706 width=204) (actual time=2,327.113..2,329.239 rows=14 loops=1)

  • Join Filter: (temp_rr_contracts.sg_con_saledate <= earnquarter.earnquarter)
3. 46.258 2,329.157 ↑ 106,840,331,592,163.0 1 1

Hash Left Join (cost=162,542.96..14,270,535,917,943.81 rows=106,840,331,592,163 width=200) (actual time=2,327.093..2,329.157 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)
4. 0.187 1,507.304 ↑ 106,605,668,489,489.0 1 1

Hash Left Join (cost=12,374.96..2,816,408,336,305.57 rows=106,605,668,489,489 width=284) (actual time=1,507.283..1,507.304 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))
5. 446.028 1,478.621 ↑ 106,605,668,489,489.0 1 1

Merge Right Join (cost=8,664.16..2,016,465,797,653.47 rows=106,605,668,489,489 width=328) (actual time=1,478.601..1,478.621 rows=1 loops=1)

  • Merge Cond: (bi_jd_pmt_m1.sg_pmt_contract = temp_rr_contracts.sg_con_contract)
6. 853.154 853.154 ↑ 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.019..853.154 rows=5,668,659 loops=1)

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

Materialize (cost=8,663.73..486,908,850,341.99 rows=27,811,305,362,230 width=328) (actual time=179.420..179.439 rows=1 loops=1)

8. 9.893 179.436 ↑ 27,811,305,362,230.0 1 1

Merge Left Join (cost=8,663.73..417,380,586,936.41 rows=27,811,305,362,230 width=328) (actual time=179.418..179.436 rows=1 loops=1)

  • Merge Cond: (temp_rr_contracts.sg_con_contract = temp_con_d7.sg_con7_contract)
9. 19.290 154.735 ↑ 12,039,083,134.0 1 1

Merge Left Join (cost=8,663.31..180,896,175.81 rows=12,039,083,134 width=320) (actual time=154.725..154.735 rows=1 loops=1)

  • Merge Cond: (temp_rr_contracts.sg_con_contract = temp_mc_bundle.sg_splitcon_new)
10. 16.953 99.899 ↑ 15,515,682.0 1 1

Merge Join (cost=8,662.89..266,050.38 rows=15,515,682 width=276) (actual time=99.890..99.899 rows=1 loops=1)

  • Merge Cond: (temp_bi_vci_rr_contract_amounts.contract = temp_rr_contracts.sg_con_contract)
11. 54.001 54.001 ↑ 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.035..54.001 rows=170,424 loops=1)

  • Heap Fetches: 170,424
12. 0.010 28.945 ↑ 3,939.0 1 1

Sort (cost=8,662.46..8,672.31 rows=3,939 width=276) (actual time=28.944..28.945 rows=1 loops=1)

  • Sort Key: temp_rr_contracts.sg_con_contract
  • Sort Method: quicksort Memory: 25kB
13. 0.175 28.935 ↑ 3,939.0 1 1

Hash Join (cost=4,318.78..8,427.23 rows=3,939 width=276) (actual time=28.932..28.935 rows=1 loops=1)

  • Hash Cond: (temp_rr_contracts.sg_con_dealer = sg_dlr_m1.sg_dlr_dealer)
14. 0.004 0.030 ↑ 3,939.0 1 1

Bitmap Heap Scan on temp_rr_contracts (cost=48.55..4,146.67 rows=3,939 width=302) (actual time=0.028..0.030 rows=1 loops=1)

  • Recheck Cond: (rr_contract = '6000027068'::bpchar)
  • Heap Blocks: exact=1
15. 0.026 0.026 ↑ 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.026..0.026 rows=1 loops=1)

  • Index Cond: (rr_contract = '6000027068'::bpchar)
16. 13.968 28.730 ↓ 1.0 80,463 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,032kB
17. 14.762 14.762 ↓ 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.007..14.762 rows=80,463 loops=1)

18. 10.814 35.546 ↑ 1.0 155,186 1

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

19. 24.732 24.732 ↑ 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.039..24.732 rows=155,186 loops=1)

20. 5.156 14.808 ↑ 6.2 74,857 1

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

21. 9.652 9.652 ↑ 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.025..9.652 rows=74,857 loops=1)

22. 16.192 28.496 ↑ 1.0 114,632 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,313kB
23. 12.304 12.304 ↑ 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.011..12.304 rows=114,632 loops=1)

24. 200.922 775.595 ↓ 1.0 1,162,846 1

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

  • Buckets: 1,048,576 Batches: 4 Memory Usage: 21,740kB
25. 574.673 574.673 ↓ 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.010..574.673 rows=1,163,058 loops=1)

26. 0.017 0.056 ↓ 1.1 14 1

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

27. 0.039 0.039 ↓ 1.1 14 1

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

  • Index Cond: ((earnquarter >= '2017-03-31'::date) AND (earnquarter <= '2020-06-30'::date))
  • Heap Fetches: 14
28. 19.538 285.437 ↓ 1.3 123,760 1

Hash (cost=15,487.02..15,487.02 rows=92,847 width=14) (actual time=285.437..285.437 rows=123,760 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,691kB
29. 10.766 265.899 ↓ 1.3 123,760 1

Subquery Scan on vd (cost=13,630.08..15,487.02 rows=92,847 width=14) (actual time=224.671..265.899 rows=123,760 loops=1)

30. 82.991 255.133 ↓ 1.3 123,760 1

HashAggregate (cost=13,630.08..14,558.55 rows=92,847 width=110) (actual time=224.669..255.133 rows=123,760 loops=1)

  • Group Key: vin_details.vin_pattern, max(vin_details.basic_warranty_term), max((vin_details.make)::text), max((vin_details.model)::text), max((vin_details."trim")::text)
31. 157.781 172.142 ↓ 1.3 123,760 1

HashAggregate (cost=11,541.02..12,469.49 rows=92,847 width=110) (actual time=114.626..172.142 rows=123,760 loops=1)

  • Group Key: vin_details.vin_pattern
32. 14.361 14.361 ↓ 1.1 175,359 1

Seq Scan on vin_details (cost=0.00..9,471.01 rows=165,601 width=35) (actual time=0.006..14.361 rows=175,359 loops=1)

33.          

SubPlan (for Hash Left Join)

34. 0.000 0.000 ↓ 0.0 0

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

35. 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
36. 0.000 0.000 ↓ 0.0 0

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

37. 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
38. 0.000 0.000 ↓ 0.0 0

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

39. 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.266 ms
Execution time : 2,824.047 ms