explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7qzF

Settings
# exclusive inclusive rows x rows loops node
1. 0.093 422.702 ↑ 1.0 250 1

Limit (cost=35,011.00..35,011.63 rows=250 width=325) (actual time=422.565..422.702 rows=250 loops=1)

2.          

CTE hierar

3. 0.119 0.119 ↓ 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.119 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. 19.437 422.609 ↑ 81.3 250 1

Sort (cost=34,991.97..35,042.75 rows=20,314 width=325) (actual time=422.564..422.609 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. 149.286 403.172 ↓ 1.1 23,282 1

GroupAggregate (cost=27,631.62..33,878.17 rows=20,314 width=112) (actual time=247.249..403.172 rows=23,282 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. 164.785 253.886 ↓ 1.4 28,460 1

Sort (cost=27,631.62..27,682.40 rows=20,314 width=112) (actual time=247.203..253.886 rows=28,460 loops=1)

  • Sort 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
  • Sort Method: quicksort Memory: 5571kB
7. 13.903 89.101 ↓ 1.4 28,460 1

Hash Left Join (cost=25,962.35..26,178.13 rows=20,314 width=112) (actual time=45.242..89.101 rows=28,460 loops=1)

  • Hash Cond: ((s.store_src_cd)::text = (sd.store_src_cd)::text)
  • 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))
  • Rows Removed by Join Filter: 292
8. 15.857 74.692 ↓ 1.4 28,460 1

Hash Right Join (cost=25,939.60..26,028.40 rows=20,314 width=120) (actual time=44.729..74.692 rows=28,460 loops=1)

  • Hash Cond: ((f_cbus_store_summary_brand_2019_01_08_15_53_18.store_src_cd)::text = (s.store_src_cd)::text)
9. 4.416 14.277 ↓ 434.1 8,248 1

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

10. 0.007 0.009 ↓ 3.0 3 1

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

  • Group Key: hierar.hier_id
11. 0.002 0.002 ↓ 3.0 3 1

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

12. 9.852 9.852 ↓ 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.061..3.284 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[])))
13. 13.383 44.558 ↓ 1.1 23,282 1

Hash (cost=25,685.09..25,685.09 rows=20,314 width=81) (actual time=44.558..44.558 rows=23,282 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 2633kB
14. 13.209 31.175 ↓ 1.1 23,282 1

Nested Loop (cost=381.88..25,685.09 rows=20,314 width=81) (actual time=4.089..31.175 rows=23,282 loops=1)

15. 0.003 0.130 ↑ 1.0 1 1

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

  • Group Key: (hierar_2.level_04_cd)::text
16. 0.127 0.127 ↓ 3.0 3 1

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

17. 15.148 17.836 ↓ 1.1 23,282 1

Bitmap Heap Scan on v_ws_store s (cost=381.86..25,481.91 rows=20,314 width=84) (actual time=3.953..17.836 rows=23,282 loops=1)

  • Recheck Cond: ((market_cd)::text = (hierar_2.level_04_cd)::text)
  • Heap Blocks: exact=8391
18. 2.688 2.688 ↓ 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.688..2.688 rows=23,282 loops=1)

  • Index Cond: ((market_cd)::text = (hierar_2.level_04_cd)::text)
19. 0.125 0.506 ↓ 79.8 319 1

Hash (cost=22.70..22.70 rows=4 width=31) (actual time=0.506..0.506 rows=319 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
20. 0.138 0.381 ↓ 79.8 319 1

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

21. 0.004 0.006 ↓ 3.0 3 1

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

  • Group Key: hierar_1.hier_id
22. 0.002 0.002 ↓ 3.0 3 1

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

23. 0.237 0.237 ↓ 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.019..0.079 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