explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bGJA

Settings
# exclusive inclusive rows x rows loops node
1. 0.091 6,692.322 ↑ 1.0 250 1

Limit (cost=206,314.49..206,315.11 rows=250 width=325) (actual time=6,692.187..6,692.322 rows=250 loops=1)

2.          

CTE hierar

3. 0.117 0.117 ↓ 3.0 3 1

Seq Scan on d_hierarchy_2018_12_19_11_40_44 (cost=0.00..19.04 rows=1 width=7) (actual time=0.016..0.117 rows=3 loops=1)

  • Filter: (((level_03_cd)::text = '153'::text) AND ((sales_segment_cd)::text = ANY ('{100,101,102}'::text[])))
  • Rows Removed by Filter: 430
4. 18.682 6,692.231 ↑ 40.6 250 1

Sort (cost=206,295.45..206,320.84 rows=10,157 width=325) (actual time=6,692.184..6,692.231 rows=250 loops=1)

  • Sort Key: ((sum(f_cbus_store_summary_brand_2019_01_08_15_53_18.pod_ty))::double precision), s.store_src_cd
  • Sort Method: top-N heapsort Memory: 91kB
5. 204.780 6,673.549 ↓ 2.3 23,165 1

HashAggregate (cost=204,011.86..205,738.55 rows=10,157 width=112) (actual time=6,555.486..6,673.549 rows=23,165 loops=1)

  • Group Key: s.store_src_cd, s.store_cd, s.store_name, s.store_number, s.addr_01_dsc, s.city_dsc, s.state_cd, s.postal_cd, s.unsold
6. 4,448.023 6,468.769 ↓ 2.8 28,232 1

Nested Loop Left Join (cost=469.13..202,640.67 rows=10,157 width=112) (actual time=23.013..6,468.769 rows=28,232 loops=1)

  • Join Filter: ((COALESCE(f_cbus_store_summary_brand_2019_01_08_15_53_18.hier_id, sd.hier_id) = sd.hier_id) AND ((COALESCE(f_cbus_store_summary_brand_2019_01_08_15_53_18.entity_01_cd, sd.entity_01_cd))::text = (sd.entity_01_cd)::text) AND ((COALESCE(f_cbus_store_summary_brand_2019_01_08_15_53_18.brand_cd, sd.brand_cd))::text = (sd.brand_cd)::text) AND ((sd.store_src_cd)::text = (s.store_src_cd)::text))
  • Rows Removed by Join Filter: 9005689
7. 28.634 411.522 ↓ 2.8 28,232 1

Hash Left Join (cost=468.68..201,703.83 rows=10,157 width=120) (actual time=22.280..411.522 rows=28,232 loops=1)

  • Hash Cond: ((s.store_src_cd)::text = (f_cbus_store_summary_brand_2019_01_08_15_53_18.store_src_cd)::text)
8. 14.792 364.759 ↓ 2.3 23,165 1

Nested Loop (cost=379.34..201,347.86 rows=10,157 width=81) (actual time=4.142..364.759 rows=23,165 loops=1)

9. 0.004 0.129 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=78) (actual time=0.129..0.129 rows=1 loops=1)

  • Group Key: (hierar_2.level_04_cd)::text
10. 0.125 0.125 ↓ 3.0 3 1

CTE Scan on hierar hierar_2 (cost=0.00..0.02 rows=1 width=78) (actual time=0.021..0.125 rows=3 loops=1)

11. 44.468 349.838 ↓ 2.3 23,165 1

Bitmap Heap Scan on v_ws_store s (cost=379.32..201,246.26 rows=10,157 width=84) (actual time=4.009..349.838 rows=23,165 loops=1)

  • Recheck Cond: ((market_cd)::text = (hierar_2.level_04_cd)::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 117
  • Heap Blocks: exact=8391
12. 2.704 2.704 ↓ 1.1 23,282 1

Bitmap Index Scan on v_ws_store_market_cd_idx (cost=0.00..376.78 rows=20,314 width=0) (actual time=2.704..2.704 rows=23,282 loops=1)

  • Index Cond: ((market_cd)::text = (hierar_2.level_04_cd)::text)
13.          

SubPlan (forBitmap Heap Scan)

14. 23.282 302.666 ↑ 1.0 1 23,282

Limit (cost=0.56..8.65 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=23,282)

15. 279.384 279.384 ↑ 1.0 1 23,282

Index Only Scan using x_store_entity_2018_12_20_02_12_00_pk on x_store_entity_2018_12_20_02_12_00 x_store_entity_2018_12_19_12_36_17 (cost=0.56..8.65 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=23,282)

  • Index Cond: ((store_src_cd = (s.store_src_cd)::text) AND (opco_cd = '101'::text))
  • Filter: ((entity_01_cd)::text = ANY ('{2224422,2224419,2224418}'::text[]))
  • Rows Removed by Filter: 0
  • Heap Fetches: 4
16. 4.519 18.129 ↓ 434.1 8,248 1

Hash (cost=89.10..89.10 rows=19 width=47) (actual time=18.129..18.129 rows=8,248 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 650kB
17. 4.237 13.610 ↓ 434.1 8,248 1

Nested Loop (cost=0.59..89.10 rows=19 width=47) (actual time=0.144..13.610 rows=8,248 loops=1)

18. 0.006 0.007 ↓ 3.0 3 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.005..0.007 rows=3 loops=1)

  • Group Key: hierar.hier_id
19. 0.001 0.001 ↓ 3.0 3 1

CTE Scan on hierar (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=3 loops=1)

20. 9.366 9.366 ↓ 144.7 2,749 3

Index Scan using f_cbus_store_summary_brand_2019_01_08_15_53_18_pk on f_cbus_store_summary_brand_2019_01_08_15_53_18 (cost=0.57..88.87 rows=19 width=47) (actual time=0.062..3.122 rows=2,749 loops=3)

  • Index Cond: (((time_period_cd)::text = 'FYTD'::text) AND (hier_id = hierar.hier_id) AND ((brand_cd)::text = '604'::text) AND ((entity_01_cd)::text = ANY ('{2224422,2224419,2224418}'::text[])))
21. 1,608.836 1,609.224 ↓ 79.8 319 28,232

Materialize (cost=0.45..22.72 rows=4 width=31) (actual time=0.000..0.057 rows=319 loops=28,232)

22. 0.143 0.388 ↓ 79.8 319 1

Nested Loop (cost=0.45..22.70 rows=4 width=31) (actual time=0.051..0.388 rows=319 loops=1)

23. 0.004 0.005 ↓ 3.0 3 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.004..0.005 rows=3 loops=1)

  • Group Key: hierar_1.hier_id
24. 0.001 0.001 ↓ 3.0 3 1

CTE Scan on hierar hierar_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=3 loops=1)

25. 0.240 0.240 ↓ 26.5 106 3

Index Only Scan using mv_ws_store_brand_last_ship_pk on mv_ws_store_brand_last_ship sd (cost=0.43..22.63 rows=4 width=31) (actual time=0.015..0.080 rows=106 loops=3)

  • Index Cond: ((hier_id = hierar_1.hier_id) AND (brand_cd = '604'::text) AND (entity_01_cd = ANY ('{2224422,2224419,2224418}'::text[])))
  • Heap Fetches: 319