explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4UCL : STG

Settings
# exclusive inclusive rows x rows loops node
1. 297.449 33,208.615 ↓ 110.4 556,660 1

Hash Join (cost=182,822.28..323,372.89 rows=5,041 width=121) (actual time=25,279.595..33,208.615 rows=556,660 loops=1)

  • Hash Cond: (((loc.emcdpo)::text = (zones.zpcdpo)::text) AND ((prpzone_locs.z6czpr)::text = (zones.zpczpr)::text))
2. 6,708.111 32,911.037 ↓ 111.6 625,015 1

Hash Join (cost=182,817.59..323,283.22 rows=5,599 width=130) (actual time=25,279.452..32,911.037 rows=625,015 loops=1)

  • Hash Cond: (((prpzone_locs.z6cdpo)::text = (loc.emcdpo)::text) AND (prpzone_locs.z6nemp = loc.emnemp))
3. 923.975 923.975 ↓ 1.0 4,316,438 1

Seq Scan on hlzpemp prpzone_locs (cost=0.00..108,091.35 rows=4,309,105 width=15) (actual time=0.012..923.975 rows=4,316,438 loops=1)

  • Filter: ((z6czpr)::text ~~ 'P%'::text)
  • Rows Removed by Filter: 32159
4. 1,277.601 25,278.951 ↓ 177.6 1,001,652 1

Hash (cost=182,732.99..182,732.99 rows=5,640 width=133) (actual time=25,278.951..25,278.951 rows=1,001,652 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 262144 (originally 1) Memory Usage: 46096kB
5. 2,060.023 24,001.350 ↓ 177.6 1,001,652 1

Gather (cost=91,640.76..182,732.99 rows=5,640 width=133) (actual time=8,954.168..24,001.350 rows=1,001,652 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 2,332.627 21,941.327 ↓ 142.1 333,884 3

Nested Loop (cost=90,640.76..181,168.99 rows=2,350 width=133) (actual time=9,168.557..21,941.327 rows=333,884 loops=3)

7. 4,740.894 19,608.695 ↓ 150.7 354,343 3

Nested Loop (cost=90,640.33..173,438.57 rows=2,351 width=150) (actual time=9,168.512..19,608.695 rows=354,343 loops=3)

8. 3,181.190 14,867.789 ↓ 152.6 358,976 3

Nested Loop (cost=90,639.90..164,790.20 rows=2,352 width=128) (actual time=9,168.449..14,867.789 rows=358,976 loops=3)

9. 2,027.502 11,686.591 ↓ 152.6 358,977 3

Hash Join (cost=90,639.48..157,142.44 rows=2,352 width=141) (actual time=9,168.369..11,686.591 rows=358,977 loops=3)

  • Hash Cond: ((ipg.gecart)::text = (item.arcart)::text)
10. 491.929 491.929 ↑ 1.2 358,977 3

Parallel Seq Scan on hlgeinp ipg (cost=0.00..64,803.34 rows=446,957 width=114) (actual time=0.037..491.929 rows=358,977 loops=3)

  • Filter: (((gecact)::text = 'BOL'::text) AND ((gecprp)::text = 'BOL'::text) AND ((gecdpo)::text = 'BFC'::text) AND ((gecqal)::text = 'AVL'::text) AND ((getgbl)::text = '0'::text) AND ((gectst)::text = '200'::text))
  • Rows Removed by Filter: 12092
11. 802.973 9,167.160 ↓ 190.0 1,646,105 3

Hash (cost=90,531.16..90,531.16 rows=8,665 width=40) (actual time=9,167.160..9,167.160 rows=1,646,105 loops=3)

  • Buckets: 65536 (originally 16384) Batches: 64 (originally 1) Memory Usage: 4050kB
12. 7,684.213 8,364.187 ↓ 190.0 1,646,105 3

Nested Loop (cost=0.14..90,531.16 rows=8,665 width=40) (actual time=0.062..8,364.187 rows=1,646,105 loops=3)

  • Join Filter: ((item.arcfpm)::text = (expiry_group.pmcfpm)::text)
  • Rows Removed by Join Filter: 9876630
13. 0.072 0.072 ↓ 7.0 7 3

Index Scan using hlfapmp_pkey on hlfapmp expiry_group (cost=0.14..8.16 rows=1 width=21) (actual time=0.035..0.072 rows=7 loops=3)

  • Index Cond: ((pmcact)::text = 'BOL'::text)
14. 679.902 679.902 ↑ 1.0 1,646,105 21

Seq Scan on hlartip item (cost=0.00..69,944.50 rows=1,646,280 width=37) (actual time=0.008..679.902 rows=1,646,105 loops=21)

  • Filter: ((arcact)::text = 'BOL'::text)
15. 0.008 0.008 ↑ 1.0 1 1,076,930

Index Scan using hlsuppp_pkey on hlsuppp hd (cost=0.42..3.25 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1,076,930)

  • Index Cond: (((sucact)::text = 'BOL'::text) AND ((sunsup)::text = (ipg.gensup)::text))
16. 0.012 0.012 ↑ 1.0 1 1,076,929

Index Scan using hlemplp_pkey on hlemplp loc (cost=0.43..3.68 rows=1 width=22) (actual time=0.012..0.012 rows=1 loops=1,076,929)

  • Index Cond: (((emcdpo)::text = (hd.sucdpo)::text) AND (emnemp = hd.sunemp))
  • Filter: ((emtebs)::text = '0'::text)
  • Rows Removed by Filter: 0
17. 0.005 0.005 ↑ 1.0 1 1,063,028

Index Scan using boglitp_pkey on boglitp (cost=0.43..3.29 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=1,063,028)

  • Index Cond: (((gicact)::text = 'BOL'::text) AND ((giglobid)::text = (item.armrqa)::text))
18. 0.020 0.129 ↑ 1.0 96 1

Hash (cost=3.25..3.25 rows=96 width=9) (actual time=0.129..0.129 rows=96 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
19. 0.109 0.109 ↑ 1.0 96 1

Seq Scan on hlzprpp zones (cost=0.00..3.25 rows=96 width=9) (actual time=0.072..0.109 rows=96 loops=1)

  • Filter: ((zptopd)::text = '0'::text)
  • Rows Removed by Filter: 4