explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FypL

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 42,691.365 ↓ 31.4 1,429,329 1

Append (cost=810,194.80..3,944,024.02 rows=45,471 width=1,196) (actual time=4,027.338..42,691.365 rows=1,429,329 loops=1)

  • = '0'::text))
2. 606.932 7,825.249 ↓ 32.1 855,929 1

Gather (cost=810,194.80..914,430.06 rows=26,657 width=1,094) (actual time=4,027.320..7,825.249 rows=855,929 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
3. 723.573 7,218.317 ↓ 25.7 171,186 5 / 5

Parallel Hash Left Join (cost=809,194.80..910,764.36 rows=6,664 width=1,094) (actual time=4,015.228..7,218.317 rows=171,186 loops=5)

  • Hash Cond: (t_shr_d_dcr_tn_conso_nm1_xref.src_ban = t_shr_f_ban_calls.src_ban)
4. 128.673 5,697.913 ↓ 25.7 171,186 5 / 5

Nested Loop Left Join (cost=718,909.22..813,385.91 rows=6,664 width=608) (actual time=3,214.842..5,697.913 rows=171,186 loops=5)

5. 131.938 5,226.868 ↓ 25.7 171,186 5 / 5

Nested Loop Left Join (cost=718,908.80..805,565.56 rows=6,664 width=600) (actual time=3,214.811..5,226.868 rows=171,186 loops=5)

6. 318.167 4,752.558 ↓ 25.7 171,186 5 / 5

Nested Loop Left Join (cost=718,908.37..797,745.22 rows=6,664 width=592) (actual time=3,214.778..4,752.558 rows=171,186 loops=5)

  • -> Parallel Hash Left Join (cost=718,907.94..789924.88 rows=6,664 width=584) (actual time=3,214.693..3982.976rows=171,186 loops=5)
  • Hash Cond: (t_shr_d_dcr_tn_conso_nm1_xref.src_ban = t_shr_d_dcr_lob_holding.src_ban)
7. 197.685 3,237.146 ↓ 25.7 171,186 5 / 5

Parallel Hash Left Join (cost=632,472.34..703,456.30 rows=6,664 width=570) (actual time=2,698.674..3,237.146 rows=171,186 loops=5)

  • Hash Cond: (t_shr_d_dcr_tn_conso_nm1_xref.src_ban = t_shr_d_ban_age.src_ban)
8. 247.465 2,690.524 ↓ 25.7 171,186 5 / 5

Parallel Hash Join (cost=577,878.15..648,830.77 rows=6,664 width=564) (actual time=2,345.129..2,690.524 rows=171,186 loops=5)

  • Hash Cond: ((t_shr_f_environics.postal_cd)::text = (t_shr_d_conso_ban_locn.bill_postal_cd)::text)
9. 98.270 98.270 ↑ 1.3 175,622 5 / 5

Parallel Seq Scan on t_shr_f_environics (cost=0.00..68,466.27 rows=219,528 width=508) (actual time=0.020..98.270 rows=175,622 loops=5)

10. 155.971 2,344.789 ↓ 26.4 176,028 5 / 5

Parallel Hash (cost=577,794.82..577,794.82 rows=6,666 width=69) (actual time=2,344.789..2,344.789 rows=176,028 loops=5)

  • Buckets: 1,048,576 (originally 32768) Batches: 1 (originally 1) Memory Usage: 110,368kB
11. 308.901 2,188.818 ↓ 26.4 176,028 5 / 5

Parallel Hash Join (cost=147,467.01..577,794.82 rows=6,666 width=69) (actual time=868.251..2,188.818 rows=176,028 loops=5)

  • Hash Cond: (t_shr_d_dcr_tn_conso_nm1_xref.src_ban = t_shr_d_conso_ban_locn.src_ban)
12. 1,027.251 1,027.251 ↓ 16.8 186,992 5 / 5

Parallel Seq Scan on t_shr_d_dcr_tn_conso_nm1_xref (cost=0.00..430,269.43 rows=11,124 width=19) (actual time=0.038..1,027.251 rows=186,992 loops=5)

  • Filter: (((ban_type_nm)::text = 'One Bill'::text) AND ("right"((tn)::text, 1) = '0'::text))
  • Rows Removed by Filter: 3,657,883
13. 501.923 852.666 ↑ 1.3 907,307 5 / 5

Parallel Hash (cost=133,290.33..133,290.33 rows=1,134,134 width=58) (actual time=852.665..852.666 rows=907,307 loops=5)

  • Buckets: 8,388,608 Batches: 1 Memory Usage: 498,880kB
14. 350.743 350.743 ↑ 1.3 907,307 5 / 5

Parallel Seq Scan on t_shr_d_conso_ban_locn (cost=0.00..133,290.33 rows=1,134,134 width=58) (actual time=0.039..350.743 rows=907,307 loops=5)

15. 229.866 348.937 ↑ 1.2 575,936 5 / 5

Parallel Hash (cost=45,595.20..45,595.20 rows=719,920 width=14) (actual time=348.936..348.937 rows=575,936 loops=5)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 168,000kB
16. 119.071 119.071 ↑ 1.2 575,936 5 / 5

Parallel Seq Scan on t_shr_d_ban_age (cost=0.00..45,595.20 rows=719,920 width=14) (actual time=0.026..119.071 rows=575,936 loops=5)

17. 301.064 512.502 ↑ 1.2 726,137 5 / 5

Parallel Hash (cost=75,089.71..75,089.71 rows=907,671 width=22) (actual time=512.502..512.502 rows=726,137 loops=5)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 231,776kB
18. 211.438 211.438 ↑ 1.2 726,137 5 / 5

Parallel Seq Scan on t_shr_d_dcr_lob_holding (cost=0.00..75,089.71 rows=907,671 width=22) (actual time=0.017..211.438 rows=726,137 loops=5)

19. 684.743 684.743 ↓ 0.0 0 855,929 / 5

Index Scan using i_t_shr_d_ban_latest_deact_lob_cd on t_shr_d_ban_latest_deact (cost=0.43..1.16 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=855,929)

  • Index Cond: ((t_shr_d_dcr_tn_conso_nm1_xref.src_ban = src_ban) AND (lob_cd = '01'::bpchar))
20. 342.372 342.372 ↓ 0.0 0 855,929 / 5

Index Scan using i_t_shr_d_ban_latest_deact_lob_cd on t_shr_d_ban_latest_deact t_shr_d_ban_latest_deact_1 (cost=0.43..1.16 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=855,929)

  • Index Cond: ((t_shr_d_dcr_tn_conso_nm1_xref.src_ban = src_ban) AND (lob_cd = '04'::bpchar))
21. 342.372 342.372 ↓ 0.0 0 855,929 / 5

Index Scan using i_t_shr_d_ban_latest_deact_lob_cd on t_shr_d_ban_latest_deact t_shr_d_ban_latest_deact_2 (cost=0.43..1.16 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=855,929)

  • Index Cond: ((t_shr_d_dcr_tn_conso_nm1_xref.src_ban = src_ban) AND (lob_cd = '05'::bpchar))
22. 456.672 796.831 ↑ 1.2 367,736 5 / 5

Parallel Hash (cost=84,539.70..84,539.70 rows=459,670 width=296) (actual time=796.831..796.831 rows=367,736 loops=5)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 623,040kB
23. 340.159 340.159 ↑ 1.2 367,736 5 / 5

Parallel Seq Scan on t_shr_f_ban_calls (cost=0.00..84,539.70 rows=459,670 width=296) (actual time=0.023..340.159 rows=367,736 loops=5)

24. 2,743.765 32,321.722 ↓ 33.0 464,010 1

Hash Left Join (cost=2,386,962.98..2,484,298.36 rows=14,078 width=1,152) (actual time=30,475.480..32,321.722 rows=464,010 loops=1)

  • Hash Cond: (t_shr_d_dcr_tn_conso_nm1_xref_1.src_ban = t_bm_f_ban_calls.src_ban)
25. 1,262.041 29,577.957 ↓ 33.0 464,010 1

Hash Left Join (cost=2,296,389.59..2,393,646.64 rows=14,078 width=574) (actual time=28,293.894..29,577.957 rows=464,010 loops=1)

  • Hash Cond: (t_shr_d_dcr_tn_conso_nm1_xref_1.src_ban = t_bm_d_ban_age.src_ban)
26. 605.105 28,315.916 ↓ 33.0 464,010 1

Hash Join (cost=2,217,336.82..2,314,482.45 rows=14,078 width=568) (actual time=27,360.224..28,315.916 rows=464,010 loops=1)

  • Hash Cond: ((t_shr_f_environics_1.postal_cd)::text = (t_bm_d_ban_locn.bill_postal_cd)::text)
27. 350.840 350.840 ↑ 1.0 878,110 1

Seq Scan on t_shr_f_environics t_shr_f_environics_1 (cost=0.00..75,052.10 rows=878,110 width=508) (actual time=0.031..350.840 rows=878,110 loops=1)

28. 147.473 27,359.971 ↓ 33.4 470,191 1

Hash (cost=2,217,160.81..2,217,160.81 rows=14,081 width=73) (actual time=27,359.971..27,359.971 rows=470,191 loops=1)

  • Buckets: 524,288 (originally 16384) Batches: 1 (originally 1) Memory Usage: 55,601kB
29. 15,765.341 27,212.498 ↓ 33.4 470,191 1

Merge Left Join (cost=2,211,405.04..2,217,160.81 rows=14,081 width=73) (actual time=25,283.041..27,212.498 rows=470,191 loops=1)

  • Merge Cond: (t_shr_d_dcr_tn_conso_nm1_xref_1.src_ban = ((bm.src_ban)::numeric))
30. 409.624 11,447.157 ↓ 33.4 470,191 1

Sort (cost=1,248,943.46..1,248,978.66 rows=14,081 width=69) (actual time=11,341.263..11,447.157 rows=470,191 loops=1)

  • Sort Key: t_shr_d_dcr_tn_conso_nm1_xref_1.src_ban
  • Sort Method: quicksort Memory: 78,409kB
31. 11,037.533 11,037.533 ↓ 33.4 470,191 1

Hash Join (cost=609,425.04..1,247,973.18 rows=14,081 width=69) (actual time=3,811.443..11,037.533 rows=470,191 loops=1)

  • Hash Cond: (t_shr_d_dcr_tn_conso_nm1_xref_1.src_ban = t_bm_d_ban_locn.src_ban)
  • -> Index Scan using i_t_shr_d_dcr_tn_conso_nm1_xref_ban_type_nm on t_shr_d_dcr_tn_conso_nm1_xref
32. 0.000 7,680.058 ↓ 32.9 850,122 1

t_shr_d_dcr_tn_conso_nm1_xref_1 (cost=435,831.03..1,074,044.77 rows=25,812 width=19) (actual time=1,248.885..7,680.058 rows=850,122 loops=1)

  • Index Cond: ((ban_type_nm)::text = 'Mobility'::text)
  • Filter: ((NOT (hashed SubPlan 1)) AND ("right"((tn)::text, 1) = '0'::text))
  • Rows Removed by Filter: 9,463,217
33. 1,514.705 2,543.867 ↑ 1.0 4,129,689 1

Hash (cost=121,972.89..121,972.89 rows=4,129,689 width=58) (actual time=2,543.867..2,543.867 rows=4,129,689 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 415,847kB
34. 1,029.162 1,029.162 ↑ 1.0 4,129,689 1

Seq Scan on t_bm_d_ban_locn (cost=0.00..121,972.89 rows=4,129,689 width=58) (actual time=0.055..1,029.162 rows=4,129,689 loops=1)

35. 3,062.175 14,776.369 ↓ 7.4 5,528,751 1

Sort (cost=962,461.58..964,340.31 rows=751,491 width=4) (actual time=13,941.588..14,776.369 rows=5,528,751 loops=1)

  • Sort Key: ((bm.src_ban)::numeric)
  • Sort Method: quicksort Memory: 431,972kB
36. 609.373 11,714.194 ↓ 7.3 5,487,123 1

Subquery Scan on bm (cost=868,452.32..889,118.33 rows=751,491 width=4) (actual time=9,906.060..11,714.194 rows=5,487,123 loops=1)

37. 4,987.175 11,104.821 ↓ 7.3 5,487,123 1

HashAggregate (cost=868,452.32..881,603.42 rows=751,491 width=8) (actual time=9,906.044..11,104.821 rows=5,487,123 loops=1)

  • Group Key: t_bm_f_sub_base.period_end_dt, ("left"((t_bm_f_sub_base.src_ban)::text, 9))::integer
38. 6,117.646 6,117.646 ↑ 1.0 10,254,573 1

Seq Scan on t_bm_f_sub_base (cost=0.00..817,179.46 rows=10,254,573 width=8) (actual time=0.061..6,117.646 rows=10,254,573 loops=1)

  • Filter: ((aggregation_type)::text = 'weekly'::text)
39. 548.892 916.299 ↑ 1.0 2,206,123 1

Hash (cost=51,476.23..51,476.23 rows=2,206,123 width=14) (actual time=916.299..916.299 rows=2,206,123 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 134,026kB
40. 367.407 367.407 ↑ 1.0 2,206,123 1

Seq Scan on t_bm_d_ban_age (cost=0.00..51,476.23 rows=2,206,123 width=14) (actual time=0.046..367.407 rows=2,206,123 loops=1)

41. 1,130.658 2,172.862 ↑ 1.0 1,372,773 1

Hash (cost=73,413.73..73,413.73 rows=1,372,773 width=296) (actual time=2,172.862..2,172.862 rows=1,372,773 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 457,441kB
42. 1,042.204 1,042.204 ↑ 1.0 1,372,773 1

Seq Scan on t_bm_f_ban_calls (cost=0.00..73,413.73 rows=1,372,773 width=296) (actual time=0.039..1,042.204 rows=1,372,773 loops=1)

43. 1,825.579 2,736.581 ↓ 23.1 109,390 1

Seq Scan on t_shr_f_tn_calls (cost=429,106.73..544,613.54 rows=4,736 width=2,049) (actual time=1,607.488..2,736.581 rows=109,390 loops=1)

  • Filter: ((NOT (hashed SubPlan 2)) AND ("right"((tn)::text, 1) = '0'::text))
  • Rows Removed by Filter: 1,784,828
44.          

SubPlan (for Seq Scan)

45. 0.000 911.002 ↓ 20.8 1,999,890 1

Gather (cost=1,000.00..428,866.42 rows=96,122 width=11) (actual time=1.474..911.002 rows=1,999,890 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
46. 1,132.392 1,132.392 ↓ 16.6 399,978 5 / 5

Parallel Seq Scan on t_shr_d_dcr_tn_conso_nm1_xref t_shr_d_dcr_tn_conso_nm1_xref_3 (cost=0.00..418,254.22 rows=24,030 width=11) (actual time=0.027..1,132.392 rows=399,978 loops=5)

  • Filter: ("right"((tn)::text, 1) = '0'::text)
  • Rows Removed by Filter: 3,444,896
47.          

SubPlan (for t_shr_d_dcr_tn_conso_nm1_xref_1)

48. 0.000 918.126 ↓ 21.0 934,959 1

Gather (cost=1,000.00..435,719.23 rows=44,498 width=11) (actual time=2.558..918.126 rows=934,959 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
49. 1,043.302 1,043.302 ↓ 16.8 186,992 5 / 5

Parallel Seq Scan on t_shr_d_dcr_tn_conso_nm1_xref t_shr_d_dcr_tn_conso_nm1_xref_2 (cost=0.00..430,269.43 rows=11,124 width=11) (actual time=0.040..1,043.302 rows=186,992 loops=5)

  • Filter: (((ban_type_nm)::text = 'One Bill'::text) AND ("right"((tn)::text, 1)
  • Rows Removed by Filter: 3,657,883
Planning time : 16.500 ms
Execution time : 43,130.289 ms