explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aqnD

Settings
# exclusive inclusive rows x rows loops node
1. 339.778 84,798.893 ↓ 13.4 1,256 1

HashAggregate (cost=2,940.95..2,943.77 rows=94 width=68) (actual time=84,731.189..84,798.893 rows=1,256 loops=1)

  • Group Key: rl.product_id_, rl.reorder_quantity_, rl.reorder_point_, ypi.sd_purchase_lot_
  • Filter: (((((((max(COALESCE(CASE WHEN ((ss.product_stock_status_type_id_)::text = 'C071ONHANDQTY'::text) THEN ss.quantity_ ELSE NULL::numeric END, 0::numeric)) + max(COALESCE(CASE WHEN ((ss.product_stock_status_type_id_)::text = 'C071BOQTY'::text) THEN ss.quantity_ ELSE NULL::numeric END, 0::numeric))) + max(COALESCE(CASE WHEN ((ss.product_stock_status_type_id_)::text = 'C071ONRECEIVINGQTY'::text) THEN ss.quantity_ ELSE NULL::numeric END, 0::numeric))) + max(COALESCE(CASE WHEN ((ss.product_stock_status_type_id_)::text = 'C071EOONPURCHASEQTY'::text) THEN ss.quantity_ ELSE NULL::numeric END, 0::numeric))) + max(COALESCE(CASE WHEN ((ss.product_stock_status_type_id_)::text = 'C071ROONPURCHASEQTY'::text) THEN ss.quantity_ ELSE NULL::numeric END, 0::numeric))) + max(COALESCE(CASE WHEN ((ss.product_stock_status_type_id_)::text = 'C071ONTRANSFERINQTY'::text) THEN ss.quantity_ ELSE NULL::numeric END, 0::numeric))) > 0::numeric) OR ((rl.reorder_quantity_ <> 1::numeric) AND (rl.reorder_point_ <> 0::numeric)))
  • Rows Removed by Filter: 53551
2. 64,997.634 84,459.115 ↓ 558.8 58,112 1

Nested Loop Left Join (cost=5.97..2,933.67 rows=104 width=68) (actual time=112.336..84,459.115 rows=58,112 loops=1)

  • Join Filter: (((ss.site_id_)::text = (rl.site_id_)::text) AND ((ss.product_id_)::text = (rl.product_id_)::text))
  • Rows Removed by Join Filter: 316450158
3. 83.454 1,320.364 ↓ 527.0 54,807 1

Nested Loop Left Join (cost=5.42..2,873.36 rows=104 width=55) (actual time=5.650..1,320.364 rows=54,807 loops=1)

4. 359.077 359.998 ↓ 527.0 54,807 1

Index Scan using reorder_guideline_idx02 on reorder_guideline rl (cost=5.01..2,429.34 rows=104 width=51) (actual time=5.632..359.998 rows=54,807 loops=1)

  • Index Cond: (((site_id_)::text = 'JBCB12'::text) AND ((facility_id_)::text = 'f33509aa-6279-43db-b869-e7d35cc4f694'::text))
  • Filter: (NOT (hashed SubPlan 1))
5.          

SubPlan (forIndex Scan)

6. 0.921 0.921 ↓ 0.0 0 1

Index Scan using product_feature_idx01 on product_feature profea (cost=0.42..4.44 rows=1 width=37) (actual time=0.921..0.921 rows=0 loops=1)

  • Index Cond: (((site_id_)::text = 'JBCB12'::text) AND ((product_feature_category_id_)::text = 'PARTROPROQEXCEPTIONSIGN'::text))
  • Filter: ((string_value_)::text = '1'::text)
7. 876.912 876.912 ↑ 1.0 1 54,807

Index Scan using index_yimm_product_info_01 on yimm_product_info ypi (cost=0.41..4.26 rows=1 width=41) (actual time=0.012..0.016 rows=1 loops=54,807)

  • Index Cond: ((rl.product_id_)::text = (product_id_)::text)
8. 18,039.620 18,141.117 ↓ 481.2 5,774 54,807

Materialize (cost=0.55..38.50 rows=12 width=62) (actual time=0.000..0.331 rows=5,774 loops=54,807)

9. 101.497 101.497 ↓ 481.2 5,774 1

Index Scan using product_stock_status_idx02 on product_stock_status ss (cost=0.55..38.44 rows=12 width=62) (actual time=0.589..101.497 rows=5,774 loops=1)

  • Index Cond: (((site_id_)::text = 'JBCB12'::text) AND ((facility_id_)::text = 'f33509aa-6279-43db-b869-e7d35cc4f694'::text))
  • Filter: ((product_stock_status_type_id_)::text = ANY ('{C071ONHANDQTY,C071BOQTY,C071ONRECEIVINGQTY,C071EOONPURCHASEQTY,C071ROONPURCHASEQTY,C071ONTRANSFERINQTY}'::text[]))
  • Rows Removed by Filter: 2569
Planning time : 2.215 ms
Execution time : 84,801.435 ms