explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q3wL

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

Limit (cost=2,000.19..2,000.19 rows=1 width=24) (actual time=188.481..188.489 rows=1 loops=1)

2. 0.102 188.480 ↑ 1.0 1 1

Sort (cost=2,000.19..2,000.19 rows=1 width=24) (actual time=188.480..188.480 rows=1 loops=1)

  • Sort Key: w.lane, w.rack, w.shelf, w.slot
  • Sort Method: top-N heapsort Memory: 25kB
3. 1.935 188.378 ↓ 409.0 409 1

Nested Loop Semi Join (cost=1,434.29..2,000.18 rows=1 width=24) (actual time=50.798..188.378 rows=409 loops=1)

  • Join Filter: (w.lane = warehouse_lanes.lane_id)
  • Rows Removed by Join Filter: 8298
4. 0.000 186.443 ↓ 2,902.3 8,707 1

Nested Loop (cost=1,434.29..1,996.82 rows=3 width=24) (actual time=9.154..186.443 rows=8,707 loops=1)

5. 3.857 141.086 ↓ 3,848.1 26,937 1

Nested Loop (cost=1,433.87..1,993.67 rows=7 width=8) (actual time=7.598..141.086 rows=26,937 loops=1)

  • Join Filter: (o.product_id = b.product_id)
6. 0.000 22.569 ↓ 1,170.0 1,170 1

Nested Loop (cost=1,433.44..1,990.28 rows=1 width=12) (actual time=7.586..22.569 rows=1,170 loops=1)

7. 2.083 17.904 ↓ 1,170.0 1,170 1

Hash Join (cost=1,433.01..1,988.84 rows=1 width=8) (actual time=7.575..17.904 rows=1,170 loops=1)

  • Hash Cond: (o.order_id = ord.id)
8. 8.283 8.283 ↓ 7.6 13,646 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.016..8.283 rows=13,646 loops=1)

  • Index Cond: (status = 0)
9. 0.120 7.538 ↑ 3.8 683 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 59kB
10. 6.199 7.418 ↑ 3.8 683 1

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

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

SubPlan (for Index Scan)

12. 0.000 0.281 ↓ 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.015..0.281 rows=1,000 loops=1)

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

Initplan (for Index Scan)

14. 0.761 1.443 ↑ 1.0 1 1

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

15. 0.682 0.682 ↑ 1.0 7,324 1

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

16. 0.001 0.938 ↓ 0.0 0 1

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

17. 0.008 0.937 ↓ 0.0 0 1

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

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

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

19. 0.005 0.929 ↓ 0.0 0 1

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

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

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

21. 0.655 0.655 ↓ 1.1 3,904 1

Bitmap Index Scan on idx_orders_status_index (cost=0.00..37.86 rows=3,465 width=0) (actual time=0.655..0.655 rows=3,904 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..161.20 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. 4.680 4.680 ↑ 1.0 1 1,170

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=1,170)

  • Index Cond: (id = o.product_id)
  • Heap Fetches: 1168
25. 114.660 114.660 ↓ 3.3 23 1,170

Index Scan using idx_cdf1b2e94584665a on boxes b (cost=0.43..3.29 rows=7 width=8) (actual time=0.014..0.098 rows=23 loops=1,170)

  • Index Cond: (product_id = p.id)
  • Filter: (status = 4)
  • Rows Removed by Filter: 109
26. 53.874 53.874 ↓ 0.0 0 26,937

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.002 rows=0 loops=26,937)

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

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

28. 0.027 0.027 ↑ 1.0 1 1

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

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