explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A84B

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 54,845.114 ↓ 43.0 43 1

Limit (cost=48,246.04..48,246.11 rows=1 width=283) (actual time=54,845.082..54,845.114 rows=43 loops=1)

2. 0.025 54,845.108 ↓ 43.0 43 1

Unique (cost=48,246.04..48,246.11 rows=1 width=283) (actual time=54,845.081..54,845.108 rows=43 loops=1)

3. 0.086 54,845.083 ↓ 43.0 43 1

Sort (cost=48,246.04..48,246.04 rows=1 width=283) (actual time=54,845.080..54,845.083 rows=43 loops=1)

  • Sort Key: destination_locations.name, origin_locations.name, shipments.id, shipments.created_at, shipments.updated_at, shipments.reference_number, shipments.origin_notes, shipments.destination_notes, shipments.earliest_ship_at, shipments.earliest_arrival_at, shipments.location_id, shipments.ready_to_ship_at, shipments.shipped_at, shipments.arrived_at, shipments.received_at, shipments.state, shipments.latest_arrival_at, shipments.latest_ship_at, shipments.origin_id, shipments.destination_id, shipments.archived_at, shipments.tracking_number, shipments.tracking_vendor, shipments.courier, shipments.container_number, shipments.flexport_container_id, (min(all_orders.need_by_date))
  • Sort Method: quicksort Memory: 36kB
4. 0.343 54,844.997 ↓ 43.0 43 1

GroupAggregate (cost=48,246.00..48,246.03 rows=1 width=283) (actual time=54,844.585..54,844.997 rows=43 loops=1)

  • Group Key: destination_locations.name, origin_locations.name, shipments.id
5. 5.789 54,844.654 ↓ 1,113.0 1,113 1

Sort (cost=48,246.00..48,246.01 rows=1 width=283) (actual time=54,844.577..54,844.654 rows=1,113 loops=1)

  • Sort Key: destination_locations.name, origin_locations.name, shipments.id
  • Sort Method: quicksort Memory: 344kB
6. 5,252.896 54,838.865 ↓ 1,113.0 1,113 1

Nested Loop Left Join (cost=45,943.21..48,245.99 rows=1 width=283) (actual time=808.422..54,838.865 rows=1,113 loops=1)

  • Join Filter: ((all_orders.order_id = shipment_fulfillments.order_id) AND (all_orders.order_type = (shipment_fulfillments.order_type)::text))
  • Rows Removed by Join Filter: 52,752,861
7. 1.607 64.147 ↓ 1,113.0 1,113 1

Nested Loop Left Join (cost=10,632.95..11,370.23 rows=1 width=297) (actual time=50.371..64.147 rows=1,113 loops=1)

8. 0.422 55.917 ↓ 179.0 179 1

Nested Loop (cost=10,632.66..11,361.31 rows=1 width=275) (actual time=50.357..55.917 rows=179 loops=1)

9. 0.735 54.958 ↓ 179.0 179 1

Nested Loop Left Join (cost=10,632.37..11,353.00 rows=1 width=261) (actual time=50.341..54.958 rows=179 loops=1)

10. 0.272 52.612 ↓ 179.0 179 1

Nested Loop Left Join (cost=10,632.08..11,344.68 rows=1 width=247) (actual time=50.320..52.612 rows=179 loops=1)

11. 8.498 51.136 ↓ 43.0 43 1

Bitmap Heap Scan on shipments (cost=10,631.79..11,335.76 rows=1 width=247) (actual time=50.303..51.136 rows=43 loops=1)

  • Recheck Cond: (origin_id = ANY ('{3038170,88217,5354666,3153120,9005800,16277001,16277002,16277003,16277004,16277006,16277007,16533081,16533083,16533085,16533086,16533087,16533088,16702917,16702927,17922772,17922773,17922774,17922775,17922776,17922777,17927108,17927109,17927110,17927111,17927112,18128212,18128213,18128584,18128587,18133146,18133176,18133177,18133178,18133181,18133182,18133216,18133224,18133230,18133232,18133242,18133246,18133248,18133250,18133252,18133274,18133276,18133278,18133280,18133307,18133312,18133314,18133322,18133324,18133326,18133328,18133330,18133333,18133365,18133366,18133385,18133389,18133407,18133409,18133422,18133423,18133425,18133428,18133491,18133493,18133495,18133531,18133533,18133549,18135002,18135004,18135005,18135017,18135019,18135055,18135072,18135073,18135075,18135086,29751843,29745896,29747444,29745899,29749057,29745895,29753389,29753388,27417000,27313521,27315047,27315049,27315048,27315052,27315053,27315050,27315046,27315051,27313522,30903640,30905372}'::bigint[]))
  • Filter: (((reference_number)::text ~~* '%AUS - S - CONG1221%'::text) AND ((hashed SubPlan 1) OR (hashed SubPlan 2)))
  • Rows Removed by Filter: 252
  • Heap Blocks: exact=96
12. 0.099 0.099 ↑ 1.6 295 1

Bitmap Index Scan on index_shipments_on_origin_id (cost=0.00..322.88 rows=464 width=0) (actual time=0.099..0.099 rows=295 loops=1)

  • Index Cond: (origin_id = ANY ('{3038170,88217,5354666,3153120,9005800,16277001,16277002,16277003,16277004,16277006,16277007,16533081,16533083,16533085,16533086,16533087,16533088,16702917,16702927,17922772,17922773,17922774,17922775,17922776,17922777,17927108,17927109,17927110,17927111,17927112,18128212,18128213,18128584,18128587,18133146,18133176,18133177,18133178,18133181,18133182,18133216,18133224,18133230,18133232,18133242,18133246,18133248,18133250,18133252,18133274,18133276,18133278,18133280,18133307,18133312,18133314,18133322,18133324,18133326,18133328,18133330,18133333,18133365,18133366,18133385,18133389,18133407,18133409,18133422,18133423,18133425,18133428,18133491,18133493,18133495,18133531,18133533,18133549,18135002,18135004,18135005,18135017,18135019,18135055,18135072,18135073,18135075,18135086,29751843,29745896,29747444,29745899,29749057,29745895,29753389,29753388,27417000,27313521,27315047,27315049,27315048,27315052,27315053,27315050,27315046,27315051,27313522,30903640,30905372}'::bigint[]))
13.          

SubPlan (for Bitmap Heap Scan)

14. 11.346 42.539 ↓ 1.0 24,829 1

Hash Join (cost=2,421.95..5,094.86 rows=23,836 width=8) (actual time=22.906..42.539 rows=24,829 loops=1)

  • Hash Cond: (location_hierarchies.child_id = locations.id)
15. 8.424 8.424 ↓ 1.0 26,544 1

Index Scan using index_location_hierarchies_on_parent_id on location_hierarchies (cost=0.29..2,605.07 rows=25,956 width=8) (actual time=0.015..8.424 rows=26,544 loops=1)

  • Index Cond: (parent_id = ANY ('{91242,91274,91281,91349,91387,91408,91423,91434,91442,91490,91497,91505,91658,91754,91783,91878,91888,91920,92014,92029,92061,92091,92093,92114,92117,92193,92228,92240,92245,92247,92256,92266,92274,92279,92281,92288,92293,92302,92304,92309,92334,92337,92344,92355,92358,92364,92367,114663,115833,115874,115875,117004,148505,1963143,7310527,8213690,27802621}'::bigint[]))
16. 11.298 22.769 ↑ 1.0 61,019 1

Hash (cost=1,658.15..1,658.15 rows=61,081 width=8) (actual time=22.769..22.769 rows=61,019 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,896kB
17. 11.471 11.471 ↑ 1.0 61,019 1

Seq Scan on locations (cost=0.00..1,658.15 rows=61,081 width=8) (actual time=0.007..11.471 rows=61,019 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 5,496
18. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2,421.95..5,094.86 rows=23,836 width=8) (never executed)

  • Hash Cond: (location_hierarchies_1.child_id = locations_1.id)
19. 0.000 0.000 ↓ 0.0 0

Index Scan using index_location_hierarchies_on_parent_id on location_hierarchies location_hierarchies_1 (cost=0.29..2,605.07 rows=25,956 width=8) (never executed)

  • Index Cond: (parent_id = ANY ('{91242,91274,91281,91349,91387,91408,91423,91434,91442,91490,91497,91505,91658,91754,91783,91878,91888,91920,92014,92029,92061,92091,92093,92114,92117,92193,92228,92240,92245,92247,92256,92266,92274,92279,92281,92288,92293,92302,92304,92309,92334,92337,92344,92355,92358,92364,92367,114663,115833,115874,115875,117004,148505,1963143,7310527,8213690,27802621}'::bigint[]))
20. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,658.15..1,658.15 rows=61,081 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on locations locations_1 (cost=0.00..1,658.15 rows=61,081 width=8) (never executed)

  • Filter: (deleted_at IS NULL)
22. 1.204 1.204 ↓ 4.0 4 43

Index Scan using index_fulfillments_on_shipment_id on fulfillments (cost=0.29..8.91 rows=1 width=16) (actual time=0.012..0.028 rows=4 loops=43)

  • Index Cond: (shipment_id = shipments.id)
  • Filter: ((order_type)::text = 'TransferOrder'::text)
23. 1.611 1.611 ↑ 1.0 1 179

Index Scan using locations_pkey on locations origin_locations (cost=0.29..8.31 rows=1 width=22) (actual time=0.009..0.009 rows=1 loops=179)

  • Index Cond: (id = shipments.origin_id)
24. 0.537 0.537 ↑ 1.0 1 179

Index Scan using locations_pkey on locations destination_locations (cost=0.29..8.31 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=179)

  • Index Cond: (id = shipments.destination_id)
25. 6.623 6.623 ↓ 3.0 6 179

Index Scan using index_fulfillments_on_shipment_id on fulfillments shipment_fulfillments (cost=0.29..8.91 rows=2 width=30) (actual time=0.004..0.037 rows=6 loops=179)

  • Index Cond: (shipment_id = shipments.id)
26. 32,681.019 49,521.822 ↓ 1.6 47,398 1,113

Merge Left Join (cost=35,310.26..36,137.56 rows=29,528 width=232) (actual time=0.666..44.494 rows=47,398 loops=1,113)

  • Merge Cond: ((all_orders.order_id = shipment_states.order_id) AND (all_orders.order_type = (shipment_states.order_type)::text))
27.          

CTE transfer_order_fields

28. 10.834 335.998 ↓ 4.6 26,472 1

Hash Right Join (cost=14,655.08..15,091.52 rows=5,777 width=232) (actual time=305.304..335.998 rows=26,472 loops=1)

  • Hash Cond: (midpoint_locations.order_id = ranked_by_earliest_arrival.id)
  • Join Filter: (midpoint_locations.midpoint_id <> ranked_by_earliest_arrival.destination_id)
  • Rows Removed by Join Filter: 7
29.          

CTE ranked_by_earliest_arrival

30. 35.815 164.851 ↓ 1.2 30,544 1

WindowAgg (cost=5,863.45..6,522.60 rows=26,366 width=103) (actual time=108.909..164.851 rows=30,544 loops=1)

31. 13.882 129.036 ↓ 1.2 30,544 1

Group (cost=5,863.45..6,061.20 rows=26,366 width=95) (actual time=108.893..129.036 rows=30,544 loops=1)

  • Group Key: tor.id, s.earliest_arrival_at
32. 40.144 115.154 ↓ 1.2 31,779 1

Sort (cost=5,863.45..5,929.37 rows=26,366 width=95) (actual time=108.866..115.154 rows=31,779 loops=1)

  • Sort Key: tor.id, s.earliest_arrival_at
  • Sort Method: external merge Disk: 3,048kB
33. 13.550 75.010 ↓ 1.2 31,779 1

Hash Left Join (cost=2,442.60..3,927.35 rows=26,366 width=95) (actual time=31.774..75.010 rows=31,779 loops=1)

  • Hash Cond: (f.shipment_id = s.id)
34. 19.617 41.063 ↓ 1.2 31,779 1

Hash Right Join (cost=947.24..2,362.76 rows=26,366 width=95) (actual time=11.025..41.063 rows=31,779 loops=1)

  • Hash Cond: (f.order_id = tor.id)
35. 10.502 10.502 ↑ 1.0 31,450 1

Seq Scan on fulfillments f (cost=0.00..1,332.70 rows=31,548 width=16) (actual time=0.013..10.502 rows=31,450 loops=1)

  • Filter: ((order_type)::text = 'TransferOrder'::text)
  • Rows Removed by Filter: 20,926
36. 7.295 10.944 ↑ 1.0 26,366 1

Hash (cost=617.66..617.66 rows=26,366 width=87) (actual time=10.944..10.944 rows=26,366 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 3,165kB
37. 3.649 3.649 ↑ 1.0 26,366 1

Seq Scan on transfer_orders tor (cost=0.00..617.66 rows=26,366 width=87) (actual time=0.013..3.649 rows=26,366 loops=1)

38. 10.991 20.397 ↑ 1.0 36,994 1

Hash (cost=1,032.94..1,032.94 rows=36,994 width=16) (actual time=20.396..20.397 rows=36,994 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,182kB
39. 9.406 9.406 ↑ 1.0 36,994 1

Seq Scan on shipments s (cost=0.00..1,032.94 rows=36,994 width=16) (actual time=0.008..9.406 rows=36,994 loops=1)

40.          

CTE midpoint_locations

41. 11.209 110.019 ↑ 1.6 5,372 1

Merge Join (cost=7,167.11..7,537.59 rows=8,841 width=32) (actual time=92.776..110.019 rows=5,372 loops=1)

  • Merge Cond: ((first_leg.id = second_leg.id) AND (first_leg.f_origin_id = second_leg.f_destination_id))
42.          

CTE fulfillment_locations

43. 18.316 58.426 ↓ 1.7 31,450 1

Sort (cost=3,697.80..3,744.82 rows=18,805 width=40) (actual time=54.992..58.426 rows=31,450 loops=1)

  • Sort Key: tor_1.id
  • Sort Method: quicksort Memory: 3,226kB
44. 15.849 40.110 ↓ 1.7 31,450 1

Hash Join (cost=947.24..2,362.76 rows=18,805 width=40) (actual time=14.004..40.110 rows=31,450 loops=1)

  • Hash Cond: (f_1.order_id = tor_1.id)
45. 10.481 10.481 ↑ 1.0 31,450 1

Seq Scan on fulfillments f_1 (cost=0.00..1,332.70 rows=31,548 width=24) (actual time=0.018..10.481 rows=31,450 loops=1)

  • Filter: ((order_type)::text = 'TransferOrder'::text)
  • Rows Removed by Filter: 20,926
46. 7.256 13.780 ↑ 1.0 26,366 1

Hash (cost=617.66..617.66 rows=26,366 width=24) (actual time=13.780..13.780 rows=26,366 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,698kB
47. 6.524 6.524 ↑ 1.0 26,366 1

Seq Scan on transfer_orders tor_1 (cost=0.00..617.66 rows=26,366 width=24) (actual time=0.007..6.524 rows=26,366 loops=1)

48. 14.369 81.352 ↓ 1.7 31,450 1

Sort (cost=1,711.14..1,758.16 rows=18,805 width=32) (actual time=78.333..81.352 rows=31,450 loops=1)

  • Sort Key: first_leg.id, first_leg.f_origin_id
  • Sort Method: quicksort Memory: 3,226kB
49. 66.983 66.983 ↓ 1.7 31,450 1

CTE Scan on fulfillment_locations first_leg (cost=0.00..376.10 rows=18,805 width=32) (actual time=54.996..66.983 rows=31,450 loops=1)

50. 12.212 17.458 ↓ 1.7 31,571 1

Sort (cost=1,711.14..1,758.16 rows=18,805 width=16) (actual time=14.432..17.458 rows=31,571 loops=1)

  • Sort Key: second_leg.id, second_leg.f_destination_id
  • Sort Method: quicksort Memory: 2,243kB
51. 5.246 5.246 ↓ 1.7 31,450 1

CTE Scan on fulfillment_locations second_leg (cost=0.00..376.10 rows=18,805 width=16) (actual time=0.002..5.246 rows=31,450 loops=1)

52. 112.670 112.670 ↑ 1.6 5,340 1

CTE Scan on midpoint_locations (cost=0.00..198.92 rows=8,797 width=16) (actual time=92.780..112.670 rows=5,340 loops=1)

  • Filter: (midpoint_id <> origin_id)
  • Rows Removed by Filter: 32
53. 11.760 212.494 ↓ 199.7 26,366 1

Hash (cost=593.24..593.24 rows=132 width=120) (actual time=212.494..212.494 rows=26,366 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,963kB
54. 200.734 200.734 ↓ 199.7 26,366 1

CTE Scan on ranked_by_earliest_arrival (cost=0.00..593.24 rows=132 width=120) (actual time=108.914..200.734 rows=26,366 loops=1)

  • Filter: (rank = 1)
  • Rows Removed by Filter: 4,178
55.          

CTE purchase_order_fields

56. 10.184 70.355 ↑ 1.0 20,926 1

Hash Left Join (cost=3,095.92..4,420.03 rows=20,926 width=244) (actual time=37.761..70.355 rows=20,926 loops=1)

  • Hash Cond: (f_2.shipment_id = s_1.id)
57. 10.695 39.869 ↑ 1.0 20,926 1

Hash Left Join (cost=1,600.55..2,869.72 rows=20,926 width=196) (actual time=17.147..39.869 rows=20,926 loops=1)

  • Hash Cond: (por.id = f_2.order_id)
58. 8.047 12.148 ↑ 1.0 20,926 1

Hash Left Join (cost=7.50..942.71 rows=20,926 width=188) (actual time=0.103..12.148 rows=20,926 loops=1)

  • Hash Cond: (por.vendor_id = v.id)
59. 4.019 4.019 ↑ 1.0 20,926 1

Seq Scan on purchase_orders por (cost=0.00..880.26 rows=20,926 width=188) (actual time=0.012..4.019 rows=20,926 loops=1)

60. 0.034 0.082 ↑ 1.0 200 1

Hash (cost=5.00..5.00 rows=200 width=16) (actual time=0.082..0.082 rows=200 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
61. 0.048 0.048 ↑ 1.0 200 1

Seq Scan on vendors v (cost=0.00..5.00 rows=200 width=16) (actual time=0.006..0.048 rows=200 loops=1)

62. 6.035 17.026 ↓ 1.0 20,926 1

Hash (cost=1,332.70..1,332.70 rows=20,828 width=16) (actual time=17.026..17.026 rows=20,926 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,232kB
63. 10.991 10.991 ↓ 1.0 20,926 1

Seq Scan on fulfillments f_2 (cost=0.00..1,332.70 rows=20,828 width=16) (actual time=0.007..10.991 rows=20,926 loops=1)

  • Filter: ((order_type)::text = 'PurchaseOrder'::text)
  • Rows Removed by Filter: 31,450
64. 10.773 20.302 ↑ 1.0 36,994 1

Hash (cost=1,032.94..1,032.94 rows=36,994 width=16) (actual time=20.302..20.302 rows=36,994 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,182kB
65. 9.529 9.529 ↑ 1.0 36,994 1

Seq Scan on shipments s_1 (cost=0.00..1,032.94 rows=36,994 width=16) (actual time=0.009..9.529 rows=36,994 loops=1)

66.          

CTE shipment_states

67. 34.652 126.040 ↓ 1.1 46,963 1

GroupAggregate (cost=6,940.02..8,016.68 rows=44,232 width=54) (actual time=83.382..126.040 rows=46,963 loops=1)

  • Group Key: f_3.order_id, f_3.order_type
68. 40.335 91.388 ↑ 1.0 52,376 1

Sort (cost=6,940.02..7,070.96 rows=52,376 width=30) (actual time=83.366..91.388 rows=52,376 loops=1)

  • Sort Key: f_3.order_id, f_3.order_type
  • Sort Method: external merge Disk: 2,088kB
69. 22.275 51.053 ↑ 1.0 52,376 1

Hash Left Join (cost=1,495.37..2,834.62 rows=52,376 width=30) (actual time=21.276..51.053 rows=52,376 loops=1)

  • Hash Cond: (f_3.shipment_id = s_2.id)
70. 7.541 7.541 ↑ 1.0 52,376 1

Seq Scan on fulfillments f_3 (cost=0.00..1,201.76 rows=52,376 width=30) (actual time=0.010..7.541 rows=52,376 loops=1)

71. 10.491 21.237 ↑ 1.0 36,994 1

Hash (cost=1,032.94..1,032.94 rows=36,994 width=16) (actual time=21.236..21.237 rows=36,994 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,440kB
72. 10.746 10.746 ↑ 1.0 36,994 1

Seq Scan on shipments s_2 (cost=0.00..1,032.94 rows=36,994 width=16) (actual time=0.006..10.746 rows=36,994 loops=1)

73.          

CTE all_orders

74. 7.233 476.164 ↓ 1.8 47,398 1

Append (cost=0.00..986.92 rows=26,703 width=232) (actual time=305.309..476.164 rows=47,398 loops=1)

75. 363.168 363.168 ↓ 4.6 26,472 1

CTE Scan on transfer_order_fields (cost=0.00..115.54 rows=5,777 width=232) (actual time=305.307..363.168 rows=26,472 loops=1)

76. 7.137 105.763 ↑ 1.0 20,926 1

Subquery Scan on *SELECT* 2 (cost=0.00..680.10 rows=20,926 width=232) (actual time=37.765..105.763 rows=20,926 loops=1)

77. 98.626 98.626 ↑ 1.0 20,926 1

CTE Scan on purchase_order_fields (cost=0.00..418.52 rows=20,926 width=228) (actual time=37.763..98.626 rows=20,926 loops=1)

78. 8,270.457 8,794.926 ↓ 1.8 47,398 1,113

Sort (cost=2,497.36..2,564.12 rows=26,703 width=48) (actual time=0.503..7.902 rows=47,398 loops=1,113)

  • Sort Key: all_orders.order_id, all_orders.order_type
  • Sort Method: external sort Disk: 1,680kB
79. 524.469 524.469 ↓ 1.8 47,398 1

CTE Scan on all_orders (cost=0.00..534.06 rows=26,703 width=48) (actual time=305.311..524.469 rows=47,398 loops=1)

80. 7,889.163 8,045.877 ↓ 1.1 47,069 1,113

Sort (cost=4,297.76..4,408.34 rows=44,232 width=40) (actual time=0.163..7.229 rows=47,069 loops=1,113)

  • Sort Key: shipment_states.order_id, shipment_states.order_type
  • Sort Method: external sort Disk: 1,664kB
81. 156.714 156.714 ↓ 1.1 46,963 1

CTE Scan on shipment_states (cost=0.00..884.64 rows=44,232 width=40) (actual time=83.387..156.714 rows=46,963 loops=1)

Planning time : 3.577 ms
Execution time : 54,856.246 ms