explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GtZ0

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 295,918.021 ↓ 0.0 0 1

Unique (cost=11,082,153.15..11,082,154.78 rows=163 width=30) (actual time=295,918.021..295,918.021 rows=0 loops=1)

2.          

CTE whsm_with_zones

3. 23,672.171 65,971.219 ↓ 7,952.6 61,012,047 1

Merge Join (cost=6,156.50..2,448,668.38 rows=7,672 width=20) (actual time=295.481..65,971.219 rows=61,012,047 loops=1)

  • 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. 37,719.224 37,719.224 ↓ 1.0 60,298,211 1

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 time=0.024..37,719.224 rows=60,298,211 loops=1)

  • Index Cond: (is_excluded = 'N'::bpchar)
  • Heap Fetches: 1009721
5. 4,527.550 4,579.824 ↓ 2,792.3 61,012,282 1

Sort (cost=6,155.81..6,210.44 rows=21,850 width=51) (actual time=295.453..4,579.824 rows=61,012,282 loops=1)

  • Sort Key: smzc.region_name, smzg.ship_method_zoning_group, smzc.zone
  • Sort Method: quicksort Memory: 12504kB
6. 28.718 52.274 ↓ 3.9 85,875 1

Hash Join (cost=180.32..4,580.94 rows=21,850 width=51) (actual time=1.969..52.274 rows=85,875 loops=1)

  • Hash Cond: ((smzc.region_name = smzg.region_name) AND ((smzc.ship_method)::text = (smzg.ship_method)::text))
7. 21.745 21.745 ↓ 1.0 85,875 1

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

  • Filter: (is_enabled = 'Y'::bpchar)
  • Rows Removed by Filter: 19706
8. 1.149 1.811 ↑ 1.0 4,728 1

Hash (cost=108.73..108.73 rows=4,773 width=37) (actual time=1.811..1.811 rows=4,728 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 390kB
9. 0.662 0.662 ↑ 1.0 4,728 1

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

10. 0.009 295,918.021 ↓ 0.0 0 1

Sort (cost=8,633,484.78..8,633,485.19 rows=163 width=30) (actual time=295,918.021..295,918.021 rows=0 loops=1)

  • Sort Key: wsm.ship_method, wsm.warehouse_id, wsom3.ship_option
  • Sort Method: quicksort Memory: 25kB
11. 44.507 295,918.012 ↓ 0.0 0 1

Hash Join (cost=8,551,657.30..8,633,478.79 rows=163 width=30) (actual time=295,918.012..295,918.012 rows=0 loops=1)

  • Hash Cond: (wsom3.ship_method_visibility_level = vis.ship_method_visibility_level)
12. 178.772 295,873.489 ↓ 383.2 249,455 1

Nested Loop (cost=8,551,656.24..8,633,473.66 rows=651 width=35) (actual time=199,444.061..295,873.489 rows=249,455 loops=1)

13. 25,199.699 231,388.087 ↓ 952.5 1,108,735 1

Merge Semi Join (cost=8,551,655.68..8,622,754.82 rows=1,164 width=102) (actual time=175,621.429..231,388.087 rows=1,108,735 loops=1)

  • Merge Cond: ((wsm.ship_method)::text = (whsmz.ship_method)::text)
  • Join Filter: ((whsmz.warehouse_id IS NULL) OR (whsmz.warehouse_id = wsm.warehouse_id))
  • Rows Removed by Join Filter: 124463155
14. 285.105 50,469.374 ↓ 253.9 1,176,267 1

Merge Left Join (cost=8,551,007.19..8,621,662.27 rows=4,632 width=24) (actual time=43,840.756..50,469.374 rows=1,176,267 loops=1)

  • Merge Cond: (((wsm.ship_method)::text = (orl.ship_method)::text) AND (wsm.warehouse_id = orl.source_node))
  • Filter: (('Y'::text) IS NULL)
  • Rows Removed by Filter: 7998
15. 2,550.554 3,263.583 ↓ 1.3 1,184,265 1

Sort (cost=151,327.53..153,643.26 rows=926,291 width=24) (actual time=2,927.537..3,263.583 rows=1,184,265 loops=1)

  • Sort Key: wsm.ship_method, wsm.warehouse_id
  • Sort Method: external merge Disk: 40152kB
16. 202.141 713.029 ↓ 1.3 1,184,265 1

Merge Join (cost=0.79..59,526.97 rows=926,291 width=24) (actual time=1.613..713.029 rows=1,184,265 loops=1)

  • Merge Cond: (wi.warehouse_id = wsm.warehouse_id)
17. 53.480 53.480 ↓ 1.0 50,092 1

Index Scan using pk_warehouse_info on warehouse_info wi (cost=0.29..3,530.27 rows=49,047 width=5) (actual time=0.086..53.480 rows=50,092 loops=1)

  • Filter: ((organization_name)::text = ANY ('{BR,CA,MX,US}'::text[]))
  • Rows Removed by Filter: 28981
18. 457.408 457.408 ↓ 1.0 1,495,067 1

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 time=0.013..457.408 rows=1,495,067 loops=1)

  • Filter: ((ship_method)::text <> ALL ('{CLEANUP,DISTANCE_ZONES,INTERNAL,TARGET_NEXT_DAY,TARGET_NEXT_DAY_SIG,TARGET_SECOND_DAY,TARGET_SECOND_DAY_SIG}'::text[]))
  • Rows Removed by Filter: 9914
  • Heap Fetches: 57903
19. 4.912 46,920.686 ↑ 21.2 54,599 1

Materialize (cost=8,399,679.66..8,457,471.97 rows=1,155,846 width=49) (actual time=40,913.214..46,920.686 rows=54,599 loops=1)

20. 1,038.182 46,915.774 ↑ 21.2 54,599 1

Unique (cost=8,399,679.66..8,443,023.90 rows=1,155,846 width=49) (actual time=40,913.211..46,915.774 rows=54,599 loops=1)

21. 12,449.905 45,877.592 ↓ 1.6 9,316,444 1

Sort (cost=8,399,679.66..8,414,127.74 rows=5,779,232 width=49) (actual time=40,913.210..45,877.592 rows=9,316,444 loops=1)

  • Sort Key: orl.ship_method, orl.source_node
  • Sort Method: external merge Disk: 267560kB
22. 8,881.422 33,427.687 ↓ 1.6 9,316,444 1

Merge Join (cost=1.39..7,637,725.21 rows=5,779,232 width=49) (actual time=0.074..33,427.687 rows=9,316,444 loops=1)

  • Merge Cond: (ori.obt_route_id = orl.obt_route_id)
  • Join Filter: (NOT (SubPlan 4))
  • Rows Removed by Join Filter: 1786968
23. 201.155 201.155 ↑ 1.2 1,786,968 1

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 time=0.023..201.155 rows=1,786,968 loops=1)

  • Heap Fetches: 0
24. 2,138.286 2,138.286 ↑ 1.0 11,103,412 1

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 time=0.013..2,138.286 rows=11,103,412 loops=1)

  • Heap Fetches: 0
25.          

SubPlan (forMerge Join)

26. 0.000 22,206.824 ↑ 1.0 1 11,103,412

Result (cost=0.60..0.61 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11,103,412)

27.          

Initplan (forResult)

28. 0.000 22,206.824 ↑ 1.0 1 11,103,412

Limit (cost=0.56..0.60 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11,103,412)

29. 22,206.824 22,206.824 ↑ 51.0 1 11,103,412

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 time=0.002..0.002 rows=1 loops=11,103,412)

  • Index Cond: ((obt_route_id = ori.obt_route_id) AND (leg_no IS NOT NULL))
  • Heap Fetches: 0
30. 69,494.184 155,719.014 ↓ 24,161.9 185,370,449 1

Sort (cost=648.49..667.67 rows=7,672 width=98) (actual time=131,780.657..155,719.014 rows=185,370,449 loops=1)

  • Sort Key: whsmz.ship_method
  • Sort Method: external sort Disk: 1912032kB
31. 86,224.830 86,224.830 ↓ 7,952.6 61,012,047 1

CTE Scan on whsm_with_zones whsmz (cost=0.00..153.44 rows=7,672 width=98) (actual time=295.484..86,224.830 rows=61,012,047 loops=1)

32. 22,191.798 64,306.630 ↓ 0.0 0 1,108,735

Index Scan using pk_whse_ship_option_methods on warehouse_ship_option_methods wsom3 (cost=0.56..9.20 rows=1 width=35) (actual time=0.053..0.058 rows=0 loops=1,108,735)

  • 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)))
  • Rows Removed by Filter: 5
33.          

SubPlan (forIndex Scan)

34. 42,114.832 42,114.832 ↓ 3.4 57 2,632,177

Index Scan using fk2_promise_cutoff_groups on promise_cutoff_groups (cost=0.28..16.57 rows=17 width=16) (actual time=0.003..0.016 rows=57 loops=2,632,177)

  • Index Cond: ((ship_option)::text = (wsom3.ship_option)::text)
35. 0.006 0.016 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.010 0.010 ↑ 1.0 1 1

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

  • Filter: ((name)::text = 'PROMISE'::text)
  • Rows Removed by Filter: 3