explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wagv : babajaba

Settings
# exclusive inclusive rows x rows loops node
1. 245.672 24,419.293 ↓ 4.8 95,083 1

Hash Left Join (cost=1,162,825.37..1,206,163.90 rows=19,889 width=1,190) (actual time=19,858.134..24,419.293 rows=95,083 loops=1)

  • Hash Cond: (tmd.rgn_id = rum.rgn_id)
2.          

CTE t_mreg_dstrt

3. 0.066 0.066 ↑ 1.0 48 1

Seq Scan on dstrt_lk dl (cost=0.00..6.63 rows=49 width=29) (actual time=0.006..0.066 rows=48 loops=1)

  • Filter: (((dept_id = '2'::numeric) AND (dstrt_id <> ALL ('{144,182,193}'::numeric[]))) OR ((dept_id = '3'::numeric) AND (dstrt_id = '35'::numeric)))
  • Rows Removed by Filter: 123
4.          

CTE t_fws_dt

5. 85.764 102.130 ↑ 1.8 71,052 1

Bitmap Heap Scan on mttr_dt (cost=3,515.74..44,060.94 rows=130,636 width=20) (actual time=21.111..102.130 rows=71,052 loops=1)

  • Recheck Cond: (mttr_dt_nm_id = '4'::numeric)
  • Filter: ((mttr_dt_type_id = '3'::numeric) AND ((actv_ind)::text = 'A'::text))
  • Rows Removed by Filter: 92578
  • Heap Blocks: exact=29789
6. 16.366 16.366 ↓ 1.0 163,630 1

Bitmap Index Scan on mttr_dt_mttr_dt_nm_id_idx (cost=0.00..3,483.08 rows=161,954 width=0) (actual time=16.366..16.366 rows=163,630 loops=1)

  • Index Cond: (mttr_dt_nm_id = '4'::numeric)
7.          

CTE t_reassgnt

8. 100.453 893.457 ↓ 149.8 156,716 1

Hash Join (cost=36,024.34..43,359.46 rows=1,046 width=44) (actual time=468.042..893.457 rows=156,716 loops=1)

  • Hash Cond: (ra.from_dstrt_id = dl_1.dstrt_id)
9. 76.316 792.878 ↓ 149.8 156,716 1

Subquery Scan on ra (cost=36,017.49..43,339.46 rows=1,046 width=25) (actual time=467.901..792.878 rows=156,716 loops=1)

  • Filter: (ra.rn = 1)
  • Rows Removed by Filter: 52670
10. 186.493 716.562 ↓ 1.0 209,386 1

WindowAgg (cost=36,017.49..40,724.47 rows=209,199 width=41) (actual time=467.899..716.562 rows=209,386 loops=1)

11. 392.775 530.069 ↓ 1.0 209,386 1

Sort (cost=36,017.49..36,540.49 rows=209,199 width=33) (actual time=467.892..530.069 rows=209,386 loops=1)

  • Sort Key: mad.mttr_id, mad.asgnt_actn_dt DESC, mad.crtd_dt DESC
  • Sort Method: external merge Disk: 9640kB
12. 137.294 137.294 ↓ 1.0 209,386 1

Seq Scan on mttr_asgnt_dtl mad (cost=0.00..11,807.54 rows=209,199 width=33) (actual time=0.006..137.294 rows=209,386 loops=1)

  • Filter: (asgnt_actn_id = '2'::numeric)
  • Rows Removed by Filter: 221859
13. 0.065 0.126 ↑ 1.0 171 1

Hash (cost=4.71..4.71 rows=171 width=24) (actual time=0.126..0.126 rows=171 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
14. 0.061 0.061 ↑ 1.0 171 1

Seq Scan on dstrt_lk dl_1 (cost=0.00..4.71 rows=171 width=24) (actual time=0.008..0.061 rows=171 loops=1)

15.          

CTE t_cntct_rep

16. 1,187.966 2,093.273 ↑ 1.2 278,978 1

GroupAggregate (cost=130,801.50..146,215.12 rows=339,115 width=76) (actual time=771.517..2,093.273 rows=278,978 loops=1)

  • Group Key: mc.mttr_id
17. 441.477 905.307 ↑ 1.2 343,434 1

Sort (cost=130,801.50..131,834.19 rows=413,076 width=33) (actual time=771.486..905.307 rows=343,434 loops=1)

  • Sort Key: mc.mttr_id
  • Sort Method: external merge Disk: 15984kB
18. 401.583 463.830 ↑ 1.2 343,434 1

Bitmap Heap Scan on mttr_cntct mc (cost=17,599.59..80,971.67 rows=413,076 width=33) (actual time=69.833..463.830 rows=343,434 loops=1)

  • Recheck Cond: (cntct_type_id = '14'::numeric)
  • Filter: (((row_type)::text = ANY ('{R,C}'::text[])) AND ((actv_ind)::text = 'A'::text))
  • Rows Removed by Filter: 471598
  • Heap Blocks: exact=47567
19. 62.247 62.247 ↓ 1.0 815,085 1

Bitmap Index Scan on mttr_cntct_idx6 (cost=0.00..17,496.32 rows=814,919 width=0) (actual time=62.247..62.247 rows=815,085 loops=1)

  • Index Cond: (cntct_type_id = '14'::numeric)
20.          

CTE t_bllbl_entty

21. 750.888 1,144.242 ↓ 1.7 148,277 1

GroupAggregate (cost=28,338.61..33,488.49 rows=89,590 width=108) (actual time=334.360..1,144.242 rows=148,277 loops=1)

  • Group Key: mkt_reg_bllbl_entty_mv.mttr_id
22. 331.815 393.354 ↑ 1.0 204,689 1

Sort (cost=28,338.61..28,850.33 rows=204,689 width=44) (actual time=334.321..393.354 rows=204,689 loops=1)

  • Sort Key: mkt_reg_bllbl_entty_mv.mttr_id
  • Sort Method: external sort Disk: 11104kB
23. 61.539 61.539 ↑ 1.0 204,689 1

Seq Scan on mkt_reg_bllbl_entty_mv (cost=0.00..3,981.89 rows=204,689 width=44) (actual time=0.010..61.539 rows=204,689 loops=1)

24.          

CTE t_tt_bllbl_entty

25. 548.096 1,586.692 ↑ 1.2 146,724 1

GroupAggregate (cost=28,911.54..67,562.10 rows=180,850 width=76) (actual time=481.680..1,586.692 rows=146,724 loops=1)

  • Group Key: m_1.mttr_id
26. 294.142 1,038.596 ↓ 1.0 180,852 1

Merge Join (cost=28,911.54..63,492.97 rows=180,850 width=46) (actual time=481.645..1,038.596 rows=180,852 loops=1)

  • Merge Cond: ((mbe.mttr_id)::text = (m_1.mttr_id)::text)
27. 347.145 544.639 ↓ 1.0 180,852 1

Sort (cost=28,910.99..29,363.12 rows=180,850 width=46) (actual time=481.623..544.639 rows=180,852 loops=1)

  • Sort Key: mbe.mttr_id
  • Sort Method: external merge Disk: 9800kB
28. 118.747 197.494 ↓ 1.0 180,852 1

Hash Join (cost=3.89..7,553.78 rows=180,850 width=46) (actual time=0.085..197.494 rows=180,852 loops=1)

  • Hash Cond: (mbe.bllbl_entty_id = bel.bllbl_entty_id)
29. 78.679 78.679 ↓ 1.0 180,852 1

Seq Scan on mttr_bllbl_entty mbe (cost=0.00..5,285.76 rows=180,850 width=17) (actual time=0.008..78.679 rows=180,852 loops=1)

  • Filter: ((tm_trckg_fl)::text = 'Y'::text)
  • Rows Removed by Filter: 33851
30. 0.037 0.068 ↑ 1.0 84 1

Hash (cost=2.84..2.84 rows=84 width=34) (actual time=0.068..0.068 rows=84 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
31. 0.031 0.031 ↑ 1.0 84 1

Seq Scan on bllbl_entty_lk bel (cost=0.00..2.84 rows=84 width=34) (actual time=0.006..0.031 rows=84 loops=1)

32. 199.815 199.815 ↑ 1.1 745,551 1

Index Only Scan using mttr_pkey on mttr m_1 (cost=0.42..32,071.54 rows=796,766 width=12) (actual time=0.014..199.815 rows=745,551 loops=1)

  • Heap Fetches: 13730
33.          

CTE t_crrnt_bllbl_entty

34. 571.832 1,674.177 ↑ 1.4 148,284 1

GroupAggregate (cost=32,454.74..72,066.19 rows=206,474 width=76) (actual time=531.064..1,674.177 rows=148,284 loops=1)

  • Group Key: m_2.mttr_id
35. 302.851 1,102.345 ↓ 1.0 206,476 1

Merge Join (cost=32,454.74..67,420.52 rows=206,474 width=46) (actual time=531.031..1,102.345 rows=206,476 loops=1)

  • Merge Cond: ((mbe_1.mttr_id)::text = (m_2.mttr_id)::text)
36. 386.873 602.096 ↓ 1.0 206,476 1

Sort (cost=32,454.18..32,970.37 rows=206,474 width=46) (actual time=531.008..602.096 rows=206,476 loops=1)

  • Sort Key: mbe_1.mttr_id
  • Sort Method: external merge Disk: 11208kB
37. 132.519 215.223 ↓ 1.0 206,476 1

Hash Join (cost=3.89..7,874.57 rows=206,474 width=46) (actual time=0.089..215.223 rows=206,476 loops=1)

  • Hash Cond: (mbe_1.bllbl_entty_id = bel_1.bllbl_entty_id)
38. 82.636 82.636 ↓ 1.0 206,476 1

Seq Scan on mttr_bllbl_entty mbe_1 (cost=0.00..5,285.76 rows=206,474 width=17) (actual time=0.008..82.636 rows=206,476 loops=1)

  • Filter: ((crrnt_fl)::text = 'Y'::text)
  • Rows Removed by Filter: 8227
39. 0.037 0.068 ↑ 1.0 84 1

Hash (cost=2.84..2.84 rows=84 width=34) (actual time=0.068..0.068 rows=84 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
40. 0.031 0.031 ↑ 1.0 84 1

Seq Scan on bllbl_entty_lk bel_1 (cost=0.00..2.84 rows=84 width=34) (actual time=0.006..0.031 rows=84 loops=1)

41. 197.398 197.398 ↑ 1.1 745,551 1

Index Only Scan using mttr_pkey on mttr m_2 (cost=0.42..32,071.54 rows=796,766 width=12) (actual time=0.015..197.398 rows=745,551 loops=1)

  • Heap Fetches: 13730
42.          

CTE t_clsd_dpstn

43. 2,550.962 5,461.648 ↓ 3,647.9 729,575 1

GroupAggregate (cost=239,308.41..268,472.69 rows=200 width=104) (actual time=2,148.284..5,461.648 rows=729,575 loops=1)

  • Group Key: md.mttr_id
44. 438.701 2,910.686 ↑ 1.1 786,708 1

Unique (cost=239,308.41..248,281.11 rows=897,270 width=104) (actual time=2,148.260..2,910.686 rows=786,708 loops=1)

45. 1,099.840 2,471.985 ↑ 1.1 814,325 1

Sort (cost=239,308.41..241,551.59 rows=897,270 width=104) (actual time=2,148.257..2,471.985 rows=814,325 loops=1)

  • Sort Key: md.mttr_id, (NULL::numeric), dcl.dpstn_code_ds
  • Sort Method: external merge Disk: 33480kB
46. 307.861 1,372.145 ↑ 1.1 814,325 1

Append (cost=5.61..52,450.02 rows=897,270 width=104) (actual time=0.128..1,372.145 rows=814,325 loops=1)

47. 366.297 502.008 ↑ 1.2 499,357 1

Hash Join (cost=5.61..20,903.97 rows=600,355 width=62) (actual time=0.128..502.008 rows=499,357 loops=1)

  • Hash Cond: (md.dpstn_code_id = dcl.dpstn_code_id)
48. 135.596 135.596 ↓ 1.0 604,534 1

Seq Scan on mttr_dpstn md (cost=0.00..13,303.24 rows=604,524 width=17) (actual time=0.005..135.596 rows=604,534 loops=1)

49. 0.055 0.115 ↑ 1.0 144 1

Hash (cost=3.81..3.81 rows=144 width=23) (actual time=0.115..0.115 rows=144 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
50. 0.060 0.060 ↑ 1.0 144 1

Seq Scan on dpstn_code_lk dcl (cost=0.00..3.81 rows=144 width=23) (actual time=0.007..0.060 rows=144 loops=1)

  • Filter: (dpstn_code_id <> '94'::numeric)
  • Rows Removed by Filter: 1
51. 220.061 562.276 ↓ 1.1 314,968 1

Hash Left Join (cost=7.07..22,573.35 rows=296,915 width=49) (actual time=0.162..562.276 rows=314,968 loops=1)

  • Hash Cond: (ma.actn_type_id = atl.actn_type_id)
52. 169.644 342.186 ↓ 1.1 314,968 1

Hash Left Join (cost=5.26..15,927.04 rows=296,915 width=35) (actual time=0.126..342.186 rows=314,968 loops=1)

  • Hash Cond: (ma.dpstn_code_id = dcl_1.dpstn_code_id)
53. 172.437 172.437 ↓ 1.1 314,968 1

Seq Scan on mttr_actn ma (cost=0.00..12,173.77 rows=296,915 width=22) (actual time=0.010..172.437 rows=314,968 loops=1)

  • Filter: ((actn_st_id = ANY ('{2,3}'::numeric[])) AND ((actv_ind)::text = 'A'::text))
  • Rows Removed by Filter: 45151
54. 0.059 0.105 ↑ 1.0 145 1

Hash (cost=3.45..3.45 rows=145 width=23) (actual time=0.105..0.105 rows=145 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
55. 0.046 0.046 ↑ 1.0 145 1

Seq Scan on dpstn_code_lk dcl_1 (cost=0.00..3.45 rows=145 width=23) (actual time=0.006..0.046 rows=145 loops=1)

56. 0.015 0.029 ↑ 1.0 36 1

Hash (cost=1.36..1.36 rows=36 width=24) (actual time=0.029..0.029 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
57. 0.014 0.014 ↑ 1.0 36 1

Seq Scan on actn_type_lk atl (cost=0.00..1.36 rows=36 width=24) (actual time=0.004..0.014 rows=36 loops=1)

58.          

CTE t_cause_lst

59. 2,438.584 4,406.460 ↓ 1.0 655,967 1

GroupAggregate (cost=145,611.84..169,276.07 rows=655,963 width=76) (actual time=1,622.790..4,406.460 rows=655,967 loops=1)

  • Group Key: mc_1.mttr_id
60. 1,166.260 1,967.876 ↓ 1.0 921,656 1

Sort (cost=145,611.84..147,915.97 rows=921,652 width=39) (actual time=1,622.761..1,967.876 rows=921,656 loops=1)

  • Sort Key: mc_1.mttr_id
  • Sort Method: external merge Disk: 42784kB
61. 591.957 801.616 ↓ 1.0 921,656 1

Hash Join (cost=31.15..29,100.91 rows=921,652 width=39) (actual time=0.595..801.616 rows=921,656 loops=1)

  • Hash Cond: (mc_1.cause_code_id = ccl.cause_code_id)
62. 209.080 209.080 ↓ 1.0 921,656 1

Seq Scan on mttr_cause mc_1 (cost=0.00..17,443.52 rows=921,652 width=17) (actual time=0.007..209.080 rows=921,656 loops=1)

63. 0.312 0.579 ↑ 1.0 851 1

Hash (cost=20.51..20.51 rows=851 width=27) (actual time=0.579..0.579 rows=851 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 58kB
64. 0.267 0.267 ↑ 1.0 851 1

Seq Scan on cause_code_lk ccl (cost=0.00..20.51 rows=851 width=27) (actual time=0.006..0.267 rows=851 loops=1)

65.          

CTE t_legal_start_dt

66. 7.500 91.032 ↓ 37.0 9,573 1

HashAggregate (cost=61,069.62..61,072.21 rows=259 width=48) (actual time=87.928..91.032 rows=9,573 loops=1)

  • Group Key: a.mttr_id, a.legal_start_dt
67. 3.599 83.532 ↓ 37.0 9,573 1

Append (cost=5,839.70..61,068.33 rows=259 width=48) (actual time=25.028..83.532 rows=9,573 loops=1)

68. 3.868 40.157 ↓ 1,538.2 9,229 1

Subquery Scan on a (cost=5,839.70..5,888.62 rows=6 width=20) (actual time=25.027..40.157 rows=9,229 loops=1)

  • Filter: (a.rn = 1)
  • Rows Removed by Filter: 70
69. 9.215 36.289 ↓ 7.6 9,299 1

WindowAgg (cost=5,839.70..5,873.33 rows=1,223 width=66) (actual time=25.025..36.289 rows=9,299 loops=1)

70. 8.940 27.074 ↓ 7.6 9,299 1

Sort (cost=5,839.70..5,842.76 rows=1,223 width=38) (actual time=25.019..27.074 rows=9,299 loops=1)

  • Sort Key: mad_1.mttr_id, mad_1.to_dstrt_id, mad_1.asgnt_actn_dt DESC, mad_1.crtd_dt DESC
  • Sort Method: quicksort Memory: 1111kB
71. 4.472 18.134 ↓ 7.6 9,299 1

Nested Loop (cost=79.36..5,776.99 rows=1,223 width=38) (actual time=2.621..18.134 rows=9,299 loops=1)

72. 0.044 0.044 ↑ 1.0 1 1

Seq Scan on dstrt_lk dl_2 (cost=0.00..5.14 rows=1 width=5) (actual time=0.039..0.044 rows=1 loops=1)

  • Filter: (rgn_id = '11'::numeric)
  • Rows Removed by Filter: 170
73. 11.740 13.618 ↓ 5.4 9,299 1

Bitmap Heap Scan on mttr_asgnt_dtl mad_1 (cost=79.36..5,754.70 rows=1,715 width=38) (actual time=2.576..13.618 rows=9,299 loops=1)

  • Recheck Cond: (to_dstrt_id = dl_2.dstrt_id)
  • Filter: (asgnt_actn_id = '2'::numeric)
  • Rows Removed by Filter: 9755
  • Heap Blocks: exact=5511
74. 1.878 1.878 ↓ 5.4 19,054 1

Bitmap Index Scan on mttr_asgnt_dtl_idx3 (cost=0.00..78.94 rows=3,535 width=0) (actual time=1.878..1.878 rows=19,054 loops=1)

  • Index Cond: (to_dstrt_id = dl_2.dstrt_id)
75. 4.937 39.776 ↓ 1.4 344 1

Gather (cost=1,217.33..55,177.11 rows=253 width=20) (actual time=7.174..39.776 rows=344 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
76. 13.429 34.839 ↓ 1.1 115 3

Nested Loop Anti Join (cost=217.33..54,151.81 rows=105 width=20) (actual time=2.105..34.839 rows=115 loops=3)

77. 16.300 21.402 ↓ 12.7 1,618 3

Nested Loop (cost=216.48..53,528.90 rows=127 width=20) (actual time=1.302..21.402 rows=1,618 loops=3)

78. 2.615 5.098 ↑ 1.2 3,253 3

Parallel Bitmap Heap Scan on mttr m_3 (cost=216.05..23,662.06 rows=4,066 width=12) (actual time=1.277..5.098 rows=3,253 loops=3)

  • Recheck Cond: (dstrt_id = '35'::numeric)
  • Heap Blocks: exact=2357
79. 2.483 2.483 ↓ 1.0 9,762 1

Bitmap Index Scan on mttr_dstrt_id_idx (cost=0.00..213.61 rows=9,758 width=0) (actual time=2.483..2.483 rows=9,762 loops=1)

  • Index Cond: (dstrt_id = '35'::numeric)
80. 0.004 0.004 ↓ 0.0 0 9,759

Index Scan using mttr_dt_combo_idx on mttr_dt md_1 (cost=0.43..7.34 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=9,759)

  • Index Cond: (((mttr_id)::text = (m_3.mttr_id)::text) AND (mttr_dt_nm_id = '24'::numeric) AND (mttr_dt_type_id = '3'::numeric))
  • Filter: ((actv_ind)::text = 'A'::text)
81. 0.002 0.008 ↑ 2.0 1 4,853

Nested Loop (cost=0.85..5.04 rows=2 width=12) (actual time=0.008..0.008 rows=1 loops=4,853)

82. 0.003 0.003 ↑ 2.0 1 4,853

Index Scan using mttr_asgnt_dtl_mttr_id_idx on mttr_asgnt_dtl (cost=0.42..3.67 rows=2 width=12) (actual time=0.003..0.003 rows=1 loops=4,853)

  • Index Cond: ((m_3.mttr_id)::text = (mttr_id)::text)
  • Filter: (asgnt_actn_id = '2'::numeric)
  • Rows Removed by Filter: 0
83. 0.003 0.003 ↑ 1.0 1 4,509

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_1 (cost=0.42..0.68 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=4,509)

  • Index Cond: (mttr_id = (mttr_asgnt_dtl.mttr_id)::text)
  • Heap Fetches: 1491
84.          

CTE t_legal_clsd_dt

85. 0.203 5.947 ↓ 37.0 407 1

Subquery Scan on a_1 (cost=13,409.51..13,483.58 rows=11 width=48) (actual time=5.152..5.947 rows=407 loops=1)

  • Filter: (a_1.rn = 1)
  • Rows Removed by Filter: 135
86. 0.484 5.744 ↑ 4.2 542 1

WindowAgg (cost=13,409.51..13,455.09 rows=2,279 width=56) (actual time=5.150..5.744 rows=542 loops=1)

87. 0.499 5.260 ↑ 4.2 542 1

Sort (cost=13,409.51..13,415.21 rows=2,279 width=48) (actual time=5.144..5.260 rows=542 loops=1)

  • Sort Key: md_2.mttr_id, md_2.mttr_dt
  • Sort Method: quicksort Memory: 67kB
88. 0.525 4.761 ↑ 4.2 542 1

HashAggregate (cost=13,236.83..13,259.62 rows=2,279 width=48) (actual time=4.582..4.761 rows=542 loops=1)

  • Group Key: md_2.mttr_id, md_2.mttr_dt
89. 0.276 4.236 ↑ 3.2 706 1

Append (cost=49.17..13,225.44 rows=2,279 width=48) (actual time=0.041..4.236 rows=706 loops=1)

90. 0.201 0.225 ↑ 11.2 156 1

Bitmap Heap Scan on mttr_dt md_2 (cost=49.17..7,038.69 rows=1,753 width=20) (actual time=0.040..0.225 rows=156 loops=1)

  • Recheck Cond: (mttr_dt_nm_id = '91'::numeric)
  • Filter: (((actv_ind)::text = 'A'::text) AND (mttr_dt_type_id = '3'::numeric))
  • Heap Blocks: exact=141
91. 0.024 0.024 ↑ 13.9 156 1

Bitmap Index Scan on mttr_dt_mttr_dt_nm_id_idx (cost=0.00..48.73 rows=2,173 width=0) (actual time=0.024..0.024 rows=156 loops=1)

  • Index Cond: (mttr_dt_nm_id = '91'::numeric)
92. 0.555 3.735 ↓ 1.0 550 1

Nested Loop (cost=0.85..6,163.96 rows=526 width=20) (actual time=0.039..3.735 rows=550 loops=1)

93. 0.430 0.430 ↓ 1.0 550 1

Index Scan using mttr_crspc_type_id_idx on mttr_crspc mc_2 (cost=0.42..1,752.20 rows=530 width=18) (actual time=0.025..0.430 rows=550 loops=1)

  • Index Cond: (crspc_type_id = '47'::numeric)
94. 2.750 2.750 ↑ 1.0 1 550

Index Scan using mttr_crspc_dt_crspc_id_idx on mttr_crspc_dt mcd (cost=0.43..8.31 rows=1 width=14) (actual time=0.004..0.005 rows=1 loops=550)

  • Index Cond: (mttr_crspc_id = mc_2.mttr_crspc_id)
  • Filter: (crspc_dt_type_id = '1'::numeric)
  • Rows Removed by Filter: 2
95.          

CTE t_rvw_prd_start_dt

96. 108.231 124.898 ↓ 1.2 161,613 1

Bitmap Heap Scan on mttr_dt md_3 (cost=3,559.10..44,139.19 rows=132,245 width=20) (actual time=21.080..124.898 rows=161,613 loops=1)

  • Recheck Cond: (mttr_dt_nm_id = '32'::numeric)
  • Filter: ((mttr_dt_type_id = '3'::numeric) AND ((actv_ind)::text = 'A'::text))
  • Rows Removed by Filter: 361
  • Heap Blocks: exact=27671
97. 16.667 16.667 ↑ 1.0 161,974 1

Bitmap Index Scan on mttr_dt_mttr_dt_nm_id_idx (cost=0.00..3,526.04 rows=163,948 width=0) (actual time=16.667..16.667 rows=161,974 loops=1)

  • Index Cond: (mttr_dt_nm_id = '32'::numeric)
98.          

CTE t_rvw_prd_end_dt

99. 108.532 125.365 ↓ 1.2 160,399 1

Bitmap Heap Scan on mttr_dt md_4 (cost=3,559.99..44,142.09 rows=132,337 width=20) (actual time=21.221..125.365 rows=160,399 loops=1)

  • Recheck Cond: (mttr_dt_nm_id = '33'::numeric)
  • Filter: ((mttr_dt_type_id = '3'::numeric) AND ((actv_ind)::text = 'A'::text))
  • Rows Removed by Filter: 816
  • Heap Blocks: exact=27658
100. 16.833 16.833 ↑ 1.0 161,215 1

Bitmap Index Scan on mttr_dt_mttr_dt_nm_id_idx (cost=0.00..3,526.90 rows=164,063 width=0) (actual time=16.833..16.833 rows=161,215 loops=1)

  • Index Cond: (mttr_dt_nm_id = '33'::numeric)
101. 57.926 24,173.606 ↓ 4.8 95,083 1

Hash Left Join (cost=155,467.01..194,776.63 rows=19,889 width=835) (actual time=19,858.097..24,173.606 rows=95,083 loops=1)

  • Hash Cond: ((m.mttr_id)::text = (tlcd.mttr_id)::text)
102. 61.819 24,109.355 ↓ 4.8 95,083 1

Hash Left Join (cost=155,466.65..194,701.68 rows=19,889 width=827) (actual time=19,851.761..24,109.355 rows=95,083 loops=1)

  • Hash Cond: ((m.mttr_id)::text = (tlsd.mttr_id)::text)
103. 349.334 23,947.485 ↓ 4.8 95,083 1

Hash Left Join (cost=155,458.23..193,997.09 rows=19,889 width=819) (actual time=19,751.700..23,947.485 rows=95,083 loops=1)

  • Hash Cond: ((m.mttr_id)::text = (tcd.mttr_id)::text)
104. 509.243 17,226.501 ↓ 4.8 95,083 1

Hash Right Join (cost=155,451.73..193,443.59 rows=19,889 width=755) (actual time=13,377.165..17,226.501 rows=95,083 loops=1)

  • Hash Cond: ((tcl.mttr_id)::text = (m.mttr_id)::text)
105. 4,962.963 4,962.963 ↓ 1.0 655,967 1

CTE Scan on t_cause_lst tcl (cost=0.00..13,119.26 rows=655,963 width=104) (actual time=1,622.794..4,962.963 rows=655,967 loops=1)

106. 103.937 11,754.295 ↓ 4.8 95,083 1

Hash (cost=153,454.12..153,454.12 rows=19,889 width=691) (actual time=11,754.295..11,754.295 rows=95,083 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 4) Memory Usage: 4213kB
107. 159.259 11,650.358 ↓ 4.8 95,083 1

Hash Right Join (cost=146,221.09..153,454.12 rows=19,889 width=691) (actual time=11,279.266..11,650.358 rows=95,083 loops=1)

  • Hash Cond: ((trped.mttr_id)::text = (m.mttr_id)::text)
108. 233.139 233.139 ↓ 1.2 160,399 1

CTE Scan on t_rvw_prd_end_dt trped (cost=0.00..2,646.74 rows=132,337 width=48) (actual time=21.225..233.139 rows=160,399 loops=1)

109. 102.240 11,257.960 ↓ 4.8 95,083 1

Hash (cost=144,243.47..144,243.47 rows=19,889 width=683) (actual time=11,257.960..11,257.960 rows=95,083 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 4) Memory Usage: 4141kB
110. 160.387 11,155.720 ↓ 4.8 95,083 1

Hash Right Join (cost=137,033.64..144,243.47 rows=19,889 width=683) (actual time=10,782.981..11,155.720 rows=95,083 loops=1)

  • Hash Cond: ((trpsd.mttr_id)::text = (m.mttr_id)::text)
111. 233.505 233.505 ↓ 1.2 161,613 1

CTE Scan on t_rvw_prd_start_dt trpsd (cost=0.00..2,644.90 rows=132,245 width=48) (actual time=21.083..233.505 rows=161,613 loops=1)

112. 115.611 10,761.828 ↓ 4.8 95,083 1

Hash (cost=135,075.03..135,075.03 rows=19,889 width=675) (actual time=10,761.828..10,761.828 rows=95,083 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 4) Memory Usage: 4036kB
113. 61.901 10,646.217 ↓ 4.8 95,083 1

Hash Left Join (cost=114,383.28..135,075.03 rows=19,889 width=675) (actual time=8,704.483..10,646.217 rows=95,083 loops=1)

  • Hash Cond: (m.case_wt_id = cwl.case_wt_id)
114. 71.851 10,584.308 ↓ 4.8 95,083 1

Hash Left Join (cost=114,382.19..134,826.08 rows=19,889 width=668) (actual time=8,704.469..10,584.308 rows=95,083 loops=1)

  • Hash Cond: (mttr_type_lk.mttr_ctgry_id = mcl.mttr_ctgry_id)
115. 245.985 10,512.446 ↓ 4.8 95,083 1

Hash Right Join (cost=114,381.13..134,632.76 rows=19,889 width=658) (actual time=8,704.449..10,512.446 rows=95,083 loops=1)

  • Hash Cond: ((tcr.mttr_id)::text = (m.mttr_id)::text)
116. 2,333.601 2,333.601 ↑ 1.2 278,978 1

CTE Scan on t_cntct_rep tcr (cost=0.00..6,782.30 rows=339,115 width=104) (actual time=771.521..2,333.601 rows=278,978 loops=1)

117. 101.548 7,932.860 ↓ 4.8 95,083 1

Hash (cost=112,617.51..112,617.51 rows=19,889 width=594) (actual time=7,932.860..7,932.860 rows=95,083 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 4) Memory Usage: 4033kB
118. 171.483 7,831.312 ↓ 4.8 95,083 1

Hash Right Join (cost=99,848.22..112,617.51 rows=19,889 width=594) (actual time=6,388.347..7,831.312 rows=95,083 loops=1)

  • Hash Cond: ((tcbe.mttr_id)::text = (m.mttr_id)::text)
119. 1,802.564 1,802.564 ↑ 1.4 148,284 1

CTE Scan on t_crrnt_bllbl_entty tcbe (cost=0.00..4,129.48 rows=206,474 width=104) (actual time=531.067..1,802.564 rows=148,284 loops=1)

120. 95.130 5,857.265 ↓ 4.8 95,083 1

Hash (cost=98,239.60..98,239.60 rows=19,889 width=530) (actual time=5,857.265..5,857.265 rows=95,083 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 4) Memory Usage: 4033kB
121. 178.585 5,762.135 ↓ 4.8 95,083 1

Hash Right Join (cost=87,042.28..98,239.60 rows=19,889 width=530) (actual time=4,347.604..5,762.135 rows=95,083 loops=1)

  • Hash Cond: ((ttbe.mttr_id)::text = (m.mttr_id)::text)
122. 1,717.643 1,717.643 ↑ 1.2 146,724 1

CTE Scan on t_tt_bllbl_entty ttbe (cost=0.00..3,617.00 rows=180,850 width=104) (actual time=481.683..1,717.643 rows=146,724 loops=1)

123. 89.536 3,865.907 ↓ 4.8 95,083 1

Hash (cost=85,588.66..85,588.66 rows=19,889 width=466) (actual time=3,865.907..3,865.907 rows=95,083 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 8 (originally 4) Memory Usage: 4033kB
124. 163.260 3,776.371 ↓ 4.8 95,083 1

Hash Right Join (cost=78,966.54..85,588.66 rows=19,889 width=466) (actual time=2,670.209..3,776.371 rows=95,083 loops=1)

  • Hash Cond: ((tbe.mttr_id)::text = (m.mttr_id)::text)
125. 1,277.289 1,277.289 ↓ 1.7 148,277 1

CTE Scan on t_bllbl_entty tbe (cost=0.00..1,791.80 rows=89,590 width=136) (actual time=334.363..1,277.289 rows=148,277 loops=1)

126. 74.273 2,335.822 ↓ 4.8 95,083 1

Hash (cost=77,745.93..77,745.93 rows=19,889 width=370) (actual time=2,335.822..2,335.822 rows=95,083 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 8 (originally 2) Memory Usage: 3969kB
127. 88.381 2,261.549 ↓ 4.8 95,083 1

Hash Right Join (cost=71,360.71..77,745.93 rows=19,889 width=370) (actual time=2,043.757..2,261.549 rows=95,083 loops=1)

  • Hash Cond: ((tfd.mttr_id)::text = (m.mttr_id)::text)
128. 150.576 150.576 ↑ 1.8 71,052 1

CTE Scan on t_fws_dt tfd (cost=0.00..2,612.72 rows=130,636 width=48) (actual time=21.114..150.576 rows=71,052 loops=1)

129. 76.810 2,022.592 ↓ 4.8 95,083 1

Hash (cost=70,160.10..70,160.10 rows=19,889 width=362) (actual time=2,022.592..2,022.592 rows=95,083 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 8 (originally 2) Memory Usage: 3969kB
130. 59.608 1,945.782 ↓ 4.8 95,083 1

Hash Left Join (cost=68,990.95..70,160.10 rows=19,889 width=362) (actual time=1,112.191..1,945.782 rows=95,083 loops=1)

  • Hash Cond: (m.mttr_sub_type_id = mstl.mttr_sub_type_id)
131. 61.407 1,885.945 ↓ 4.8 95,083 1

Hash Join (cost=68,979.44..69,897.80 rows=19,889 width=335) (actual time=1,111.951..1,885.945 rows=95,083 loops=1)

  • Hash Cond: (m.mttr_st_id = msl.mttr_st_id)
132. 158.092 1,824.528 ↓ 4.8 95,083 1

Hash Right Join (cost=68,978.38..69,704.48 rows=19,889 width=328) (actual time=1,111.936..1,824.528 rows=95,083 loops=1)

  • Hash Cond: ((tr.mttr_id)::text = (m.mttr_id)::text)
133. 1,022.569 1,022.569 ↓ 149.8 156,716 1

CTE Scan on t_reassgnt tr (cost=0.00..20.92 rows=1,046 width=126) (actual time=468.045..1,022.569 rows=156,716 loops=1)

134. 83.379 643.867 ↓ 4.8 95,083 1

Hash (cost=68,068.76..68,068.76 rows=19,889 width=242) (actual time=643.867..643.867 rows=95,083 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 8 (originally 2) Memory Usage: 3969kB
135. 76.322 560.488 ↓ 4.8 95,083 1

Hash Join (cost=4,669.01..68,068.76 rows=19,889 width=242) (actual time=87.553..560.488 rows=95,083 loops=1)

  • Hash Cond: (m.dstrt_id = tmd.dstrt_id)
136. 0.000 484.046 ↓ 1.7 95,083 1

Gather (cost=4,667.42..67,661.26 rows=55,204 width=125) (actual time=87.427..484.046 rows=95,083 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
137. 25.825 499.757 ↓ 1.4 31,694 3

Hash Join (cost=3,667.42..61,140.86 rows=23,002 width=125) (actual time=84.297..499.757 rows=31,694 loops=3)

  • Hash Cond: (m.mttr_type_id = x.mttr_type_id)
138. 167.944 473.677 ↓ 1.3 31,694 3

Hash Join (cost=3,658.37..60,806.41 rows=23,665 width=107) (actual time=84.018..473.677 rows=31,694 loops=3)

  • Hash Cond: ((m.mttr_id)::text = (tcf.mttr_id)::text)
139. 222.296 222.296 ↓ 1.3 248,883 3

Parallel Seq Scan on mttr m (cost=0.00..50,000.75 rows=198,304 width=84) (actual time=0.006..222.296 rows=248,883 loops=3)

  • Filter: (((mttr_sub_type_id IS NULL) OR (mttr_sub_type_id <> ALL ('{109,31}'::numeric[]))) AND (mttr_type_id <> '8'::numeric) AND (mttr_st_id <> '4'::numeric))
  • Rows Removed by Filter: 16752
140. 47.518 83.437 ↑ 1.0 95,083 3

Hash (cost=1,726.83..1,726.83 rows=95,083 width=35) (actual time=83.437..83.437 rows=95,083 loops=3)

  • Buckets: 65536 Batches: 2 Memory Usage: 3683kB
141. 35.919 35.919 ↑ 1.0 95,083 3

Seq Scan on mkt_reg_firm_lst_mv tcf (cost=0.00..1,726.83 rows=95,083 width=35) (actual time=0.009..35.919 rows=95,083 loops=3)

142. 0.055 0.255 ↓ 1.0 105 3

Hash (cost=7.75..7.75 rows=104 width=27) (actual time=0.255..0.255 rows=105 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
143. 0.090 0.200 ↓ 1.0 105 3

Hash Join (cost=3.34..7.75 rows=104 width=27) (actual time=0.086..0.200 rows=105 loops=3)

  • Hash Cond: (mttr_type_lk.mttr_type_id = x.mttr_type_id)
144. 0.038 0.038 ↓ 1.0 108 3

Seq Scan on mttr_type_lk (cost=0.00..3.07 rows=107 width=23) (actual time=0.007..0.038 rows=108 loops=3)

145. 0.040 0.072 ↓ 1.0 105 3

Hash (cost=2.04..2.04 rows=104 width=4) (actual time=0.072..0.072 rows=105 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
146. 0.032 0.032 ↓ 1.0 105 3

Seq Scan on xcld_fp_mttr_type_id_tbl x (cost=0.00..2.04 rows=104 width=4) (actual time=0.004..0.032 rows=105 loops=3)

147. 0.019 0.120 ↑ 1.0 48 1

Hash (cost=0.98..0.98 rows=49 width=122) (actual time=0.120..0.120 rows=48 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
148. 0.101 0.101 ↑ 1.0 48 1

CTE Scan on t_mreg_dstrt tmd (cost=0.00..0.98 rows=49 width=122) (actual time=0.009..0.101 rows=48 loops=1)

149. 0.005 0.010 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=12) (actual time=0.010..0.010 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
150. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on mttr_st_lk msl (cost=0.00..1.03 rows=3 width=12) (actual time=0.004..0.005 rows=3 loops=1)

151. 0.133 0.229 ↓ 1.0 291 1

Hash (cost=7.89..7.89 rows=289 width=32) (actual time=0.229..0.229 rows=291 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
152. 0.096 0.096 ↓ 1.0 291 1

Seq Scan on mttr_sub_type_lk mstl (cost=0.00..7.89 rows=289 width=32) (actual time=0.008..0.096 rows=291 loops=1)

153. 0.004 0.011 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=10) (actual time=0.011..0.011 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
154. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on mttr_ctgry_lk mcl (cost=0.00..1.03 rows=3 width=10) (actual time=0.006..0.007 rows=3 loops=1)

155. 0.003 0.008 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=12) (actual time=0.008..0.008 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
156. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on case_wt_lk cwl (cost=0.00..1.04 rows=4 width=12) (actual time=0.004..0.005 rows=4 loops=1)

157. 343.685 6,371.650 ↓ 3,647.9 729,575 1

Hash (cost=4.00..4.00 rows=200 width=104) (actual time=6,371.650..6,371.650 rows=729,575 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 16 (originally 1) Memory Usage: 3585kB
158. 6,027.965 6,027.965 ↓ 3,647.9 729,575 1

CTE Scan on t_clsd_dpstn tcd (cost=0.00..4.00 rows=200 width=104) (actual time=2,148.287..6,027.965 rows=729,575 loops=1)

159. 3.104 100.051 ↓ 37.0 9,573 1

Hash (cost=5.18..5.18 rows=259 width=48) (actual time=100.051..100.051 rows=9,573 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 615kB
160. 96.947 96.947 ↓ 37.0 9,573 1

CTE Scan on t_legal_start_dt tlsd (cost=0.00..5.18 rows=259 width=48) (actual time=87.931..96.947 rows=9,573 loops=1)

161. 0.141 6.325 ↓ 37.0 407 1

Hash (cost=0.22..0.22 rows=11 width=48) (actual time=6.325..6.325 rows=407 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
162. 6.184 6.184 ↓ 37.0 407 1

CTE Scan on t_legal_clsd_dt tlcd (cost=0.00..0.22 rows=11 width=48) (actual time=5.154..6.184 rows=407 loops=1)

163. 0.008 0.015 ↑ 26.7 6 1

Hash (cost=11.60..11.60 rows=160 width=332) (actual time=0.015..0.015 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
164. 0.007 0.007 ↑ 26.7 6 1

Seq Scan on rgn_unit_mppng rum (cost=0.00..11.60 rows=160 width=332) (actual time=0.005..0.007 rows=6 loops=1)