explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SZ1U : 1

Settings
# exclusive inclusive rows x rows loops node
1. 24.329 11,456.849 ↑ 4.3 86 1

HashAggregate (cost=1,531.87..1,536.52 rows=372 width=960) (actual time=11,456.814..11,456.849 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.208 11,432.520 ↓ 11.7 4,368 1

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

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

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

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

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

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

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

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

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

7. 27.005 11,272.881 ↓ 84.0 4,368 1

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

8. 8,422.902 11,106.100 ↓ 1,456.0 4,368 1

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

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

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

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

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

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

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

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

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

14. 2,646.577 2,664.480 ↓ 8,178.0 8,178 4,368

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

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

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
16. 34.944 139.776 ↑ 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.032..0.032 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. 104.832 104.832 ↑ 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.024..0.024 rows=1 loops=4,368)

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

  • Index Cond: ((abc_id_)::text = (proabc.abc_id_)::text)
19. 65.520 65.520 ↑ 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.015..0.015 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.270 0.470 ↓ 1.0 238 1

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

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

Seq Scan on product_category_setting procatsettingl (cost=0.00..8.27 rows=227 width=234) (actual time=0.008..0.200 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.292 0.683 ↓ 1.0 238 1

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

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

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