explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I4FI : 3

Settings
# exclusive inclusive rows x rows loops node
1. 43.604 13,668.425 ↑ 4.3 86 1

HashAggregate (cost=1,536.89..1,541.54 rows=372 width=960) (actual time=13,668.380..13,668.425 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. 10,134.522 13,624.821 ↓ 11.7 4,368 1

Nested Loop Left Join (cost=36.47..1,526.66 rows=372 width=960) (actual time=38.526..13,624.821 rows=4,368 loops=1)

  • Join Filter: (((product_cost.site_id_)::text = (pff.site_id_)::text) AND ((pff.product_id_)::text = (product_cost.product_id_)::text))
  • Rows Removed by Join Filter: 35720626
3. 15.267 371.547 ↓ 11.7 4,368 1

Hash Join (cost=36.06..1,511.71 rows=372 width=1,038) (actual time=7.816..371.547 rows=4,368 loops=1)

  • Hash Cond: ((procatm.product_category_id_)::text = (procatsettingm.product_category_id_)::text)
4. 7.675 355.725 ↓ 11.7 4,368 1

Nested Loop (cost=24.95..1,495.49 rows=372 width=1,000) (actual time=7.217..355.725 rows=4,368 loops=1)

5. 7.601 317.474 ↓ 31.4 4,368 1

Hash Join (cost=24.53..626.84 rows=139 width=1,098) (actual time=7.170..317.474 rows=4,368 loops=1)

  • Hash Cond: ((procatl.product_category_id_)::text = (procatsettingl.product_category_id_)::text)
6. 6.143 309.580 ↓ 31.4 4,368 1

Nested Loop (cost=13.42..613.82 rows=139 width=1,060) (actual time=6.764..309.580 rows=4,368 loops=1)

7. 10.140 224.813 ↓ 84.0 4,368 1

Nested Loop (cost=13.01..288.86 rows=52 width=864) (actual time=6.603..224.813 rows=4,368 loops=1)

8. 24.985 188.465 ↓ 84.0 4,368 1

Nested Loop (cost=12.72..262.48 rows=52 width=946) (actual time=6.539..188.465 rows=4,368 loops=1)

9. 8.193 19.336 ↓ 1,456.0 4,368 1

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

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

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

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

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

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

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

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

14. 34.944 144.144 ↑ 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.033..0.033 rows=1 loops=4,368)

  • Recheck Cond: (((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pff.product_id_)::text))
  • Heap Blocks: exact=4368
15. 109.200 109.200 ↑ 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.025..0.025 rows=1 loops=4,368)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pff.product_id_)::text))
16. 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)
17. 78.624 78.624 ↑ 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.017..0.018 rows=1 loops=4,368)

  • Index Cond: ((product_id_ = (proabc.product_id_)::text) AND (product_category_type_id_ = 'PARTLARGEGROUP'::text))
  • Heap Fetches: 4368
18. 0.188 0.293 ↓ 1.0 238 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
19. 0.105 0.105 ↓ 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.105 rows=238 loops=1)

20. 30.576 30.576 ↑ 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.007 rows=1 loops=4,368)

  • Index Cond: ((product_id_ = (proabc.product_id_)::text) AND (product_category_type_id_ = 'PARTMIDDLEGROUP'::text))
  • Heap Fetches: 4368
21. 0.227 0.555 ↓ 1.0 238 1

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

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

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

23. 3,101.697 3,118.752 ↓ 8,178.0 8,178 4,368

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

24. 17.055 17.055 ↓ 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.086..17.055 rows=8,178 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))