explain.depesz.com

PostgreSQL's explain analyze made readable

Result: deu6 : 1

Settings
# exclusive inclusive rows x rows loops node
1. 26.785 12,351.212 ↑ 4.3 86 1

HashAggregate (cost=1,531.87..1,536.52 rows=372 width=960) (actual time=12,351.183..12,351.212 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. 6.283 12,324.427 ↓ 11.7 4,368 1

Hash Join (cost=36.47..1,521.64 rows=372 width=960) (actual time=68.422..12,324.427 rows=4,368 loops=1)

  • Hash Cond: ((procatm.product_category_id_)::text = (procatsettingm.product_category_id_)::text)
3. 6.207 12,317.626 ↓ 11.7 4,368 1

Nested Loop (cost=25.36..1,505.41 rows=372 width=922) (actual time=67.870..12,317.626 rows=4,368 loops=1)

  • Join Filter: ((pff.product_id_)::text = (procatm.product_id_)::text)
4. 7.561 12,285.211 ↓ 31.4 4,368 1

Hash Join (cost=24.95..635.72 rows=139 width=1,118) (actual time=67.807..12,285.211 rows=4,368 loops=1)

  • Hash Cond: ((procatl.product_category_id_)::text = (procatsettingl.product_category_id_)::text)
5. 9.688 12,277.282 ↓ 31.4 4,368 1

Nested Loop (cost=13.84..622.70 rows=139 width=1,080) (actual time=67.386..12,277.282 rows=4,368 loops=1)

  • Join Filter: ((pff.product_id_)::text = (procatl.product_id_)::text)
6. 8.884 12,175.866 ↓ 84.0 4,368 1

Nested Loop (cost=13.42..297.35 rows=52 width=884) (actual time=67.232..12,175.866 rows=4,368 loops=1)

7. 33.682 12,140.774 ↓ 84.0 4,368 1

Nested Loop (cost=13.14..270.97 rows=52 width=966) (actual time=67.132..12,140.774 rows=4,368 loops=1)

8. 9,144.306 11,928.004 ↓ 1,456.0 4,368 1

Nested Loop Left Join (cost=8.41..55.82 rows=3 width=770) (actual time=66.902..11,928.004 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.895 23.122 ↓ 1,456.0 4,368 1

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

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

Bitmap Heap Scan on product_facility_feature pff (cost=4.65..43.91 rows=10 width=810) (actual time=11.025..15.972 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. 10.968 10.968 ↓ 436.8 4,368 1

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

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

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

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

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

14. 2,739.269 2,760.576 ↓ 8,178.0 8,178 4,368

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

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

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
16. 52.416 179.088 ↑ 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.040..0.041 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. 126.672 126.672 ↑ 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.029..0.029 rows=1 loops=4,368)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pff.product_id_)::text))
18. 26.208 26.208 ↑ 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.006..0.006 rows=1 loops=4,368)

  • Index Cond: ((abc_id_)::text = (proabc.abc_id_)::text)
19. 91.728 91.728 ↑ 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.021..0.021 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.272 0.368 ↓ 1.0 238 1

Hash (cost=8.27..8.27 rows=227 width=234) (actual time=0.368..0.368 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. 26.208 26.208 ↑ 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.005..0.006 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.214 0.518 ↓ 1.0 238 1

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

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

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