explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iyyC : 3

Settings
# exclusive inclusive rows x rows loops node
1. 28.264 12,825.822 ↑ 14.3 86 1

HashAggregate (cost=6,195.41..6,210.78 rows=1,230 width=960) (actual time=12,825.786..12,825.822 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.448 12,797.558 ↓ 3.6 4,368 1

Hash Left Join (cost=282.10..6,161.58 rows=1,230 width=960) (actual time=36.723..12,797.558 rows=4,368 loops=1)

  • Hash Cond: ((procatm.product_category_id_)::text = (procatsettingm.product_category_id_)::text)
3. 10.588 12,790.787 ↓ 3.6 4,368 1

Hash Left Join (cost=271.00..6,133.56 rows=1,230 width=922) (actual time=36.381..12,790.787 rows=4,368 loops=1)

  • Hash Cond: ((proabc.abc_id_)::text = (abcinfo.abc_id_)::text)
4. 9.051 12,773.356 ↓ 3.6 4,368 1

Merge Left Join (cost=15.04..5,860.69 rows=1,230 width=1,004) (actual time=29.408..12,773.356 rows=4,368 loops=1)

  • Merge Cond: ((pff.facility_id_)::text = (fi.facility_id_)::text)
5. 11.525 12,762.755 ↓ 3.6 4,368 1

Nested Loop Left Join (cost=14.90..5,835.04 rows=1,230 width=1,064) (actual time=29.280..12,762.755 rows=4,368 loops=1)

6. 9.813 12,607.086 ↓ 60.7 4,368 1

Nested Loop Left Join (cost=10.13..665.32 rows=72 width=1,064) (actual time=29.215..12,607.086 rows=4,368 loops=1)

7. 8.164 12,571.065 ↓ 161.8 4,368 1

Nested Loop Left Join (cost=5.68..226.24 rows=27 width=966) (actual time=29.193..12,571.065 rows=4,368 loops=1)

8. 29.176 12,541.061 ↓ 161.8 4,368 1

Nested Loop Left Join (cost=5.41..215.98 rows=27 width=928) (actual time=29.156..12,541.061 rows=4,368 loops=1)

9. 9,466.992 12,359.005 ↓ 436.8 4,368 1

Nested Loop Left Join (cost=0.96..53.36 rows=10 width=830) (actual time=29.042..12,359.005 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
10. 26.605 26.605 ↓ 436.8 4,368 1

Index Scan using product_facility_feature_idx01 on product_facility_feature pff (cost=0.55..44.75 rows=10 width=810) (actual time=0.189..26.605 rows=4,368 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_feature_category_id_)::text = 'PARTSAVERAGEDEMAND'::text))
11. 2,847.201 2,865.408 ↓ 8,178.0 8,178 4,368

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

12. 18.207 18.207 ↓ 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.091..18.207 rows=8,178 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
13. 82.992 152.880 ↑ 3.0 1 4,368

Bitmap Heap Scan on product_category_info procatl (cost=4.45..16.23 rows=3 width=196) (actual time=0.035..0.035 rows=1 loops=4,368)

  • Recheck Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text))
  • Heap Blocks: exact=4368
14. 69.888 69.888 ↑ 3.0 1 4,368

Bitmap Index Scan on product_category_info_idx01 (cost=0.00..4.45 rows=3 width=0) (actual time=0.016..0.016 rows=1 loops=4,368)

  • Index Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text))
15. 21.840 21.840 ↑ 1.0 1 4,368

Index Scan using pk_product_category on product_category_setting procatsettingl (cost=0.27..0.37 rows=1 width=234) (actual time=0.004..0.005 rows=1 loops=4,368)

  • Index Cond: ((procatl.product_category_id_)::text = (product_category_id_)::text)
16. 8.736 26.208 ↑ 3.0 1 4,368

Bitmap Heap Scan on product_category_info procatm (cost=4.45..16.23 rows=3 width=196) (actual time=0.006..0.006 rows=1 loops=4,368)

  • Recheck Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text))
  • Heap Blocks: exact=4368
17. 17.472 17.472 ↑ 3.0 1 4,368

Bitmap Index Scan on product_category_info_idx01 (cost=0.00..4.45 rows=3 width=0) (actual time=0.004..0.004 rows=1 loops=4,368)

  • Index Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text))
18. 48.048 144.144 ↑ 17.0 1 4,368

Bitmap Heap Scan on product_abc_info proabc (cost=4.77..71.63 rows=17 width=294) (actual time=0.033..0.033 rows=1 loops=4,368)

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

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

  • Index Cond: (((site_id_)::text = (pff.site_id_)::text) AND ((site_id_)::text = 'AA101'::text) AND ((product_id_)::text = (pff.product_id_)::text))
20. 1.481 1.550 ↓ 73.7 4,422 1

Materialize (cost=0.14..17.19 rows=60 width=136) (actual time=0.037..1.550 rows=4,422 loops=1)

21. 0.069 0.069 ↑ 1.1 55 1

Index Scan using pk_facility_info on facility_info fi (cost=0.14..17.04 rows=60 width=136) (actual time=0.029..0.069 rows=55 loops=1)

22. 2.922 6.843 ↑ 1.0 5,376 1

Hash (cost=188.76..188.76 rows=5,376 width=114) (actual time=6.843..6.843 rows=5,376 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 406kB
23. 3.921 3.921 ↑ 1.0 5,376 1

Seq Scan on abc_definition_info abcinfo (cost=0.00..188.76 rows=5,376 width=114) (actual time=0.020..3.921 rows=5,376 loops=1)

24. 0.152 0.323 ↓ 1.0 238 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
25. 0.171 0.171 ↓ 1.0 238 1

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