explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5gKm

Settings
# exclusive inclusive rows x rows loops node
1. 177.086 840.591 ↓ 30.2 2,838 1

HashAggregate (cost=2,986.46..2,989.28 rows=94 width=68) (actual time=777.408..840.591 rows=2,838 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: 51968
2. 17.089 663.505 ↓ 555.9 57,812 1

Merge Left Join (cost=110.78..2,979.18 rows=104 width=68) (actual time=142.494..663.505 rows=57,812 loops=1)

  • Merge Cond: ((rl.product_id_)::text = (ss.product_id_)::text)
  • Join Filter: ((ss.site_id_)::text = (rl.site_id_)::text)
3. 48.200 509.229 ↓ 527.0 54,806 1

Nested Loop Left Join (cost=5.42..2,873.36 rows=104 width=55) (actual time=6.211..509.229 rows=54,806 loops=1)

4. 295.394 296.611 ↓ 527.0 54,806 1

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

  • Index Cond: (((site_id_)::text = 'AA0107'::text) AND ((facility_id_)::text = 'baa51851-df38-4a3f-9f13-05486c7b0dac'::text))
  • Filter: (NOT (hashed SubPlan 1))
5.          

SubPlan (forIndex Scan)

6. 1.217 1.217 ↓ 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=1.217..1.217 rows=0 loops=1)

  • Index Cond: (((site_id_)::text = 'AA0107'::text) AND ((product_feature_category_id_)::text = 'PARTROPROQEXCEPTIONSIGN'::text))
  • Filter: ((string_value_)::text = '1'::text)
7. 164.418 164.418 ↑ 1.0 1 54,806

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.003..0.003 rows=1 loops=54,806)

  • Index Cond: ((rl.product_id_)::text = (product_id_)::text)
8. 5.366 137.187 ↓ 170.4 6,305 1

Sort (cost=105.36..105.45 rows=37 width=62) (actual time=136.278..137.187 rows=6,305 loops=1)

  • Sort Key: ss.product_id_
  • Sort Method: quicksort Memory: 1079kB
9. 131.821 131.821 ↓ 170.4 6,305 1

Index Scan using product_stock_status_idx02 on product_stock_status ss (cost=0.56..104.40 rows=37 width=62) (actual time=1.064..131.821 rows=6,305 loops=1)

  • Index Cond: (((site_id_)::text = 'AA0107'::text) AND ((facility_id_)::text = 'baa51851-df38-4a3f-9f13-05486c7b0dac'::text) AND ((product_stock_status_type_id_)::text = ANY ('{C071ONHANDQTY,C071BOQTY,C071ONRECEIVINGQTY,C071EOONPURCHASEQTY,C071ROONPURCHASEQTY,C071ONTRANSFERINQTY}'::text[])))
Planning time : 28.378 ms
Execution time : 843.760 ms