explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C8P1

Settings
# exclusive inclusive rows x rows loops node
1. 391.044 90,640.571 ↓ 13.3 1,252 1

HashAggregate (cost=2,944.07..2,946.89 rows=94 width=68) (actual time=90,562.714..90,640.571 rows=1,252 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: 53555
2. 71,767.164 90,249.527 ↓ 559.0 58,134 1

Nested Loop Left Join (cost=5.97..2,936.79 rows=104 width=68) (actual time=12.512..90,249.527 rows=58,134 loops=1)

  • Join Filter: (((rl.site_id_)::text = 'JBCB12'::text) AND ((ss.facility_id_)::text = (rl.facility_id_)::text) AND ((ss.product_id_)::text = (rl.product_id_)::text))
  • Rows Removed by Join Filter: 317655890
3. 109.688 615.281 ↓ 527.0 54,807 1

Nested Loop Left Join (cost=5.42..2,873.36 rows=104 width=92) (actual time=0.050..615.281 rows=54,807 loops=1)

4. 176.742 176.751 ↓ 527.0 54,807 1

Index Scan using reorder_guideline_idx02 on reorder_guideline rl (cost=5.01..2,429.34 rows=104 width=88) (actual time=0.042..176.751 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.009 0.009 ↓ 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.009..0.009 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. 328.842 328.842 ↑ 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.006..0.006 rows=1 loops=54,807)

  • Index Cond: ((rl.product_id_)::text = (product_id_)::text)
8. 17,857.658 17,867.082 ↓ 483.0 5,796 54,807

Materialize (cost=0.55..38.50 rows=12 width=93) (actual time=0.000..0.326 rows=5,796 loops=54,807)

9. 9.424 9.424 ↓ 483.0 5,796 1

Index Scan using product_stock_status_idx02 on product_stock_status ss (cost=0.55..38.44 rows=12 width=93) (actual time=0.018..9.424 rows=5,796 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: 2575
Planning time : 1.370 ms
Execution time : 90,645.155 ms