explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NyQG : 1

Settings
# exclusive inclusive rows x rows loops node
1. 44.362 15,745.650 ↑ 14.3 86 1

HashAggregate (cost=6,216.08..6,231.45 rows=1,230 width=960) (actual time=15,745.611..15,745.650 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. 11,166.731 15,701.288 ↓ 3.6 4,368 1

Nested Loop Left Join (cost=271.27..6,182.25 rows=1,230 width=960) (actual time=88.454..15,701.288 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. 13.239 1,127.517 ↓ 3.6 4,368 1

Hash Left Join (cost=270.85..6,152.29 rows=1,230 width=1,038) (actual time=57.423..1,127.517 rows=4,368 loops=1)

  • Hash Cond: ((proabc.abc_id_)::text = (abcinfo.abc_id_)::text)
4. 16.888 1,106.366 ↓ 3.6 4,368 1

Merge Left Join (cost=14.89..5,879.42 rows=1,230 width=1,120) (actual time=49.387..1,106.366 rows=4,368 loops=1)

  • Merge Cond: ((pff.facility_id_)::text = (fi.facility_id_)::text)
5. 12.533 1,088.161 ↓ 3.6 4,368 1

Nested Loop Left Join (cost=14.75..5,853.77 rows=1,230 width=1,180) (actual time=49.199..1,088.161 rows=4,368 loops=1)

6. 8.325 616.988 ↓ 60.7 4,368 1

Nested Loop Left Join (cost=9.98..684.05 rows=72 width=1,082) (actual time=24.052..616.988 rows=4,368 loops=1)

7. 12.260 591.191 ↓ 60.7 4,368 1

Nested Loop Left Join (cost=9.71..656.71 rows=72 width=1,044) (actual time=24.036..591.191 rows=4,368 loops=1)

8. 8.307 548.355 ↓ 161.8 4,368 1

Nested Loop Left Join (cost=5.27..217.62 rows=27 width=946) (actual time=24.004..548.355 rows=4,368 loops=1)

9. 29.139 509.472 ↓ 161.8 4,368 1

Nested Loop Left Join (cost=5.00..207.37 rows=27 width=908) (actual time=19.783..509.472 rows=4,368 loops=1)

10. 288.141 288.141 ↓ 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=19.382..288.141 rows=4,368 loops=1)

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_feature_category_id_)::text = 'PARTSAVERAGEDEMAND'::text))
11. 61.152 192.192 ↑ 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.044..0.044 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
12. 131.040 131.040 ↑ 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.030..0.030 rows=1 loops=4,368)

  • Index Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTLARGEGROUP'::text))
13. 30.576 30.576 ↑ 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.006..0.007 rows=1 loops=4,368)

  • Index Cond: ((procatl.product_category_id_)::text = (product_category_id_)::text)
14. 8.736 30.576 ↑ 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.007..0.007 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
15. 21.840 21.840 ↑ 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.005..0.005 rows=1 loops=4,368)

  • Index Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text))
16. 17.472 17.472 ↑ 1.0 1 4,368

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

  • Index Cond: ((procatm.product_category_id_)::text = (product_category_id_)::text)
17. 39.312 458.640 ↑ 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.104..0.105 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
18. 419.328 419.328 ↑ 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.096..0.096 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))
19. 1.193 1.317 ↓ 73.7 4,422 1

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

20. 0.124 0.124 ↑ 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.063..0.124 rows=55 loops=1)

21. 3.579 7.912 ↑ 1.0 5,376 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 406kB
22. 4.333 4.333 ↑ 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.027..4.333 rows=5,376 loops=1)

23. 3,388.673 3,407.040 ↓ 8,178.0 8,178 4,368

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

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

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text))
Planning time : 5.269 ms
Execution time : 15,746.539 ms