explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OIkt : 1

Settings
# exclusive inclusive rows x rows loops node
1. 52.417 14,701.412 ↑ 4.3 86 1

HashAggregate (cost=1,536.89..1,541.54 rows=372 width=960) (actual time=14,701.360..14,701.412 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,949.848 14,648.995 ↓ 11.7 4,368 1

Nested Loop Left Join (cost=36.47..1,526.66 rows=372 width=960) (actual time=33.308..14,648.995 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
3. 7.938 379.467 ↓ 11.7 4,368 1

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

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

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

5. 7.961 330.624 ↓ 31.4 4,368 1

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

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

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

7. 10.255 236.155 ↓ 84.0 4,368 1

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

8. 31.929 199.692 ↓ 84.0 4,368 1

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

9. 8.721 19.251 ↓ 1,456.0 4,368 1

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

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

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

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

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

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

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

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

14. 39.312 148.512 ↑ 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.034 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.018..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.196 0.303 ↓ 1.0 238 1

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

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

20. 34.944 34.944 ↑ 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.006..0.008 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.182 0.406 ↓ 1.0 238 1

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

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

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

23. 3,303.831 3,319.680 ↓ 8,178.0 8,178 4,368

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

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

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