explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jokR : Optimization for: Optimization for: plan #PXpZ; plan #KcUg

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.024 8,043.550 ↓ 18.2 109 1

HashAggregate (cost=52,216.14..52,216.23 rows=6 width=35) (actual time=8,043.507..8,043.550 rows=109 loops=1)

  • Group Key: soitem.sales_order_id_
2. 0.408 8,042.526 ↓ 22.5 135 1

Nested Loop Left Join (cost=20.62..52,216.07 rows=6 width=35) (actual time=258.225..8,042.526 rows=135 loops=1)

3. 0.654 7,972.728 ↓ 22.5 135 1

Nested Loop (cost=20.07..52,201.07 rows=6 width=68) (actual time=257.380..7,972.728 rows=135 loops=1)

  • Join Filter: ((itemprogress.order_item_id_)::text = (item.order_item_id_)::text)
4. 19.201 7,788.339 ↓ 22.5 135 1

Nested Loop (cost=19.51..52,185.29 rows=6 width=100) (actual time=256.108..7,788.339 rows=135 loops=1)

5. 13.523 2,084.880 ↓ 1.0 9,458 1

Nested Loop (cost=18.95..25,355.70 rows=9,381 width=58) (actual time=210.942..2,084.880 rows=9,458 loops=1)

6. 459.508 459.508 ↓ 148.1 2,813 1

Index Scan using order_info_idx02 on order_info orderinfo (cost=0.56..7,173.96 rows=19 width=37) (actual time=13.183..459.508 rows=2,813 loops=1)

  • Index Cond: (((site_id_)::text = 'JA0004'::text) AND ((non_serial_item_flag_)::text = '1'::text) AND ((order_date_)::text >= '20190601'::text) AND ((order_date_)::text <= '20190701'::text))
  • Filter: ((delivery_facility_)::text = 'af2ff7d0-ece9-463f-9c62-52fe4a627520'::text)
7. 247.544 1,611.849 ↑ 164.7 3 2,813

Bitmap Heap Scan on sales_order_item soitem (cost=18.39..951.99 rows=494 width=58) (actual time=0.569..0.573 rows=3 loops=2,813)

  • Recheck Cond: ((sales_order_id_)::text = (orderinfo.order_id_)::text)
  • Heap Blocks: exact=2713
8. 1,364.305 1,364.305 ↑ 164.7 3 2,813

Bitmap Index Scan on sales_order_item_idx02 (cost=0.00..18.26 rows=494 width=0) (actual time=0.485..0.485 rows=3 loops=2,813)

  • Index Cond: ((sales_order_id_)::text = (orderinfo.order_id_)::text)
9. 5,684.258 5,684.258 ↓ 0.0 0 9,458

Index Scan using order_item_progress_idx01 on order_item_progress itemprogress (cost=0.56..2.85 rows=1 width=42) (actual time=0.601..0.601 rows=0 loops=9,458)

  • Index Cond: (((order_item_id_)::text = (soitem.order_item_id_)::text) AND ((order_item_progress_type_id_)::text = 'C041BACKORDERQTY'::text))
10. 183.735 183.735 ↑ 1.0 1 135

Index Scan using pk_retail_order_item on order_item item (cost=0.56..2.62 rows=1 width=79) (actual time=1.358..1.361 rows=1 loops=135)

  • Index Cond: ((order_item_id_)::text = (soitem.order_item_id_)::text)
11. 69.390 69.390 ↓ 0.0 0 135

Index Scan using product_stock_status_idx02 on product_stock_status stock (cost=0.55..2.49 rows=1 width=40) (actual time=0.510..0.514 rows=0 loops=135)

  • Index Cond: (((site_id_)::text = 'JA0004'::text) AND ((facility_id_)::text = 'af2ff7d0-ece9-463f-9c62-52fe4a627520'::text) AND ((product_id_)::text = (item.product_id_)::text) AND ((product_stock_status_type_id_)::text = 'C071ONHANDQTY'::text))
  • Filter: (quantity_ > 0::numeric)
  • Rows Removed by Filter: 1
Planning time : 1.926 ms
Execution time : 8,043.668 ms