explain.depesz.com

PostgreSQL's explain analyze made readable

Result: juIf

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

Unique (cost=11,082,153.15..11,082,154.78 rows=163 width=30) (actual rows= loops=)

2.          

CTE whsm_with_zones

3. 0.000 0.000 ↓ 0.0

Merge Join (cost=6,156.50..2,448,668.38 rows=7,672 width=20) (actual rows= loops=)

  • Merge Cond: ((smz.region_name = smzc.region_name) AND ((smz.ship_method_zoning_group)::text = (smzg.ship_method_zoning_group)::text) AND ((smz.zone)::text = (smzc.zone)::text))
4. 0.000 0.000 ↓ 0.0

Index Only Scan using ship_method_zone_cover_unique on ship_method_zone_coverage smz (cost=0.69..2,006,257.66 rows=58,127,957 width=41) (actual rows= loops=)

  • Index Cond: (is_excluded = 'N'::bpchar)
5. 0.000 0.000 ↓ 0.0

Sort (cost=6,155.81..6,210.44 rows=21,850 width=51) (actual rows= loops=)

  • Sort Key: smzc.region_name, smzg.ship_method_zoning_group, smzc.zone
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=180.32..4,580.94 rows=21,850 width=51) (actual rows= loops=)

  • Hash Cond: ((smzc.region_name = smzg.region_name) AND ((smzc.ship_method)::text = (smzg.ship_method)::text))
7. 0.000 0.000 ↓ 0.0

Seq Scan on ship_method_zone_configuration smzc (cost=0.00..3,109.71 rows=85,792 width=31) (actual rows= loops=)

  • Filter: (is_enabled = 'Y'::bpchar)
8. 0.000 0.000 ↓ 0.0

Hash (cost=108.73..108.73 rows=4,773 width=37) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on ship_method_zoning_groups smzg (cost=0.00..108.73 rows=4,773 width=37) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=8,633,484.78..8,633,485.19 rows=163 width=30) (actual rows= loops=)

  • Sort Key: wsm.ship_method, wsm.warehouse_id, wsom3.ship_option
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=8,551,657.30..8,633,478.79 rows=163 width=30) (actual rows= loops=)

  • Hash Cond: (wsom3.ship_method_visibility_level = vis.ship_method_visibility_level)
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8,551,656.24..8,633,473.66 rows=651 width=35) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Merge Semi Join (cost=8,551,655.68..8,622,754.82 rows=1,164 width=102) (actual rows= loops=)

  • Merge Cond: ((wsm.ship_method)::text = (whsmz.ship_method)::text)
  • Join Filter: ((whsmz.warehouse_id IS NULL) OR (whsmz.warehouse_id = wsm.warehouse_id))
14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=8,551,007.19..8,621,662.27 rows=4,632 width=24) (actual rows= loops=)

  • Merge Cond: (((wsm.ship_method)::text = (orl.ship_method)::text) AND (wsm.warehouse_id = orl.source_node))
  • Filter: (('Y'::text) IS NULL)
15. 0.000 0.000 ↓ 0.0

Sort (cost=151,327.53..153,643.26 rows=926,291 width=24) (actual rows= loops=)

  • Sort Key: wsm.ship_method, wsm.warehouse_id
16. 0.000 0.000 ↓ 0.0

Merge Join (cost=0.79..59,526.97 rows=926,291 width=24) (actual rows= loops=)

  • Merge Cond: (wi.warehouse_id = wsm.warehouse_id)
17. 0.000 0.000 ↓ 0.0

Index Scan using pk_warehouse_info on warehouse_info wi (cost=0.29..3,530.27 rows=49,047 width=5) (actual rows= loops=)

  • Filter: ((organization_name)::text = ANY ('{BR,CA,MX,US}'::text[]))
18. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_warehouse_ship_methods on warehouse_ship_methods wsm (cost=0.43..43,000.43 rows=1,444,744 width=19) (actual rows= loops=)

  • Filter: ((ship_method)::text <> ALL ('{CLEANUP,DISTANCE_ZONES,INTERNAL,TARGET_NEXT_DAY,TARGET_NEXT_DAY_SIG,TARGET_SECOND_DAY,TARGET_SECOND_DAY_SIG}'::text[]))
19. 0.000 0.000 ↓ 0.0

Materialize (cost=8,399,679.66..8,457,471.97 rows=1,155,846 width=49) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Unique (cost=8,399,679.66..8,443,023.90 rows=1,155,846 width=49) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=8,399,679.66..8,414,127.74 rows=5,779,232 width=49) (actual rows= loops=)

  • Sort Key: orl.ship_method, orl.source_node
22. 0.000 0.000 ↓ 0.0

Merge Join (cost=1.39..7,637,725.21 rows=5,779,232 width=49) (actual rows= loops=)

  • Merge Cond: (ori.obt_route_id = orl.obt_route_id)
  • Join Filter: (NOT (SubPlan 4))
23. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_obt_ri_route_id on obt_route_info ori (cost=0.43..53,555.64 rows=2,134,814 width=8) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Index Only Scan using i_obt_route_legs_x on obt_route_legs orl (cost=0.69..402,669.66 rows=11,558,465 width=33) (actual rows= loops=)

25.          

SubPlan (for Merge Join)

26. 0.000 0.000 ↓ 0.0

Result (cost=0.60..0.61 rows=1 width=8) (actual rows= loops=)

27.          

Initplan (for Result)

28. 0.000 0.000 ↓ 0.0

Limit (cost=0.56..0.60 rows=1 width=8) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using pk_obt_rl_ri_ln on obt_route_legs orl2 (cost=0.56..2.58 rows=51 width=8) (actual rows= loops=)

  • Index Cond: ((obt_route_id = ori.obt_route_id) AND (leg_no IS NOT NULL))
30. 0.000 0.000 ↓ 0.0

Sort (cost=648.49..667.67 rows=7,672 width=98) (actual rows= loops=)

  • Sort Key: whsmz.ship_method
31. 0.000 0.000 ↓ 0.0

CTE Scan on whsm_with_zones whsmz (cost=0.00..153.44 rows=7,672 width=98) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using pk_whse_ship_option_methods on warehouse_ship_option_methods wsom3 (cost=0.56..9.20 rows=1 width=35) (actual rows= loops=)

  • Index Cond: ((warehouse_id = wsm.warehouse_id) AND ((ship_method)::text = (wsm.ship_method)::text))
  • Filter: ((is_enabled = 'Y'::bpchar) AND ((ship_option)::text = ANY ('{next,next-business,next-ca,next-mx,next-mx-store,next-non48,next-wow,prime-1-sat,same-ca,same-mx,same-mx-store,same-us,second,second-ca,second-mx,second-mx-store,second-non48}'::text[])) AND (NOT (SubPlan 2)))
33.          

SubPlan (for Index Scan)

34. 0.000 0.000 ↓ 0.0

Index Scan using fk2_promise_cutoff_groups on promise_cutoff_groups (cost=0.28..16.57 rows=17 width=16) (actual rows= loops=)

  • Index Cond: ((ship_option)::text = (wsom3.ship_option)::text)
35. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=1 width=5) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on obt_ship_method_visibility vis (cost=0.00..1.05 rows=1 width=5) (actual rows= loops=)

  • Filter: ((name)::text = 'PROMISE'::text)