explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gGBi

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=31,923.89..31,924.56 rows=3 width=1,373) (never executed)

2.          

CTE purchase_log

3. 0.000 0.000 ↓ 0.0

Hash Join (cost=9,853.53..18,621.81 rows=1 width=867) (never executed)

4. 0.000 0.000 ↓ 0.0

Seq Scan on cbs_vwfullpurchasedetails p (cost=0..8,604.5 rows=31,195 width=867) (never executed)

  • Filter: (p.confirmed AND (p.lineofbusiness = 'EchoPark'::text))
5. 0.000 0.000 ↓ 0.0

Hash (cost=9,385.2..9,385.2 rows=31,222 width=25) (never executed)

6. 0.000 0.000 ↓ 0.0

HashAggregate (cost=8,760.76..9,072.98 rows=31,222 width=25) (never executed)

  • Group Key: cbs_vwfullpurchasedetails.vin
7. 0.000 0.000 ↓ 0.0

Seq Scan on cbs_vwfullpurchasedetails cbs_vwfullpurchasedetails (cost=0..8,604.5 rows=31,253 width=25) (never executed)

  • Filter: (cbs_vwfullpurchasedetails.lineofbusiness = 'EchoPark'::text)
8.          

CTE transport

9. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,285.25..2,049.77 rows=75 width=334) (never executed)

10. 0.000 0.000 ↓ 0.0

Seq Scan on transport_log_combined t1 (cost=0..685.85 rows=14,985 width=334) (never executed)

11. 0.000 0.000 ↓ 0.0

Hash (cost=1,060.47..1,060.47 rows=14,985 width=22) (never executed)

12. 0.000 0.000 ↓ 0.0

HashAggregate (cost=760.77..910.62 rows=14,985 width=22) (never executed)

  • Group Key: transport_log_combined.vins
13. 0.000 0.000 ↓ 0.0

Seq Scan on transport_log_combined transport_log_combined (cost=0..685.85 rows=14,985 width=22) (never executed)

14.          

CTE mmt

15. 0.000 0.000 ↓ 0.0

Seq Scan on ep_inv ep_inv (cost=0..465.48 rows=4,187 width=122) (never executed)

16. 0.000 0.000 ↓ 0.0

Sort (cost=10,786.83..10,786.84 rows=3 width=1,373) (never executed)

  • Sort Key: ep.rs_region_name, ep.vi_orig_pod_id, ep.vi_store_id, ep.vi_vehicle_id, ep.vi_invtr_id, ep.vi_curr_store_id, ep.org_name, ("left"(ep.vi_stock_no, 1)), ep.v_vin, ep.vi_stock_no, ep.v_uvc, bm.vehicle_class, bm.vehicle_class_1, ep.v_year, mmt.make, mmt.model, (CASE WHEN ((COALESCE(ep.bb_make, mmt.make) = 'BMW'::text) AND (COALESCE(CASE WHEN ((ep.bb_model = 'Elantra'::text) AND (ep.bb_series ~~ '%GT%'::text)) THEN 'Elantra GT'::text ELSE ep.bb_model END, mmt.model) ~~ '%series%'::text)) THEN "left"(mmt."trim", 3) WHEN ((COALESCE(ep.bb_make, mmt.make) ~~ '%merced%'::text) AND (mmt."trim" ~~ '%(%'::text)) THEN "left"(mmt."trim", (CASE WHEN ("position"(mmt."trim", '('::text) >= 1) THEN "position"(mmt."trim", '('::text) ELSE 1 END - 1)) WHEN ((COALESCE(ep.bb_make, ep.v_make) ~~ '%merced%'::text) AND (COALESCE(CASE WHEN ((ep.bb_model = 'Elantra'::text) AND (ep.bb_series ~~ '%GT%'::text)) THEN 'Elantra GT'::text ELSE ep.bb_model END, mmt.model) ~~ 'metris%'::text)) THEN 'Metris'::text WHEN ((COALESCE(ep.bb_make, mmt.make) ~~ '%merced%'::text) AND (mmt."trim" !~~ '%(%'::text)) THEN ((split_part(mmt."trim", ' '::text, 1) || ' '::text) || split_part(mmt."trim", ' '::text, 2)) WHEN ((COALESCE(ep.bb_make, mmt.make) = 'Lexus'::text) AND (length(COALESCE(CASE WHEN ((ep.bb_model = 'Elantra'::text) AND (ep.bb_series ~~ '%GT%'::text)) THEN 'Elantra GT'::text ELSE ep.bb_model END, mmt.model)) < 3) AND (mmt."trim" ~~ '%(%'::text)) THEN "left"(mmt."trim", (CASE WHEN ("position"(mmt."trim", '('::text) >= 1) THEN "position"(mmt."trim", '('::text) ELSE 1 END - 1)) ELSE COALESCE(CASE WHEN ((ep.bb_model = 'Elantra'::text) AND (ep.bb_series ~~ '%GT%'::text)) THEN 'Elantra GT'::text ELSE mmt.model END, mmt.model) END), mmt."trim", (CASE WHEN (mmt."trim" ~~ '%(%'::text) THEN "left"(mmt."trim", (CASE WHEN ("position"(mmt."trim", '('::text) >= 1) THEN "position"(mmt."trim", '('::text) ELSE 1 END - 1)) ELSE mmt."trim" END), pl."trim", ep.vi_mileage, (rtrim(ltrim(ep.vi_extr_color))), (rtrim(ltrim(ep.vi_intr_color))), ep.vi_engine, (CASE WHEN (ep.vi_drivetrain IS NULL) THEN NULL::text WHEN ((upper(ep.vi_drivetrain) ~~ '%AWD%'::text) OR (upper(ep.vi_drivetrain) ~~ '%4WD%'::text)) THEN '4WD'::text ELSE '2WD'::text END), (CASE WHEN (upper(ep.vi_transmission) ~~ 'MANUAL%'::text) THEN 'Manual'::text ELSE 'Automatic'::text END), (COALESCE(ep.opt_leather, '0'::double precision)), (COALESCE(ep.opt_nav, '0'::double precision)), (COALESCE(ep.opt_pano, '0'::double precision)), ep.vi_group_age, ep.vi_age, ep.vi_daysonlot, (CASE WHEN (CASE WHEN (ep.sr_rollup_code = 'Pre-Recon'::text) THEN '0'::double precision WHEN ((ep.recon_date IS NULL) AND (ep.sr_rollup_code = 'Recon'::text) AND (COALESCE(ep.vi_daysonlot, '0'::bigint) = 0)) THEN (ep.vi_age)::double precision WHEN (date_part('day'::text, ((ep.recon_date)::timestamp without time zone - CASE WHEN (ep.vi_acquired_date < ep.vi_creation_date) THEN ep.vi_acquired_date ELSE ((ep.vi_creation_date)::date)::timestamp without time zone END)) > ((ep.vi_age - COALESCE(ep.vi_daysonlot, '0'::bigint)))::double precision) THEN NULL::double precision ELSE (((ep.vi_age - COALESCE(ep.vi_daysonlot, '0'::bigint)))::double precision - COALESCE(date_part('day'::text, ((ep.recon_date)::timestamp without time zone - CASE WHEN (ep.vi_acquired_date < ep.vi_creation_date) THEN ep.vi_acquired_date ELSE ((ep.vi_creation_date)::date)::timestamp without time zone END)), '0'::double precision)) END > '150'::double precision) THEN NULL::double precision ELSE CASE WHEN (ep.sr_rollup_code = 'Pre-Recon'::text) THEN '0'::double precision WHEN ((ep.recon_date IS NULL) AND (ep.sr_rollup_code = 'Recon'::text) AND (COALESCE(ep.vi_daysonlot, '0'::bigint) = 0)) THEN (ep.vi_age)::double precision WHEN (date_part('day'::text, ((ep.recon_date)::timestamp without time zone - CASE WHEN (ep.vi_acquired_date < ep.vi_creation_date) THEN ep.vi_acquired_date ELSE ((ep.vi_creation_date)::date)::timestamp without time zone END)) > ((ep.vi_age - COALESCE(ep.vi_daysonlot, '0'::bigint)))::double precision) THEN NULL::double precision ELSE (((ep.vi_age - COALESCE(ep.vi_daysonlot, '0'::bigint)))::double precision - COALESCE(date_part('day'::text, ((ep.recon_date)::timestamp without time zone - CASE WHEN (ep.vi_acquired_date < ep.vi_creation_date) THEN ep.vi_acquired_date ELSE ((ep.vi_creation_date)::date)::timestamp without time zone END)), '0'::double precision)) END END), (CASE WHEN ((ep.recon_date IS NULL) AND (ep.sr_rollup_code <> 'Pre-Recon'::text)) THEN '0'::double precision WHEN ((ep.recon_date IS NULL) AND (ep.sr_rollup_code = 'Pre-Recon'::text)) THEN (ep.vi_age)::double precision WHEN (date_part('day'::text, ((ep.recon_date)::timestamp without time zone - CASE WHEN (ep.vi_acquired_date < ep.vi_creation_date) THEN ep.vi_acquired_date ELSE ((ep.vi_creation_date)::date)::timestamp without time zone END)) > ((ep.vi_age - COALESCE(ep.vi_daysonlot, '0'::bigint)))::double precision) THEN NULL::double precision ELSE date_part('day'::text, ((ep.recon_date)::timestamp without time zone - CASE WHEN (ep.vi_acquired_date < ep.vi_creation_date) THEN ep.vi_acquired_date ELSE ((ep.vi_creation_date)::date)::timestamp without time zone END)) END), (LEAST(ep.vi_acquired_date, ((ep.vi_creation_date)::date)::timestamp without time zone)), (COALESCE(ep.vi_sonic_buyer, pl.buyername)), pl.physicallocation, pl.sourcename, pl.seller, (CASE WHEN pl.isauctionclosed THEN 'Closed'::text WHEN ((NOT pl.isauctionclosed) OR (pl.vin IS NOT NULL)) THEN 'Open'::text ELSE NULL::text END), pl.isliveorfixed, pl.cost, pl.comment, t.vendor, acv.webv_base_cost, (COALESCE(gt.trans_cost, acv.webv_transport)), ((acv.webv_auctionfee + acv.webv_buyerfee)), (CASE WHEN ((acv.acv - COALESCE(gt.trans_cost, acv.webv_transport)) < '0'::double precision) THEN '0'::double precision ELSE (acv.acv - COALESCE(gt.trans_cost, acv.webv_transport)) END), acv.acv, ep.vp_act_recon_amt, ep.ro_openro_amt, ep.vp_gl_balance, ep.vp_retail_price, ((ep.vp_retail_price - ep.vp_gl_balance)), ep.pch_old_value, ep.pch_created_by, (((now())::date - ep.pch_creation_date)), ep.pch_creation_date, ep.vi_is_price_override, epp.vg_guardrail, epp.vnp_current_markdown_reason, epp.vpa_price_type, mp.epp_mapr_ma_mkt_price, mp.epp_mapu_mkt_at_purchase, epp.icmr_value_default_target, epp.icmr_value_modified_target, ep.kbb_wsale_above, ep.kbb_retail_above, ep.nada_trade_above, ep.nada_retail_above, ep.nada_creation_date, (CASE WHEN (ep.sr_rollup_code !~~ 'AFS%'::text) THEN 'No Price Analysis'::text WHEN (((ep.vp_retail_price >= cg.highoutlierlowerbound) AND (ep.vp_retail_price <= cg.highoutlierupperbound)) OR ((ep.vp_retail_price >= cg.lowoutlierlowerbound) AND (ep.vp_retail_price <= cg.lowoutlierupperbound))) THEN 'Uncertain'::text WHEN ((ep.vp_retail_price >= cg.overpricedlowerbound) AND (ep.vp_retail_price <= cg.overpricedupperbound)) THEN 'Overpriced'::text WHEN ((ep.vp_retail_price >= cg.highpricedlowerbound) AND (ep.vp_retail_price <= cg.highpricedupperbound)) THEN 'High Price'::text WHEN ((ep.vp_retail_price >= cg.okpricedlowerbound) AND (ep.vp_retail_price <= cg.fairpricedupperbound)) THEN 'Fair Deal'::text WHEN ((ep.vp_retail_price >= cg.goodpricelowerbound) AND (ep.vp_retail_price <= cg.goodpriceupperbound)) THEN 'Good Deal'::text WHEN ((ep.vp_retail_price >= cg.greatpricelowerbound) AND (ep.vp_retail_price <= cg.greatpriceupperbound)) THEN 'Great Deal'::text ELSE 'No Price Analysis'::text END), (CASE WHEN (ep.sr_rollup_code !~~ 'AFS%'::text) THEN 'No Price Analysis'::character varying WHEN (((ep.vp_retail_price >= cg.highoutlierlowerbound) AND (ep.vp_retail_price <= cg.highoutlierupperbound)) OR ((ep.vp_retail_price >= cg.lowoutlierlowerbound) AND (ep.vp_retail_price <= cg.lowoutlierupperbound))) THEN 'No Price Analysis'::character varying WHEN ((ep.vp_retail_price >= cg.overpricedlowerbound) AND (ep.vp_retail_price <= cg.overpricedupperbound)) THEN ((ep.vp_retail_price - cg.highpricedlowerbound))::character varying WHEN ((ep.vp_retail_price >= cg.highpricedlowerbound) AND (ep.vp_retail_price <= cg.highpricedupperbound)) THEN ((ep.vp_retail_price - cg.okpricedlowerbound))::character varying WHEN ((ep.vp_retail_price >= cg.okpricedlowerbound) AND (ep.vp_retail_price <= cg.fairpricedupperbound)) THEN ((ep.vp_retail_price - cg.goodpricelowerbound))::character varying WHEN ((ep.vp_retail_price >= cg.goodpricelowerbound) AND (ep.vp_retail_price <= cg.goodpriceupperbound)) THEN ((ep.vp_retail_price - cg.greatpricelowerbound))::character varying ELSE 'No Price Analysis'::character varying END), cg.dealrating, cg.searchrankinsearchurl, cg.totalsearchesinsearchurl, (CASE WHEN (ep.cf_vehicle_id IS NULL) THEN 'No'::text ELSE 'Yes'::text END), vdp.viewcount, (COALESCE(l.vs_leadcount, 0)), ep.vi_notes, ep.vi_curr_status_id, ep.s_status_name, ep.sr_rollup_code, (CASE WHEN (ep.sr_rollup_code ~~ 'AFS%'::text) THEN 'In-Stock'::text WHEN (ep.sr_rollup_code = 'Recon'::text) THEN 'Recon'::text WHEN (ep.sr_rollup_code = 'Pre-Recon'::text) THEN 'In-Transit'::text ELSE NULL::text END), (COALESCE(pl.fleetindicator, false)), (CASE WHEN (((date_part('year'::text, (CURRENT_DATE)::timestamp without time zone) - (ep.v_year)::double precision) < '5'::double precision) AND (ep.vi_mileage < 50000) AND (ep.cf_vehicle_id IS NULL)) THEN 'Yes'::text ELSE 'No'::text END), (CASE WHEN (ep.v_vin IS NULL) THEN 1 ELSE 0 END), ep.metaloaddate
17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=579.11..10,786.81 rows=3 width=1,373) (never executed)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=579.11..10,761.31 rows=1 width=1,105) (never executed)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=579.11..9,621.8 rows=1 width=970) (never executed)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=578.69..4,410.54 rows=1 width=939) (never executed)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=578.26..4,408.43 rows=1 width=935) (never executed)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=578.26..4,405.99 rows=1 width=903) (never executed)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=577.84..4,405.29 rows=1 width=899) (never executed)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=577.42..4,403.94 rows=1 width=891) (never executed)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=577.42..4,151.15 rows=1 width=854) (never executed)

26. 0.000 0.000 ↓ 0.0

Hash Join (cost=577.42..4,151.1 rows=1 width=596) (never executed)

27. 0.000 0.000 ↓ 0.0

Seq Scan on gldetail_trans gt (cost=0..2,536.24 rows=138,324 width=21) (never executed)

28. 0.000 0.000 ↓ 0.0

Hash (cost=577.41..577.41 rows=1 width=588) (never executed)

29. 0.000 0.000 ↓ 0.0

Hash Join (cost=462.26..577.41 rows=1 width=588) (never executed)

30. 0.000 0.000 ↓ 0.0

CTE Scan on mmt mmt (cost=0..83.74 rows=4,187 width=136) (never executed)

31. 0.000 0.000 ↓ 0.0

Hash (cost=461.06..461.06 rows=80 width=496) (never executed)

32. 0.000 0.000 ↓ 0.0

Hash Join (cost=52.4..461.06 rows=80 width=496) (never executed)

33. 0.000 0.000 ↓ 0.0

Seq Scan on ep_inv ep (cost=0..350.34 rows=4,183 width=489) (never executed)

  • Filter: ((ep.vi_curr_store_id <> 2002) OR (ep.vi_curr_store_id IS NULL))
34. 0.000 0.000 ↓ 0.0

Hash (cost=52.29..52.29 rows=9 width=7) (never executed)

35. 0.000 0.000 ↓ 0.0

Seq Scan on sdw_dim_entity e (cost=0..52.29 rows=9 width=7) (never executed)

  • Filter: (((e.entbrand)::text = 'EchoPark'::text) AND ((e.entactive)::text = 'Active'::text))
36. 0.000 0.000 ↓ 0.0

CTE Scan on purchase_log pl (cost=0..0.02 rows=1 width=274) (never executed)

37. 0.000 0.000 ↓ 0.0

Seq Scan on acv_addtl_inv acv (cost=0..160.65 rows=5,265 width=72) (never executed)

38. 0.000 0.000 ↓ 0.0

Index Scan using mk_price_mk on mkt_price mp (cost=0.42..1.35 rows=1 width=29) (never executed)

  • Index Cond: (((mp.epp_lv_vin)::text = ep.v_vin) AND (mp.epp_is_store_id = ep.vi_store_id))
39. 0.000 0.000 ↓ 0.0

Index Scan using idx_eleads_count_vin on eleads_count l (cost=0.42..0.69 rows=1 width=22) (never executed)

  • Index Cond: ((l.vs_vin)::text = ep.v_vin)
40. 0.000 0.000 ↓ 0.0

CTE Scan on transport t (cost=0..1.5 rows=75 width=110) (never executed)

41. 0.000 0.000 ↓ 0.0

Index Scan using ddc_vehicle_views_mk on ddc_vehicle_views vdp (cost=0.43..2.11 rows=1 width=62) (never executed)

  • Index Cond: ((vdp.echoparkstockno)::text = ep.vi_stock_no)
42. 0.000 0.000 ↓ 0.0

Index Scan using ep_pricing_mk on ep_pricing epp (cost=0.42..5,211.25 rows=1 width=52) (never executed)

  • Index Cond: (epp.vpa_store_id = ep.vi_store_id)
  • Filter: ((epp.vpa_vin)::text = ep.v_vin)
43. 0.000 0.000 ↓ 0.0

Seq Scan on car_gurus_details cg (cost=0..1,032.25 rows=8,581 width=152) (never executed)

  • Filter: (cg.isnew = 0)
44. 0.000 0.000 ↓ 0.0

Seq Scan on vc_mapping bm (cost=0..16.3 rows=630 width=96) (never executed)