explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tPhn : 1

Settings
# exclusive inclusive rows x rows loops node
1. 334.557 113,424.858 ↓ 17,719.7 53,159 1

Sort (cost=2,197.52..2,197.53 rows=3 width=886) (actual time=113,409.178..113,424.858 rows=53,159 loops=1)

  • Sort Key: (COALESCE(pr.product_code_, ''::character varying))
  • Sort Method: external merge Disk: 4480kB
2. 425.077 113,090.301 ↓ 17,719.7 53,159 1

Nested Loop Left Join (cost=22.20..2,197.50 rows=3 width=886) (actual time=32.217..113,090.301 rows=53,159 loops=1)

3. 64,494.390 111,867.839 ↓ 17,719.7 53,159 1

Nested Loop Left Join (cost=11.66..129.04 rows=3 width=964) (actual time=32.070..111,867.839 rows=53,159 loops=1)

  • Join Filter: ((pcaver.product_id_)::text = (pr.product_id_)::text)
  • Rows Removed by Join Filter: 434726124
4. 52.401 16,966.501 ↓ 17,719.7 53,159 1

Nested Loop (cost=11.24..120.56 rows=3 width=944) (actual time=4.772..16,966.501 rows=53,159 loops=1)

5. 91.282 16,701.464 ↓ 17,719.7 53,159 1

Nested Loop (cost=10.97..119.42 rows=3 width=944) (actual time=4.756..16,701.464 rows=53,159 loops=1)

6. 3,295.208 16,291.228 ↓ 53,159.0 53,159 1

Nested Loop Left Join (cost=10.55..105.75 rows=1 width=944) (actual time=4.703..16,291.228 rows=53,159 loops=1)

  • Join Filter: ((pclastcost.product_id_)::text = (pr.product_id_)::text)
  • Rows Removed by Join Filter: 18605300
7. 68.885 2,789.492 ↓ 53,159.0 53,159 1

Nested Loop Left Join (cost=10.14..97.31 rows=1 width=924) (actual time=3.820..2,789.492 rows=53,159 loops=1)

8. 131.767 2,401.653 ↓ 53,159.0 53,159 1

Nested Loop Left Join (cost=9.86..96.79 rows=1 width=1,104) (actual time=3.760..2,401.653 rows=53,159 loops=1)

9. 96.491 1,313.024 ↓ 53,159.0 53,159 1

Nested Loop (cost=5.13..25.08 rows=1 width=908) (actual time=3.692..1,313.024 rows=53,159 loops=1)

10. 184.229 1,057.056 ↓ 53,159.0 53,159 1

Nested Loop (cost=4.86..24.70 rows=1 width=908) (actual time=3.633..1,057.056 rows=53,159 loops=1)

11. 128.601 128.601 ↓ 53,159.0 53,159 1

Index Scan using product_index_03 on product pr (cost=0.41..8.43 rows=1 width=712) (actual time=3.506..128.601 rows=53,159 loops=1)

  • Index Cond: (((site_id_)::text = '0000'::text) AND ((product_classification_id_)::text = 'C059PART'::text))
12. 212.636 744.226 ↑ 3.0 1 53,159

Bitmap Heap Scan on product_category_info pcilarge (cost=4.45..16.23 rows=3 width=196) (actual time=0.014..0.014 rows=1 loops=53,159)

  • Recheck Cond: (((product_id_)::text = (pr.product_id_)::text) AND ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text))
  • Heap Blocks: exact=53159
13. 531.590 531.590 ↑ 3.0 1 53,159

Bitmap Index Scan on product_category_info_idx01 (cost=0.00..4.45 rows=3 width=0) (actual time=0.010..0.010 rows=1 loops=53,159)

  • Index Cond: (((product_id_)::text = (pr.product_id_)::text) AND ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text))
14. 159.477 159.477 ↑ 1.0 1 53,159

Index Scan using pk_product_category on product_category_setting pcslarge (cost=0.27..0.37 rows=1 width=196) (actual time=0.003..0.003 rows=1 loops=53,159)

  • Index Cond: ((product_category_id_)::text = (pcilarge.product_category_id_)::text)
15. 212.636 956.862 ↑ 17.0 1 53,159

Bitmap Heap Scan on product_abc_info prabc (cost=4.72..71.55 rows=17 width=294) (actual time=0.018..0.018 rows=1 loops=53,159)

  • Recheck Cond: (((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pr.product_id_)::text))
  • Heap Blocks: exact=53159
16. 744.226 744.226 ↑ 17.0 1 53,159

Bitmap Index Scan on product_abc_info_idx01 (cost=0.00..4.72 rows=17 width=0) (actual time=0.014..0.014 rows=1 loops=53,159)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pr.product_id_)::text))
17. 318.954 318.954 ↑ 1.0 1 53,159

Index Scan using pk_abc_definition_info on abc_definition_info abc (cost=0.28..0.50 rows=1 width=212) (actual time=0.005..0.006 rows=1 loops=53,159)

  • Index Cond: ((abc_id_)::text = (prabc.abc_id_)::text)
  • Filter: (((site_id_)::text = 'AA101'::text) AND ((site_id_)::text = (prabc.site_id_)::text))
18. 10,206.528 10,206.528 ↓ 350.0 350 53,159

Index Scan using product_cost_idx04 on product_cost pclastcost (cost=0.41..8.43 rows=1 width=118) (actual time=0.009..0.192 rows=350 loops=53,159)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034RECEIVECOST'::text))
19. 318.954 318.954 ↑ 3.0 1 53,159

Index Only Scan using product_category_info_idx01 on product_category_info pcimiddle (cost=0.42..13.64 rows=3 width=196) (actual time=0.005..0.006 rows=1 loops=53,159)

  • Index Cond: ((product_id_ = (pcilarge.product_id_)::text) AND (product_category_type_id_ = 'PARTMIDDLEGROUP'::text))
  • Heap Fetches: 53159
20. 212.636 212.636 ↑ 1.0 1 53,159

Index Scan using pk_product_category on product_category_setting pcsmiddle (cost=0.27..0.37 rows=1 width=196) (actual time=0.003..0.004 rows=1 loops=53,159)

  • Index Cond: ((product_category_id_)::text = (pcimiddle.product_category_id_)::text)
21. 30,380.912 30,406.948 ↓ 8,178.0 8,178 53,159

Materialize (cost=0.41..8.44 rows=1 width=118) (actual time=0.000..0.572 rows=8,178 loops=53,159)

22. 26.036 26.036 ↓ 8,178.0 8,178 1

Index Scan using product_cost_idx04 on product_cost pcaver (cost=0.41..8.43 rows=1 width=118) (actual time=0.184..26.036 rows=8,178 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
23. 265.795 797.385 ↑ 274.0 1 53,159

Bitmap Heap Scan on yimm_product_info ypr (cost=10.54..686.75 rows=274 width=118) (actual time=0.015..0.015 rows=1 loops=53,159)

  • Recheck Cond: ((product_id_)::text = (pr.product_id_)::text)
  • Heap Blocks: exact=53159
24. 531.590 531.590 ↑ 274.0 1 53,159

Bitmap Index Scan on index_yimm_product_info_01 (cost=0.00..10.47 rows=274 width=0) (actual time=0.010..0.010 rows=1 loops=53,159)

  • Index Cond: ((product_id_)::text = (pr.product_id_)::text)
Planning time : 34.449 ms
Execution time : 113,430.948 ms