#	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: 1,081
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: 4,096 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: 3,384
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 = 1,225)) Rows Removed by Filter: 86

Planning time	:	2.915 ms
Execution time	:	599.296 ms

Limit  (cost=1999.03..1999.03 rows=1 width=24) (actual time=599.143..599.151 rows=1 loops=1)
  ->  Sort  (cost=1999.03..1999.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
        ->  Nested Loop Semi Join  (cost=1433.88..1999.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
              ->  Nested Loop  (cost=1433.88..1995.66 rows=3 width=24) (actual time=16.504..598.719 rows=1218 loops=1)
                    ->  Nested Loop  (cost=1433.59..1993.38 rows=7 width=8) (actual time=16.412..473.923 rows=106398 loops=1)
                          Join Filter: (o.product_id = boxes.product_id)
                          ->  Nested Loop  (cost=1433.16..1990.00 rows=1 width=12) (actual time=16.398..21.416 rows=407 loops=1)
                                ->  Hash Join  (cost=1432.73..1988.56 rows=1 width=8) (actual time=16.384..19.416 rows=407 loops=1)
                                      Hash Cond: (o.order_id = ord.id)
                                      ->  Index Scan using idx_orders_item_status_index on orders_item o  (cost=0.43..549.56 rows=1785 width=12) (actual time=0.021..8.006 rows=14160 loops=1)
                                            Index Cond: (status = 0)
                                      ->  Hash  (cost=1399.81..1399.81 rows=2599 width=8) (actual time=9.398..9.398 rows=333 loops=1)
                                            Buckets: 4096  Batches: 1  Memory Usage: 46kB
                                            ->  Index Scan using idx_orders_status_index on orders ord  (cost=386.45..1399.81 rows=2599 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
                                                  SubPlan 2
                                                    ->  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=1000 loops=1)
                                                          Index Cond: (max_orders_id = $0)
                                                          InitPlan 1 (returns $0)
                                                            ->  Aggregate  (cost=132.39..132.40 rows=1 width=4) (actual time=1.773..1.774 rows=1 loops=1)
                                                                  ->  Seq Scan on max_orders_id  (cost=0.00..113.91 rows=7391 width=4) (actual time=0.008..0.846 rows=7323 loops=1)
                                                  SubPlan 3
                                                    ->  Unique  (cost=226.20..226.28 rows=9 width=8) (actual time=1.535..1.535 rows=0 loops=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
                                                                ->  Nested Loop  (cost=198.65..225.88 rows=16 width=8) (actual time=1.530..1.530 rows=0 loops=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))
                                                                            ->  BitmapAnd  (cost=198.21..198.21 rows=9 width=0) (actual time=1.523..1.523 rows=0 loops=1)
                                                                                  ->  Bitmap Index Scan on idx_orders_status_index  (cost=0.00..37.86 rows=3465 width=0) (actual time=1.165..1.165 rows=3725 loops=1)
                                                                                        Index Cond: (status = ANY ('{0,1}'::integer[]))
                                                                                  ->  Bitmap Index Scan on idex_orders_priority  (cost=0.00..160.10 rows=15129 width=0) (actual time=0.014..0.014 rows=33 loops=1)
                                                                                        Index Cond: (priority = 10)
                                                                      ->  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
                                ->  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
                          ->  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
                    ->  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=106398)
                          Index Cond: (id = boxes.slot_id)
                          Filter: (rack >= 13)
                          Rows Removed by Filter: 1
              ->  Materialize  (cost=0.00..3.31 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1218)
                    ->  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

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	0.928 ms	0.2 %
Bitmap Heap Scan	1	0.007 ms	0.0 %
Bitmap Index Scan	2	1.179 ms	0.2 %
BitmapAnd	1	0.344 ms	0.1 %
Hash	1	0.080 ms	0.0 %
Hash Join	1	2.012 ms	0.3 %
Index Only Scan	2	2.035 ms	0.3 %
Index Scan	5	563.443 ms	94.0 %
Limit	1	0.009 ms	0.0 %
Materialize	1	0.000 ms	0.0 %
Nested Loop	4	29.310 ms	4.9 %
Nested Loop Semi Join	1	0.381 ms	0.1 %
Seq Scan	2	0.871 ms	0.1 %
Sort	2	0.048 ms	0.0 %
Unique	1	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
boxes	1	441.595 ms	73.7 %
Index Scan	1	441.595 ms	100.0 %
max_orders_id	1	0.846 ms	0.1 %
Seq Scan	1	0.846 ms	100.0 %
max_orders_list	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
orders	2	7.451 ms	1.2 %
Bitmap Heap Scan	1	0.007 ms	0.1 %
Index Scan	1	7.444 ms	99.9 %
orders_item	2	8.006 ms	1.3 %
Index Only Scan	1	0.000 ms	0.0 %
Index Scan	1	8.006 ms	100.0 %
products	1	2.035 ms	0.3 %
Index Only Scan	1	2.035 ms	100.0 %
warehouse_lanes	1	0.025 ms	0.0 %
Seq Scan	1	0.025 ms	100.0 %
warehouse_map	1	106.398 ms	17.8 %
Index Scan	1	106.398 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MRVx

Color mode:

Visible columns:

Per node type stats

Per table stats