explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c8yU : Optimization for: plan #iVdh

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 212.769 5,632.510 ↑ 5,333,292,191,952,819.0 14 1

Hash Left Join (cost=214,303.80..196,757,985,421,478,784.00 rows=74,666,090,687,339,472 width=32) (actual time=5,434.044..5,632.510 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.022 5,128.768 ↑ 11,488,345,755,819.5 14 1

Nested Loop (cost=196,837.69..12,195,223,943,209.26 rows=160,836,840,581,473 width=204) (actual time=5,126.678..5,128.768 rows=14 loops=1)

  • Join Filter: (temp_rr_contracts.sg_con_saledate <= earnquarter.earnquarter)
3. 49.238 5,128.689 ↑ 37,116,193,980,340.0 1 1

Hash Left Join (cost=196,837.55..4,957,566,117,040.32 rows=37,116,193,980,340 width=200) (actual time=5,126.657..5,128.689 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.179 4,298.516 ↑ 37,034,672,319,848.0 1 1

Hash Left Join (cost=46,669.56..978,416,803,985.17 rows=37,034,672,319,848 width=284) (actual time=4,298.499..4,298.516 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. 452.452 4,269.450 ↑ 37,034,672,319,848.0 1 1

Merge Right Join (cost=42,958.76..700,517,791,000.01 rows=37,034,672,319,848 width=328) (actual time=4,269.434..4,269.450 rows=1 loops=1)

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

  • Heap Fetches: 5,668,659
7. 0.005 2,947.771 ↑ 9,661,611,764,847.0 1 1

Materialize (cost=42,958.32..169,151,552,239.68 rows=9,661,611,764,847 width=328) (actual time=2,947.756..2,947.771 rows=1 loops=1)

8. 10.068 2,947.766 ↑ 9,661,611,764,847.0 1 1

Merge Left Join (cost=42,958.32..144,997,522,827.57 rows=9,661,611,764,847 width=328) (actual time=2,947.753..2,947.766 rows=1 loops=1)

  • Merge Cond: (temp_rr_contracts.sg_con_contract = temp_con_d7.sg_con7_contract)
9. 19.719 2,922.510 ↑ 4,182,362,019.0 1 1

Merge Left Join (cost=42,957.90..62,877,823.91 rows=4,182,362,019 width=320) (actual time=2,922.504..2,922.510 rows=1 loops=1)

  • Merge Cond: (temp_rr_contracts.sg_con_contract = temp_mc_bundle.sg_splitcon_new)
10. 18.234 2,863.083 ↑ 5,390,128.0 1 1

Merge Join (cost=42,957.48..123,829.10 rows=5,390,128 width=276) (actual time=2,863.079..2,863.083 rows=1 loops=1)

  • Merge Cond: (temp_rr_contracts.sg_con_contract = temp_bi_vci_rr_contract_amounts.contract)
11. 0.008 27.631 ↑ 3,939.0 1 1

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

  • Sort Key: temp_rr_contracts.sg_con_contract
  • Sort Method: quicksort Memory: 25kB
12. 0.188 27.623 ↑ 3,939.0 1 1

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

  • Hash Cond: (temp_rr_contracts.sg_con_dealer = sg_dlr_m1.sg_dlr_dealer)
13. 0.005 0.068 ↑ 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.067..0.068 rows=1 loops=1)

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

  • Index Cond: (rr_contract = '6000027068'::bpchar)
15. 12.680 27.367 ↓ 1.0 80,463 1

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

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

17. 2,716.028 2,817.218 ↑ 1.6 170,424 1

Sort (cost=34,295.02..34,979.22 rows=273,680 width=44) (actual time=2,803.960..2,817.218 rows=170,424 loops=1)

  • Sort Key: temp_bi_vci_rr_contract_amounts.contract
  • Sort Method: external sort Disk: 19,280kB
18. 101.190 101.190 ↓ 2.9 787,798 1

Seq Scan on temp_bi_vci_rr_contract_amounts (cost=0.00..9,578.80 rows=273,680 width=44) (actual time=0.041..101.190 rows=787,798 loops=1)

19. 11.689 39.708 ↑ 1.0 155,186 1

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

20. 28.019 28.019 ↑ 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.057..28.019 rows=155,186 loops=1)

21. 5.193 15.188 ↑ 6.2 74,857 1

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

22. 9.995 9.995 ↑ 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.042..9.995 rows=74,857 loops=1)

23. 16.252 28.887 ↑ 1.0 114,632 1

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

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

25. 204.980 780.935 ↓ 1.0 1,162,846 1

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

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

27. 0.016 0.057 ↓ 1.1 14 1

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

28. 0.041 0.041 ↓ 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.041 rows=14 loops=1)

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

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,691kB
30. 11.201 270.853 ↓ 1.3 123,760 1

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

31. 84.702 259.652 ↓ 1.3 123,760 1

HashAggregate (cost=13,630.08..14,558.55 rows=92,847 width=110) (actual time=228.480..259.652 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)
32. 160.455 174.950 ↓ 1.3 123,760 1

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

  • Group Key: vin_details.vin_pattern
33. 14.495 14.495 ↓ 1.1 175,359 1

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

34.          

SubPlan (for Hash Left Join)

35. 0.000 0.000 ↓ 0.0 0

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

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

Unique (cost=0.28..255.62 rows=391 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_1 (cost=0.28..254.61 rows=404 width=9) (never executed)

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

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

40. 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.251 ms
Execution time : 5,637.042 ms