explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VUeh : 1

Settings
# exclusive inclusive rows x rows loops node
1. 10.525 3,725.159 ↑ 2.0 59 1

HashAggregate (cost=736.31..737.81 rows=120 width=960) (actual time=3,725.125..3,725.159 rows=59 loops=1)

  • Group Key: pff.site_id_, fi.facility_code_, abcinfo.abc_type_, procatsettingm.category_code_, procatsettingm.description_, procatsettingl.category_code_, procatsettingl.description_
2. 4.041 3,714.634 ↓ 7.3 878 1

Hash Left Join (cost=26.43..733.01 rows=120 width=960) (actual time=42.048..3,714.634 rows=878 loops=1)

  • Hash Cond: ((procatm.product_category_id_)::text = (procatsettingm.product_category_id_)::text)
3. 2.900 3,710.104 ↓ 7.3 878 1

Nested Loop Left Join (cost=15.32..720.26 rows=120 width=922) (actual time=41.468..3,710.104 rows=878 loops=1)

4. 4.200 3,697.546 ↓ 7.3 878 1

Nested Loop Left Join (cost=15.04..659.38 rows=120 width=1,004) (actual time=41.339..3,697.546 rows=878 loops=1)

5. 3.856 3,653.836 ↓ 109.8 878 1

Nested Loop Left Join (cost=10.27..84.97 rows=8 width=1,004) (actual time=41.159..3,653.836 rows=878 loops=1)

6. 3.308 3,639.444 ↓ 292.7 878 1

Nested Loop Left Join (cost=5.82..36.18 rows=3 width=906) (actual time=41.129..3,639.444 rows=878 loops=1)

7. 6.514 3,628.234 ↓ 292.7 878 1

Nested Loop Left Join (cost=5.55..35.04 rows=3 width=868) (actual time=41.034..3,628.234 rows=878 loops=1)

8. 4.221 3,587.478 ↓ 878.0 878 1

Nested Loop Left Join (cost=1.10..18.78 rows=1 width=770) (actual time=40.865..3,587.478 rows=878 loops=1)

9. 19.600 3,576.233 ↓ 878.0 878 1

Nested Loop (cost=0.96..17.01 rows=1 width=830) (actual time=40.761..3,576.233 rows=878 loops=1)

10. 40.093 40.093 ↓ 8,178.0 8,178 1

Index Scan using product_cost_idx04 on product_cost (cost=0.41..8.43 rows=1 width=216) (actual time=0.173..40.093 rows=8,178 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
11. 3,516.540 3,516.540 ↓ 0.0 0 8,178

Index Scan using product_facility_feature_idx02 on product_facility_feature pff (cost=0.55..8.57 rows=1 width=810) (actual time=0.427..0.430 rows=0 loops=8,178)

  • Index Cond: (((product_id_)::text = (product_cost.product_id_)::text) AND ((site_id_)::text = 'AA101'::text) AND ((product_feature_category_id_)::text = 'PARTSAVERAGEDEMAND'::text))
12. 7.024 7.024 ↑ 1.0 1 878

Index Scan using pk_facility_info on facility_info fi (cost=0.14..1.76 rows=1 width=136) (actual time=0.007..0.008 rows=1 loops=878)

  • Index Cond: ((facility_id_)::text = (pff.facility_id_)::text)
13. 13.170 34.242 ↑ 3.0 1 878

Bitmap Heap Scan on product_category_info procatl (cost=4.45..16.23 rows=3 width=196) (actual time=0.039..0.039 rows=1 loops=878)

  • Recheck Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text))
  • Heap Blocks: exact=878
14. 21.072 21.072 ↑ 3.0 1 878

Bitmap Index Scan on product_category_info_idx01 (cost=0.00..4.45 rows=3 width=0) (actual time=0.024..0.024 rows=1 loops=878)

  • Index Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text))
15. 7.902 7.902 ↑ 1.0 1 878

Index Scan using pk_product_category on product_category_setting procatsettingl (cost=0.27..0.37 rows=1 width=234) (actual time=0.009..0.009 rows=1 loops=878)

  • Index Cond: ((procatl.product_category_id_)::text = (product_category_id_)::text)
16. 3.512 10.536 ↑ 3.0 1 878

Bitmap Heap Scan on product_category_info procatm (cost=4.45..16.23 rows=3 width=196) (actual time=0.011..0.012 rows=1 loops=878)

  • Recheck Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text))
  • Heap Blocks: exact=878
17. 7.024 7.024 ↑ 3.0 1 878

Bitmap Index Scan on product_category_info_idx01 (cost=0.00..4.45 rows=3 width=0) (actual time=0.008..0.008 rows=1 loops=878)

  • Index Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text))
18. 12.292 39.510 ↑ 17.0 1 878

Bitmap Heap Scan on product_abc_info proabc (cost=4.77..71.63 rows=17 width=294) (actual time=0.044..0.045 rows=1 loops=878)

  • Recheck Cond: (((site_id_)::text = (pff.site_id_)::text) AND ((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pff.product_id_)::text))
  • Heap Blocks: exact=878
19. 27.218 27.218 ↑ 17.0 1 878

Bitmap Index Scan on product_abc_info_idx01 (cost=0.00..4.76 rows=17 width=0) (actual time=0.031..0.031 rows=1 loops=878)

  • Index Cond: (((site_id_)::text = (pff.site_id_)::text) AND ((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pff.product_id_)::text))
20. 9.658 9.658 ↑ 1.0 1 878

Index Scan using pk_abc_definition_info on abc_definition_info abcinfo (cost=0.28..0.50 rows=1 width=114) (actual time=0.010..0.011 rows=1 loops=878)

  • Index Cond: ((abc_id_)::text = (proabc.abc_id_)::text)
21. 0.249 0.489 ↓ 1.0 238 1

Hash (cost=8.27..8.27 rows=227 width=234) (actual time=0.489..0.489 rows=238 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
22. 0.240 0.240 ↓ 1.0 238 1

Seq Scan on product_category_setting procatsettingm (cost=0.00..8.27 rows=227 width=234) (actual time=0.013..0.240 rows=238 loops=1)