explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DOEa : 2

Settings
# exclusive inclusive rows x rows loops node
1. 19.336 3,139.607 ↓ 17,719.7 53,159 1

Hash Left Join (cost=818.56..818.67 rows=3 width=260) (actual time=3,046.205..3,139.607 rows=53,159 loops=1)

  • Hash Cond: ((main.product_id_)::text = (pcaver.product_id_)::text)
2.          

CTE main

3. 195.373 3,052.075 ↓ 17,719.7 53,159 1

Sort (cost=801.62..801.63 rows=3 width=944) (actual time=3,030.655..3,052.075 rows=53,159 loops=1)

  • Sort Key: (COALESCE(pr.product_code_, ''::character varying))
  • Sort Method: external merge Disk: 6056kB
4. 89.552 2,856.702 ↓ 17,719.7 53,159 1

Nested Loop (cost=21.37..801.60 rows=3 width=944) (actual time=0.486..2,856.702 rows=53,159 loops=1)

5. 28.716 2,607.673 ↓ 17,719.7 53,159 1

Nested Loop (cost=21.10..800.46 rows=3 width=944) (actual time=0.464..2,607.673 rows=53,159 loops=1)

6. 73.733 2,260.003 ↓ 53,159.0 53,159 1

Nested Loop Left Join (cost=20.68..786.79 rows=1 width=944) (actual time=0.436..2,260.003 rows=53,159 loops=1)

7. 63.887 1,814.157 ↓ 53,159.0 53,159 1

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

8. 105.538 1,537.634 ↓ 53,159.0 53,159 1

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

9. 64.796 847.347 ↓ 53,159.0 53,159 1

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

10. 147.648 623.074 ↓ 53,159.0 53,159 1

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

11. 50.154 50.154 ↓ 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=0.127..50.154 rows=53,159 loops=1)

  • Index Cond: (((site_id_)::text = '0000'::text) AND ((product_classification_id_)::text = 'C059PART'::text))
12. 106.318 425.272 ↑ 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.008..0.008 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. 318.954 318.954 ↑ 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.006..0.006 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. 106.318 584.749 ↑ 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.011..0.011 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. 478.431 478.431 ↑ 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.009..0.009 rows=1 loops=53,159)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pr.product_id_)::text))
17. 212.636 212.636 ↑ 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.004..0.004 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. 106.318 372.113 ↑ 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.006..0.007 rows=1 loops=53,159)

  • Recheck Cond: ((product_id_)::text = (pr.product_id_)::text)
  • Heap Blocks: exact=53159
19. 265.795 265.795 ↑ 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.005..0.005 rows=1 loops=53,159)

  • Index Cond: ((product_id_)::text = (pr.product_id_)::text)
20. 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
21. 159.477 159.477 ↑ 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.003 rows=1 loops=53,159)

  • Index Cond: ((product_category_id_)::text = (pcimiddle.product_category_id_)::text)
22.          

CTE pclastcost

23. 0.432 0.432 ↓ 350.0 350 1

Index Scan using product_cost_idx04 on product_cost (cost=0.41..8.43 rows=1 width=118) (actual time=0.038..0.432 rows=350 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034RECEIVECOST'::text))
24.          

CTE pcaver

25. 8.012 8.012 ↓ 8,178.0 8,178 1

Index Scan using product_cost_idx04 on product_cost pclastcost_1 (cost=0.41..8.43 rows=1 width=118) (actual time=0.030..8.012 rows=8,178 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
26. 17.696 3,105.441 ↓ 17,719.7 53,159 1

Hash Left Join (cost=0.03..0.11 rows=3 width=326) (actual time=3,031.360..3,105.441 rows=53,159 loops=1)

  • Hash Cond: ((main.product_id_)::text = (pclastcost.product_id_)::text)
27. 3,087.085 3,087.085 ↓ 17,719.7 53,159 1

CTE Scan on main (cost=0.00..0.06 rows=3 width=294) (actual time=3,030.664..3,087.085 rows=53,159 loops=1)

28. 0.080 0.660 ↓ 350.0 350 1

Hash (cost=0.02..0.02 rows=1 width=130) (actual time=0.660..0.660 rows=350 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
29. 0.580 0.580 ↓ 350.0 350 1

CTE Scan on pclastcost (cost=0.00..0.02 rows=1 width=130) (actual time=0.042..0.580 rows=350 loops=1)

30. 2.893 14.830 ↓ 8,178.0 8,178 1

Hash (cost=0.02..0.02 rows=1 width=130) (actual time=14.830..14.830 rows=8,178 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 679kB
31. 11.937 11.937 ↓ 8,178.0 8,178 1

CTE Scan on pcaver (cost=0.00..0.02 rows=1 width=130) (actual time=0.030..11.937 rows=8,178 loops=1)