explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ULSp

Settings
# exclusive inclusive rows x rows loops node
1. 0.940 1,090.214 ↓ 626.0 1,252 1

Unique (cost=2,590.83..2,590.89 rows=2 width=241) (actual time=1,089.188..1,090.214 rows=1,252 loops=1)

2. 1.228 1,089.274 ↓ 655.5 1,311 1

Sort (cost=2,590.83..2,590.84 rows=2 width=241) (actual time=1,089.187..1,089.274 rows=1,311 loops=1)

  • Sort Key: rl.product_id_, rl.reorder_quantity_, rl.reorder_point_, (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))), ypi.sd_purchase_lot_
  • Sort Method: quicksort Memory: 233kB
3. 0.130 1,088.046 ↓ 655.5 1,311 1

Append (cost=2,439.46..2,590.82 rows=2 width=241) (actual time=872.151..1,088.046 rows=1,311 loops=1)

4. 1.299 872.188 ↓ 59.0 59 1

HashAggregate (cost=2,439.46..2,439.47 rows=1 width=68) (actual time=872.151..872.188 rows=59 loops=1)

  • Group Key: rl.product_id_, rl.reorder_quantity_, rl.reorder_point_, ypi.sd_purchase_lot_
5. 0.343 870.889 ↓ 245.0 245 1

Nested Loop Left Join (cost=5.97..2,439.42 rows=1 width=68) (actual time=583.219..870.889 rows=245 loops=1)

  • Join Filter: ((rl.site_id_)::text = 'JBCB12'::text)
6. 0.260 683.339 ↓ 59.0 59 1

Nested Loop Left Join (cost=5.42..2,434.82 rows=1 width=92) (actual time=574.057..683.339 rows=59 loops=1)

7. 680.114 682.253 ↓ 59.0 59 1

Index Scan using reorder_guideline_idx02 on reorder_guideline rl (cost=5.01..2,430.38 rows=1 width=88) (actual time=574.035..682.253 rows=59 loops=1)

  • Index Cond: (((site_id_)::text = 'JBCB12'::text) AND ((facility_id_)::text = 'f33509aa-6279-43db-b869-e7d35cc4f694'::text))
  • Filter: ((NOT (hashed SubPlan 2)) AND (reorder_quantity_ <> 1::numeric) AND (reorder_point_ <> 0::numeric))
  • Rows Removed by Filter: 54748
8.          

SubPlan (forIndex Scan)

9. 2.139 2.139 ↓ 0.0 0 1

Index Scan using product_feature_idx01 on product_feature profea_1 (cost=0.42..4.44 rows=1 width=37) (actual time=2.139..2.139 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)
10. 0.826 0.826 ↑ 1.0 1 59

Index Scan using index_yimm_product_info_01 on yimm_product_info ypi (cost=0.41..4.43 rows=1 width=41) (actual time=0.013..0.014 rows=1 loops=59)

  • Index Cond: ((rl.product_id_)::text = (product_id_)::text)
11. 187.207 187.207 ↓ 4.0 4 59

Index Scan using product_stock_status_idx02 on product_stock_status ss (cost=0.55..4.59 rows=1 width=93) (actual time=2.903..3.173 rows=4 loops=59)

  • Index Cond: (((site_id_)::text = 'JBCB12'::text) AND ((facility_id_)::text = (rl.facility_id_)::text) AND ((facility_id_)::text = 'f33509aa-6279-43db-b869-e7d35cc4f694'::text) AND ((product_id_)::text = (rl.product_id_)::text))
  • Filter: ((product_stock_status_type_id_)::text = ANY ('{C071ONHANDQTY,C071BOQTY,C071ONRECEIVINGQTY,C071EOONPURCHASEQTY,C071ROONPURCHASEQTY,C071ONTRANSFERINQTY}'::text[]))
  • Rows Removed by Filter: 2
12. 18.567 215.728 ↓ 1,252.0 1,252 1

HashAggregate (cost=151.29..151.31 rows=1 width=68) (actual time=212.180..215.728 rows=1,252 loops=1)

  • Group Key: rl_1.product_id_, rl_1.reorder_quantity_, rl_1.reorder_point_, ypi_1.sd_purchase_lot_
  • Filter: ((((((max(COALESCE(CASE WHEN ((ss_1.product_stock_status_type_id_)::text = 'C071ONHANDQTY'::text) THEN ss_1.quantity_ ELSE NULL::numeric END, 0::numeric)) + max(COALESCE(CASE WHEN ((ss_1.product_stock_status_type_id_)::text = 'C071BOQTY'::text) THEN ss_1.quantity_ ELSE NULL::numeric END, 0::numeric))) + max(COALESCE(CASE WHEN ((ss_1.product_stock_status_type_id_)::text = 'C071ONRECEIVINGQTY'::text) THEN ss_1.quantity_ ELSE NULL::numeric END, 0::numeric))) + max(COALESCE(CASE WHEN ((ss_1.product_stock_status_type_id_)::text = 'C071EOONPURCHASEQTY'::text) THEN ss_1.quantity_ ELSE NULL::numeric END, 0::numeric))) + max(COALESCE(CASE WHEN ((ss_1.product_stock_status_type_id_)::text = 'C071ROONPURCHASEQTY'::text) THEN ss_1.quantity_ ELSE NULL::numeric END, 0::numeric))) + max(COALESCE(CASE WHEN ((ss_1.product_stock_status_type_id_)::text = 'C071ONTRANSFERINQTY'::text) THEN ss_1.quantity_ ELSE NULL::numeric END, 0::numeric))) > 0::numeric)
  • Rows Removed by Filter: 903
13. 1.904 197.161 ↓ 5,482.0 5,482 1

Nested Loop Left Join (cost=5.97..151.22 rows=1 width=68) (actual time=1.489..197.161 rows=5,482 loops=1)

14. 2.686 162.365 ↓ 5,482.0 5,482 1

Nested Loop (cost=5.56..146.95 rows=1 width=64) (actual time=1.477..162.365 rows=5,482 loops=1)

15. 124.903 124.903 ↓ 483.0 5,796 1

Index Scan using product_stock_status_idx02 on product_stock_status ss_1 (cost=0.55..38.44 rows=12 width=93) (actual time=1.388..124.903 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
16. 34.765 34.776 ↑ 1.0 1 5,796

Index Scan using reorder_guideline_idx02 on reorder_guideline rl_1 (cost=5.01..9.03 rows=1 width=82) (actual time=0.005..0.006 rows=1 loops=5,796)

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

SubPlan (forIndex Scan)

18. 0.011 0.011 ↓ 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.011..0.011 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)
19. 32.892 32.892 ↑ 1.0 1 5,482

Index Scan using index_yimm_product_info_01 on yimm_product_info ypi_1 (cost=0.41..4.26 rows=1 width=41) (actual time=0.005..0.006 rows=1 loops=5,482)

  • Index Cond: ((rl_1.product_id_)::text = (product_id_)::text)
Planning time : 2.594 ms
Execution time : 1,090.533 ms