explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0au1

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 119.549 ↓ 0.0 0 1

Limit (cost=2,000.64..2,000.64 rows=1 width=24) (actual time=119.549..119.549 rows=0 loops=1)

2. 0.012 119.547 ↓ 0.0 0 1

Sort (cost=2,000.64..2,000.64 rows=1 width=24) (actual time=119.547..119.547 rows=0 loops=1)

  • Sort Key: w.lane, w.rack, w.shelf, w.slot
  • Sort Method: quicksort Memory: 25kB
3. 0.694 119.535 ↓ 0.0 0 1

Nested Loop Semi Join (cost=1,434.29..2,000.63 rows=1 width=24) (actual time=119.535..119.535 rows=0 loops=1)

  • Join Filter: (w.lane = warehouse_lanes.lane_id)
4. 8.110 118.841 ↓ 1,931.0 5,793 1

Nested Loop (cost=1,434.29..1,997.27 rows=3 width=24) (actual time=16.686..118.841 rows=5,793 loops=1)

5. 2.386 93.566 ↓ 2,145.6 17,165 1

Nested Loop (cost=1,433.87..1,993.67 rows=8 width=8) (actual time=10.124..93.566 rows=17,165 loops=1)

  • Join Filter: (o.product_id = b.product_id)
6. 0.000 22.189 ↓ 793.0 793 1

Nested Loop (cost=1,433.44..1,990.28 rows=1 width=12) (actual time=10.110..22.189 rows=793 loops=1)

7. 1.913 19.027 ↓ 793.0 793 1

Hash Join (cost=1,433.01..1,988.84 rows=1 width=8) (actual time=10.095..19.027 rows=793 loops=1)

  • Hash Cond: (o.order_id = ord.id)
8. 8.018 8.018 ↓ 7.5 13,440 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.019..8.018 rows=13,440 loops=1)

  • Index Cond: (status = 0)
9. 0.138 9.096 ↑ 4.9 533 1

Hash (cost=1,400.09..1,400.09 rows=2,599 width=8) (actual time=9.096..9.096 rows=533 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 53kB
10. 7.155 8.958 ↑ 4.9 533 1

Index Scan using idx_orders_status_index on orders ord (cost=386.73..1,400.09 rows=2,599 width=8) (actual time=4.322..8.958 rows=533 loops=1)

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

SubPlan (for Index Scan)

12. 0.000 0.360 ↓ 1.2 1,000 1

Index Scan using idx_max_orders_list_max_orders_id_index on max_orders_list (cost=132.83..156.81 rows=833 width=4) (actual time=0.022..0.360 rows=1,000 loops=1)

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

Initplan (for Index Scan)

14. 1.017 1.876 ↑ 1.0 1 1

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

15. 0.859 0.859 ↑ 1.0 7,324 1

Seq Scan on max_orders_id (cost=0.00..113.91 rows=7,391 width=4) (actual time=0.009..0.859 rows=7,324 loops=1)

16. 0.001 1.443 ↓ 0.0 0 1

Unique (cost=227.30..227.38 rows=9 width=8) (actual time=1.443..1.443 rows=0 loops=1)

17. 0.012 1.442 ↓ 0.0 0 1

Sort (cost=227.30..227.34 rows=16 width=8) (actual time=1.442..1.442 rows=0 loops=1)

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

Nested Loop (cost=199.75..226.98 rows=16 width=8) (actual time=1.430..1.430 rows=0 loops=1)

19. 0.006 1.430 ↓ 0.0 0 1

Bitmap Heap Scan on orders o_1 (cost=199.31..209.34 rows=9 width=8) (actual time=1.430..1.430 rows=0 loops=1)

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

BitmapAnd (cost=199.31..199.31 rows=9 width=0) (actual time=1.424..1.424 rows=0 loops=1)

21. 1.104 1.104 ↓ 1.2 4,269 1

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

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

Bitmap Index Scan on idex_orders_priority (cost=0.00..161.20 rows=15,129 width=0) (actual time=0.019..0.019 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. 3.172 3.172 ↑ 1.0 1 793

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

  • Index Cond: (id = o.product_id)
  • Heap Fetches: 791
25. 68.991 68.991 ↓ 3.1 22 793

Index Scan using idx_cdf1b2e94584665a on boxes b (cost=0.43..3.29 rows=7 width=8) (actual time=0.015..0.087 rows=22 loops=793)

  • Index Cond: (product_id = p.id)
  • Filter: (status = 4)
  • Rows Removed by Filter: 95
26. 17.165 17.165 ↓ 0.0 0 17,165

Index Scan using warehouse_map_id_idx on warehouse_map w (cost=0.42..0.44 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=17,165)

  • Index Cond: (id = b.slot_id)
  • Filter: (rack >= 13)
  • Rows Removed by Filter: 1
27. 0.000 0.000 ↓ 0.0 0 5,793

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

28. 0.020 0.020 ↓ 0.0 0 1

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

  • Filter: ((status = 1) AND (user_id = 1225))
  • Rows Removed by Filter: 87
Planning time : 3.137 ms
Execution time : 119.751 ms