explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wHKT : 1

Settings
# exclusive inclusive rows x rows loops node
1. 9.254 433.088 ↑ 2.3 86 1

HashAggregate (cost=7,615.01..7,617.51 rows=200 width=476) (actual time=433.044..433.088 rows=86 loops=1)

  • Group Key: tt.dealer_code_, tt.facility_cd_, tt.product_category_, tt.product_category_description_, tt.category_type_, tt.category_description_, tt.abc_category_
2.          

CTE tt

3. 16.552 351.219 ↓ 3.6 4,368 1

Hash Left Join (cost=270.85..6,158.44 rows=1,230 width=1,038) (actual time=12.182..351.219 rows=4,368 loops=1)

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

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

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

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

6. 6.503 226.514 ↓ 60.7 4,368 1

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

7. 10.714 185.067 ↓ 60.7 4,368 1

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

8. 9.687 135.041 ↓ 161.8 4,368 1

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

9. 16.858 103.514 ↓ 161.8 4,368 1

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

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

  • Index Cond: (((site_id_)::text = 'AA101'::text) AND ((product_feature_category_id_)::text = 'PARTSAVERAGEDEMAND'::text))
11. 17.472 74.256 ↑ 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.017..0.017 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. 56.784 56.784 ↑ 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.013..0.013 rows=1 loops=4,368)

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

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

  • Index Cond: (((pff.product_id_)::text = (product_id_)::text) AND ((product_category_type_id_)::text = 'PARTMIDDLEGROUP'::text))
16. 34.944 34.944 ↑ 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.007..0.008 rows=1 loops=4,368)

  • Index Cond: ((procatm.product_category_id_)::text = (product_category_id_)::text)
17. 17.472 74.256 ↑ 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.017..0.017 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. 56.784 56.784 ↑ 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.013..0.013 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.308 1.447 ↓ 73.7 4,422 1

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

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

21. 4.123 11.478 ↑ 1.0 5,376 1

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

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

23. 16.017 423.834 ↓ 3.6 4,368 1

Hash Right Join (cost=43.05..1,428.90 rows=1,230 width=476) (actual time=413.277..423.834 rows=4,368 loops=1)

  • Hash Cond: (((pc.site_id_)::text = (tt.dealer_code_)::text) AND ((pc.product_id_)::text = (tt.product_id_)::text))
24. 30.673 30.673 ↓ 195.1 35,110 1

Seq Scan on product_cost pc (cost=0.00..1,329.54 rows=180 width=216) (actual time=0.017..30.673 rows=35,110 loops=1)

  • Filter: ((product_cost_component_category_id_)::text = 'C034AVERAGECOST'::text)
  • Rows Removed by Filter: 1186
25. 9.581 377.144 ↓ 3.6 4,368 1

Hash (cost=24.60..24.60 rows=1,230 width=554) (actual time=377.144..377.144 rows=4,368 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 536kB
26. 367.563 367.563 ↓ 3.6 4,368 1

CTE Scan on tt (cost=0.00..24.60 rows=1,230 width=554) (actual time=12.190..367.563 rows=4,368 loops=1)