explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vazf : 4

Settings
# exclusive inclusive rows x rows loops node
1. 25.395 11,785.491 ↑ 4.3 86 1

HashAggregate (cost=1,531.87..1,536.52 rows=372 width=960) (actual time=11,785.433..11,785.491 rows=86 loops=1)

  • Group Key: pff.site_id_, fi.facility_code_, abcinfo.abc_type_, procatsettingm.category_code_, procatsettingm.description_, procatsettingl.category_code_, procatsettingl.description_
2. 5.596 11,760.096 ↓ 11.7 4,368 1

Hash Join (cost=36.47..1,521.64 rows=372 width=960) (actual time=47.599..11,760.096 rows=4,368 loops=1)

  • Hash Cond: ((procatm.product_category_id_)::text = (procatsettingm.product_category_id_)::text)
3. 6.752 11,754.109 ↓ 11.7 4,368 1

Nested Loop (cost=25.36..1,505.41 rows=372 width=922) (actual time=47.179..11,754.109 rows=4,368 loops=1)

  • Join Filter: ((pff.product_id_)::text = (procatm.product_id_)::text)
4. 6.201 11,725.517 ↓ 31.4 4,368 1

Hash Join (cost=24.95..635.72 rows=139 width=1,118) (actual time=47.134..11,725.517 rows=4,368 loops=1)

  • Hash Cond: ((procatl.product_category_id_)::text = (procatsettingl.product_category_id_)::text)
5. 9.264 11,719.066 ↓ 31.4 4,368 1

Nested Loop (cost=13.84..622.70 rows=139 width=1,080) (actual time=46.856..11,719.066 rows=4,368 loops=1)

  • Join Filter: ((pff.product_id_)::text = (procatl.product_id_)::text)
6. 8.888 11,648.650 ↓ 84.0 4,368 1

Nested Loop (cost=13.42..297.35 rows=52 width=884) (actual time=46.813..11,648.650 rows=4,368 loops=1)

7. 26.189 11,617.922 ↓ 84.0 4,368 1

Nested Loop (cost=13.14..270.97 rows=52 width=966) (actual time=46.785..11,617.922 rows=4,368 loops=1)

8. 8,794.247 11,460.693 ↓ 1,456.0 4,368 1

Nested Loop Left Join (cost=8.41..55.82 rows=3 width=770) (actual time=46.685..11,460.693 rows=4,368 loops=1)

  • Join Filter: (((product_cost.site_id_)::text = (pff.site_id_)::text) AND ((product_cost.product_id_)::text = (pff.product_id_)::text))
  • Rows Removed by Join Filter: 35720626
9. 6.432 15.070 ↓ 1,456.0 4,368 1

Hash Join (cost=8.00..47.33 rows=3 width=750) (actual time=4.979..15.070 rows=4,368 loops=1)

  • Hash Cond: ((pff.facility_id_)::text = (fi.facility_id_)::text)
10. 3.767 8.483 ↓ 436.8 4,368 1

Bitmap Heap Scan on product_facility_feature pff (cost=4.65..43.91 rows=10 width=810) (actual time=4.758..8.483 rows=4,368 loops=1)

  • Recheck Cond: (((site_id_)::text = 'AA101'::text) AND ((product_feature_category_id_)::text = 'PARTSAVERAGEDEMAND'::text))
  • Heap Blocks: exact=126
11. 4.716 4.716 ↓ 436.8 4,368 1

Bitmap Index Scan on product_facility_feature_idx01 (cost=0.00..4.65 rows=10 width=0) (actual time=4.716..4.716 rows=4,368 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_feature_category_id_)::text = 'PARTSAVERAGEDEMAND'::text))
12. 0.070 0.155 ↓ 1.5 92 1

Hash (cost=2.60..2.60 rows=60 width=136) (actual time=0.155..0.155 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
13. 0.085 0.085 ↓ 1.5 92 1

Seq Scan on facility_info fi (cost=0.00..2.60 rows=60 width=136) (actual time=0.014..0.085 rows=92 loops=1)

14. 2,628.523 2,651.376 ↓ 8,178.0 8,178 4,368

Materialize (cost=0.41..8.44 rows=1 width=216) (actual time=0.000..0.607 rows=8,178 loops=4,368)

15. 22.853 22.853 ↓ 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.126..22.853 rows=8,178 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
16. 39.312 131.040 ↑ 17.0 1 4,368

Bitmap Heap Scan on product_abc_info proabc (cost=4.72..71.55 rows=17 width=294) (actual time=0.030..0.030 rows=1 loops=4,368)

  • Recheck Cond: (((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pff.product_id_)::text))
  • Heap Blocks: exact=4368
17. 91.728 91.728 ↑ 17.0 1 4,368

Bitmap Index Scan on product_abc_info_idx01 (cost=0.00..4.72 rows=17 width=0) (actual time=0.021..0.021 rows=1 loops=4,368)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pff.product_id_)::text))
18. 21.840 21.840 ↑ 1.0 1 4,368

Index Scan using pk_abc_definition_info on abc_definition_info abcinfo (cost=0.28..0.50 rows=1 width=114) (actual time=0.005..0.005 rows=1 loops=4,368)

  • Index Cond: ((abc_id_)::text = (proabc.abc_id_)::text)
19. 61.152 61.152 ↑ 3.0 1 4,368

Index Only Scan using product_category_info_idx01 on product_category_info procatl (cost=0.42..6.22 rows=3 width=196) (actual time=0.014..0.014 rows=1 loops=4,368)

  • Index Cond: ((product_id_ = (proabc.product_id_)::text) AND (product_category_type_id_ = 'PARTLARGEGROUP'::text))
  • Heap Fetches: 4368
20. 0.154 0.250 ↓ 1.0 238 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
21. 0.096 0.096 ↓ 1.0 238 1

Seq Scan on product_category_setting procatsettingl (cost=0.00..8.27 rows=227 width=234) (actual time=0.004..0.096 rows=238 loops=1)

22. 21.840 21.840 ↑ 3.0 1 4,368

Index Only Scan using product_category_info_idx01 on product_category_info procatm (cost=0.42..6.22 rows=3 width=196) (actual time=0.004..0.005 rows=1 loops=4,368)

  • Index Cond: ((product_id_ = (proabc.product_id_)::text) AND (product_category_type_id_ = 'PARTMIDDLEGROUP'::text))
  • Heap Fetches: 4368
23. 0.173 0.391 ↓ 1.0 238 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
24. 0.218 0.218 ↓ 1.0 238 1

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