explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4to : 3

Settings
# exclusive inclusive rows x rows loops node
1. 20.325 2,600.239 ↓ 17,719.7 53,159 1

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

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

CTE main

3. 187.789 2,510.909 ↓ 17,719.7 53,159 1

Sort (cost=801.62..801.63 rows=3 width=944) (actual time=2,486.714..2,510.909 rows=53,159 loops=1)

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

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

5. 48.368 2,123.875 ↓ 17,719.7 53,159 1

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

6. 50.431 1,862.871 ↓ 53,159.0 53,159 1

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

7. 28.414 1,493.486 ↓ 53,159.0 53,159 1

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

8. 86.434 1,252.436 ↓ 53,159.0 53,159 1

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

9. 21.630 687.571 ↓ 53,159.0 53,159 1

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

10. 96.772 506.464 ↓ 53,159.0 53,159 1

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

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

  • Index Cond: (((site_id_)::text = '0000'::text) AND ((product_classification_id_)::text = 'C059PART'::text))
12. 106.318 372.113 ↑ 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.007..0.007 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. 265.795 265.795 ↑ 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.005..0.005 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.002..0.003 rows=1 loops=53,159)

  • Index Cond: ((product_category_id_)::text = (pcilarge.product_category_id_)::text)
15. 106.318 478.431 ↑ 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.009..0.009 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. 372.113 372.113 ↑ 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.007..0.007 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.003..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 318.954 ↑ 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.005..0.006 rows=1 loops=53,159)

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

  • Index Cond: ((product_id_)::text = (pr.product_id_)::text)
20. 212.636 212.636 ↑ 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.004..0.004 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.002..0.003 rows=1 loops=53,159)

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

CTE pclastcost

23. 0.353 0.353 ↓ 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.042..0.353 rows=350 loops=1)

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

CTE pcaver

25. 7.717 7.717 ↓ 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.029..7.717 rows=8,178 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
26. 17.716 2,565.505 ↓ 17,719.7 53,159 1

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

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

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

28. 0.124 0.615 ↓ 350.0 350 1

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

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

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

30. 2.835 14.409 ↓ 8,178.0 8,178 1

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

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

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

Planning time : 4.012 ms
Execution time : 2,606.306 ms