explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MRVx

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 599.151 ↑ 1.0 1 1

Limit (cost=1,999.03..1,999.03 rows=1 width=24) (actual time=599.143..599.151 rows=1 loops=1)

2. 0.042 599.142 ↑ 1.0 1 1

Sort (cost=1,999.03..1,999.03 rows=1 width=24) (actual time=599.142..599.142 rows=1 loops=1)

  • Sort Key: w.lane, w.rack, w.shelf, w.slot
  • Sort Method: top-N heapsort Memory: 25kB
3. 0.381 599.100 ↓ 137.0 137 1

Nested Loop Semi Join (cost=1,433.88..1,999.02 rows=1 width=24) (actual time=93.902..599.100 rows=137 loops=1)

  • Join Filter: (w.lane = warehouse_lanes.lane_id)
  • Rows Removed by Join Filter: 1081
4. 18.398 598.719 ↓ 406.0 1,218 1

Nested Loop (cost=1,433.88..1,995.66 rows=3 width=24) (actual time=16.504..598.719 rows=1,218 loops=1)

5. 10.912 473.923 ↓ 15,199.7 106,398 1

Nested Loop (cost=1,433.59..1,993.38 rows=7 width=8) (actual time=16.412..473.923 rows=106,398 loops=1)

  • Join Filter: (o.product_id = boxes.product_id)
6. 0.000 21.416 ↓ 407.0 407 1

Nested Loop (cost=1,433.16..1,990.00 rows=1 width=12) (actual time=16.398..21.416 rows=407 loops=1)

7. 2.012 19.416 ↓ 407.0 407 1

Hash Join (cost=1,432.73..1,988.56 rows=1 width=8) (actual time=16.384..19.416 rows=407 loops=1)

  • Hash Cond: (o.order_id = ord.id)
8. 8.006 8.006 ↓ 7.9 14,160 1

Index Scan using idx_orders_item_status_index on orders_item o (cost=0.43..549.56 rows=1,785 width=12) (actual time=0.021..8.006 rows=14,160 loops=1)

  • Index Cond: (status = 0)
9. 0.080 9.398 ↑ 7.8 333 1

Hash (cost=1,399.81..1,399.81 rows=2,599 width=8) (actual time=9.398..9.398 rows=333 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 46kB
10. 7.444 9.318 ↑ 7.8 333 1

Index Scan using idx_orders_status_index on orders ord (cost=386.45..1,399.81 rows=2,599 width=8) (actual time=5.189..9.318 rows=333 loops=1)

  • Index Cond: (status = ANY ('{0,1}'::integer[]))
  • Filter: ((hashed SubPlan 2) OR (hashed SubPlan 3))
  • Rows Removed by Filter: 3384
11.          

SubPlan (for Index Scan)

12. 0.000 0.339 ↓ 1.1 1,000 1

Index Scan using idx_max_orders_list_max_orders_id_index on max_orders_list (cost=132.83..157.53 rows=874 width=4) (actual time=0.019..0.339 rows=1,000 loops=1)

  • Index Cond: (max_orders_id = $0)
13.          

Initplan (for Index Scan)

14. 0.928 1.774 ↑ 1.0 1 1

Aggregate (cost=132.39..132.40 rows=1 width=4) (actual time=1.773..1.774 rows=1 loops=1)

15. 0.846 0.846 ↑ 1.0 7,323 1

Seq Scan on max_orders_id (cost=0.00..113.91 rows=7,391 width=4) (actual time=0.008..0.846 rows=7,323 loops=1)

16. 0.000 1.535 ↓ 0.0 0 1

Unique (cost=226.20..226.28 rows=9 width=8) (actual time=1.535..1.535 rows=0 loops=1)

17. 0.006 1.536 ↓ 0.0 0 1

Sort (cost=226.20..226.24 rows=16 width=8) (actual time=1.535..1.536 rows=0 loops=1)

  • Sort Key: o_1.id
  • Sort Method: quicksort Memory: 25kB
18. 0.000 1.530 ↓ 0.0 0 1

Nested Loop (cost=198.65..225.88 rows=16 width=8) (actual time=1.530..1.530 rows=0 loops=1)

19. 0.007 1.530 ↓ 0.0 0 1

Bitmap Heap Scan on orders o_1 (cost=198.21..208.24 rows=9 width=8) (actual time=1.530..1.530 rows=0 loops=1)

  • Recheck Cond: ((status = ANY ('{0,1}'::integer[])) AND (priority = 10))
20. 0.344 1.523 ↓ 0.0 0 1

BitmapAnd (cost=198.21..198.21 rows=9 width=0) (actual time=1.523..1.523 rows=0 loops=1)

21. 1.165 1.165 ↓ 1.1 3,725 1

Bitmap Index Scan on idx_orders_status_index (cost=0.00..37.86 rows=3,465 width=0) (actual time=1.165..1.165 rows=3,725 loops=1)

  • Index Cond: (status = ANY ('{0,1}'::integer[]))
22. 0.014 0.014 ↑ 458.5 33 1

Bitmap Index Scan on idex_orders_priority (cost=0.00..160.10 rows=15,129 width=0) (actual time=0.014..0.014 rows=33 loops=1)

  • Index Cond: (priority = 10)
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_b1cee4b58d9f6d38 on orders_item oi (cost=0.43..1.85 rows=11 width=4) (never executed)

  • Index Cond: (order_id = o_1.id)
  • Heap Fetches: 0
24. 2.035 2.035 ↑ 1.0 1 407

Index Only Scan using products_pkey on products p (cost=0.42..1.43 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=407)

  • Index Cond: (id = o.product_id)
  • Heap Fetches: 405
25. 441.595 441.595 ↓ 37.3 261 407

Index Scan using idx_cdf1b2e94584665a on boxes (cost=0.43..3.29 rows=7 width=8) (actual time=0.135..1.085 rows=261 loops=407)

  • Index Cond: (product_id = p.id)
  • Filter: (status = 4)
  • Rows Removed by Filter: 560
26. 106.398 106.398 ↓ 0.0 0 106,398

Index Scan using warehouse_map_pkey on warehouse_map w (cost=0.29..0.32 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=106,398)

  • Index Cond: (id = boxes.slot_id)
  • Filter: (rack >= 13)
  • Rows Removed by Filter: 1
27. 0.000 0.000 ↑ 1.0 1 1,218

Materialize (cost=0.00..3.31 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1,218)

28. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on warehouse_lanes (cost=0.00..3.30 rows=1 width=4) (actual time=0.018..0.025 rows=1 loops=1)

  • Filter: ((status = 1) AND (user_id = 1225))
  • Rows Removed by Filter: 86
Planning time : 2.915 ms
Execution time : 599.296 ms