explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y2IU : Optimization for: enable_nestloop_on_2_unions; plan #3BnJ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 611,875.766 ↑ 1.0 1 1

Aggregate (cost=4,618,638.24..4,618,638.25 rows=1 width=8) (actual time=611,875.766..611,875.766 rows=1 loops=1)

2. 0.001 611,875.764 ↓ 0.0 0 1

Unique (cost=4,618,638.11..4,618,638.21 rows=2 width=1,410) (actual time=611,875.764..611,875.764 rows=0 loops=1)

3. 0.011 611,875.763 ↓ 0.0 0 1

Sort (cost=4,618,638.11..4,618,638.11 rows=2 width=1,410) (actual time=611,875.763..611,875.763 rows=0 loops=1)

  • Sort Key: ('One'::text), ((mttr.crtd_dt)::date), mttr.mttr_id, o3.orgn_ctgry_ds, zz5.prior_bllbl_entty_cd, b2.bllbl_entty_cd, (substr(btrim(zz8.cmmnt_nt), 4000, 1)), ((mttr_bllbl_entty_1.last_updt_dt)::date), zz5.last_updt_dt_prior, (sum(CASE WHEN (tm_entry.bllbl_entty_id = ANY ('{8,15}'::numeric[])) THEN tm_entry.actvy_hours_ct WHEN (tm_entry.bllbl_entty_id = '23'::numeric) THEN (tm_entry.actvy_hours_ct * 0.05) WHEN (tm_entry.bllbl_entty_id = '24'::numeric) THEN (tm_entry.actvy_hours_ct * 0.04) WHEN (tm_entry.bllbl_entty_id = '25'::numeric) THEN (tm_entry.actvy_hours_ct * 0.03) WHEN (tm_entry.bllbl_entty_id = '26'::numeric) THEN (tm_entry.actvy_hours_ct * 0.02) ELSE NULL::numeric END)), d.dstrt_cd, d.dstrt_ds, zz9.staff_nm, zz7.mp_id, zz10.prdct_ds, zz10.prdct_sym, zz6.cause_code_ds, mttr.mttr_ds, mttr.asctd_mttr_id, mttr.crtd_dt
  • Sort Method: quicksort Memory: 25kB
4. 0.002 611,875.752 ↓ 0.0 0 1

Append (cost=2,031,940.50..4,618,638.10 rows=2 width=1,410) (actual time=611,875.752..611,875.752 rows=0 loops=1)

5. 0.001 2,750.227 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,031,940.50..2,330,569.43 rows=1 width=301) (actual time=2,750.227..2,750.227 rows=0 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (tm_entry.mttr_id)::text)
6. 0.001 2,750.226 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,496,047.79..1,792,281.97 rows=1 width=233) (actual time=2,750.226..2,750.226 rows=0 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz10.mttr_id)::text)
7. 0.001 2,750.225 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,182,961.43..1,439,571.94 rows=1 width=208) (actual time=2,750.225..2,750.225 rows=0 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz9.mttr_id)::text)
8. 0.000 2,750.224 ↓ 0.0 0 1

Nested Loop Left Join (cost=576,889.27..728,175.30 rows=1 width=176) (actual time=2,750.224..2,750.224 rows=0 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz8.mttr_id)::text)
9. 0.001 2,750.224 ↓ 0.0 0 1

Nested Loop Left Join (cost=457,818.02..607,546.95 rows=1 width=144) (actual time=2,750.224..2,750.224 rows=0 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz7.mttr_id)::text)
10. 0.001 2,750.223 ↓ 0.0 0 1

Nested Loop Left Join (cost=320,213.99..466,236.23 rows=1 width=140) (actual time=2,750.223..2,750.223 rows=0 loops=1)

  • Join Filter: ((mttr.mttr_id)::text = (zz6.mttr_id)::text)
11. 448.387 2,750.222 ↓ 0.0 0 1

Nested Loop (cost=88,868.67..204,879.65 rows=1 width=118) (actual time=2,750.222..2,750.222 rows=0 loops=1)

  • Join Filter: ((x.mttr_id)::text = (x_2.mttr_id)::text)
  • Rows Removed by Join Filter: 1546844
12. 0.000 24.755 ↑ 2.0 7,852 1

Gather (cost=1,016.47..14,624.54 rows=15,402 width=39) (actual time=1.672..24.755 rows=7,852 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 12.601 150.009 ↑ 2.5 2,617 3

Nested Loop (cost=16.47..12,084.34 rows=6,418 width=39) (actual time=0.308..150.009 rows=2,617 loops=3)

14. 73.879 137.405 ↑ 2.5 2,617 3

Hash Join (cost=16.04..8,680.29 rows=6,418 width=27) (actual time=0.287..137.405 rows=2,617 loops=3)

  • Hash Cond: (mttr_orgn.orgn_id = o2.orgn_id)
15. 63.462 63.462 ↑ 1.2 192,237 3

Parallel Seq Scan on mttr_orgn (cost=0.00..7,698.96 rows=240,296 width=17) (actual time=0.006..63.462 rows=192,237 loops=3)

16. 0.013 0.064 ↑ 1.0 18 3

Hash (cost=15.82..15.82 rows=18 width=20) (actual time=0.064..0.064 rows=18 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
17. 0.012 0.051 ↑ 1.0 18 3

Nested Loop (cost=4.41..15.82 rows=18 width=20) (actual time=0.028..0.051 rows=18 loops=3)

18. 0.017 0.017 ↑ 1.0 1 3

Seq Scan on orgn_ctgry_lk o3 (cost=0.00..2.00 rows=1 width=20) (actual time=0.011..0.017 rows=1 loops=3)

  • Filter: (orgn_ctgry_id = '18'::numeric)
  • Rows Removed by Filter: 79
19. 0.011 0.022 ↑ 1.0 18 3

Bitmap Heap Scan on orgn_lk o2 (cost=4.41..13.64 rows=18 width=10) (actual time=0.014..0.022 rows=18 loops=3)

  • Recheck Cond: (orgn_ctgry_id = '18'::numeric)
  • Heap Blocks: exact=4
20. 0.011 0.011 ↑ 1.0 18 3

Bitmap Index Scan on tmporgn_lk_idx1 (cost=0.00..4.41 rows=18 width=0) (actual time=0.011..0.011 rows=18 loops=3)

  • Index Cond: (orgn_ctgry_id = '18'::numeric)
21. 0.003 0.003 ↑ 1.0 1 7,852

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_2 (cost=0.42..0.53 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=7,852)

  • Index Cond: (mttr_id = (mttr_orgn.mttr_id)::text)
  • Heap Fetches: 3
22. 349.133 2,277.080 ↓ 49.2 197 7,852

Materialize (cost=87,852.20..189,331.00 rows=4 width=115) (actual time=0.183..0.290 rows=197 loops=7,852)

23. 0.158 1,927.947 ↓ 49.2 197 1

Nested Loop (cost=87,852.20..189,330.98 rows=4 width=115) (actual time=1,435.828..1,927.947 rows=197 loops=1)

24. 0.754 1,927.198 ↓ 49.2 197 1

Nested Loop Anti Join (cost=87,851.77..189,297.21 rows=4 width=103) (actual time=1,435.815..1,927.198 rows=197 loops=1)

25. 66.319 1,922.736 ↓ 154.5 618 1

Hash Right Join (cost=87,850.93..189,254.21 rows=4 width=103) (actual time=1,435.797..1,922.736 rows=618 loops=1)

  • Hash Cond: ((mttr_bllbl_entty_1.mttr_id)::text = (mttr.mttr_id)::text)
26. 76.826 1,751.966 ↓ 1.0 204,272 1

Gather (cost=45,915.16..144,510.35 rows=204,224 width=55) (actual time=1,067.772..1,751.966 rows=204,272 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
27. 80.047 1,675.140 ↑ 1.2 68,091 3

Hash Left Join (cost=44,915.16..121,896.64 rows=85,093 width=55) (actual time=1,067.845..1,675.140 rows=68,091 loops=3)

  • Hash Cond: (mttr_bllbl_entty_1.bllbl_entty_id = b2.bllbl_entty_id)
28. 262.027 1,595.009 ↑ 1.2 68,091 3

Nested Loop (cost=44,911.27..119,976.51 rows=85,093 width=25) (actual time=1,067.726..1,595.009 rows=68,091 loops=3)

  • Join Filter: ((mttr_1.dstrt_id <> '35'::numeric) OR ((mttr_1.dstrt_id = '35'::numeric) AND ((mttr_bllbl_entty_1.tm_trckg_fl)::text = 'Y'::text)))
  • Rows Removed by Join Filter: 728
29. 178.676 1,332.979 ↑ 1.3 68,819 3

Hash Join (cost=44,910.84..59,491.86 rows=86,102 width=51) (actual time=1,067.700..1,332.979 rows=68,819 loops=3)

  • Hash Cond: ((x_4.mttr_id)::text = (x_3.mttr_id)::text)
30. 87.123 87.123 ↑ 1.3 265,284 3

Parallel Seq Scan on xcld_fp_mttr_id_tbl x_4 (cost=0.00..7,621.09 rows=331,709 width=12) (actual time=0.006..87.123 rows=265,284 loops=3)

31. 102.058 1,067.180 ↑ 1.0 206,456 3

Hash (cost=40,712.78..40,712.78 rows=206,645 width=39) (actual time=1,067.180..1,067.180 rows=206,456 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 2412kB
32. 260.479 965.122 ↑ 1.0 206,456 3

Hash Join (cost=26,104.27..40,712.78 rows=206,645 width=39) (actual time=608.361..965.122 rows=206,456 loops=3)

  • Hash Cond: ((mttr_bllbl_entty_1.mttr_id)::text = (x_3.mttr_id)::text)
33. 96.817 96.817 ↑ 1.0 206,472 3

Seq Scan on mttr_bllbl_entty mttr_bllbl_entty_1 (cost=0.00..5,285.61 rows=206,645 width=27) (actual time=0.010..96.817 rows=206,472 loops=3)

  • Filter: ((crrnt_fl)::text = 'Y'::text)
  • Rows Removed by Filter: 8227
34. 341.425 607.826 ↑ 1.0 795,852 3

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (actual time=607.826..607.826 rows=795,852 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3166kB
35. 266.401 266.401 ↑ 1.0 795,852 3

Seq Scan on xcld_fp_mttr_id_tbl x_3 (cost=0.00..12,265.01 rows=796,101 width=12) (actual time=0.006..266.401 rows=795,852 loops=3)

36. 0.003 0.003 ↑ 1.0 1 206,456

Index Scan using mttr_pkey on mttr mttr_1 (cost=0.42..0.68 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=206,456)

  • Index Cond: ((mttr_id)::text = (x_3.mttr_id)::text)
37. 0.042 0.084 ↑ 1.0 84 3

Hash (cost=2.84..2.84 rows=84 width=10) (actual time=0.084..0.084 rows=84 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
38. 0.042 0.042 ↑ 1.0 84 3

Seq Scan on bllbl_entty_lk b2 (cost=0.00..2.84 rows=84 width=10) (actual time=0.010..0.042 rows=84 loops=3)

39. 0.158 104.451 ↓ 69.8 279 1

Hash (cost=41,935.72..41,935.72 rows=4 width=94) (actual time=104.451..104.451 rows=279 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
40. 0.604 104.293 ↓ 69.8 279 1

Hash Join (cost=37,356.78..41,935.72 rows=4 width=94) (actual time=92.148..104.293 rows=279 loops=1)

  • Hash Cond: (mttr.dstrt_id = d.dstrt_id)
41. 1.080 103.565 ↓ 59.3 1,660 1

Hash Left Join (cost=37,346.42..41,925.00 rows=28 width=77) (actual time=91.800..103.565 rows=1,660 loops=1)

  • Hash Cond: ((mttr.mttr_id)::text = (zz5.mttr_id)::text)
42. 10.719 10.719 ↓ 59.3 1,660 1

Index Scan using mttr_st_id_idx on mttr (cost=0.42..4,578.86 rows=28 width=73) (actual time=0.028..10.719 rows=1,660 loops=1)

  • Index Cond: (mttr_st_id = '1'::numeric)
  • Filter: (((crtd_dt)::date >= to_date('2018-11-01'::text, 'yyyy-mm-dd'::text)) AND ((crtd_dt)::date <= to_date('2018-12-03'::text, 'yyyy-mm-dd'::text)))
  • Rows Removed by Filter: 10213
43. 1.459 91.766 ↓ 109.2 4,370 1

Hash (cost=37,345.49..37,345.49 rows=40 width=21) (actual time=91.766..91.766 rows=4,370 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 301kB
44. 2.667 90.307 ↓ 109.2 4,370 1

Subquery Scan on zz5 (cost=36,987.79..37,345.49 rows=40 width=21) (actual time=80.015..90.307 rows=4,370 loops=1)

  • Filter: (zz5.rn = 1)
  • Rows Removed by Filter: 3458
45. 6.006 87.640 ↑ 1.0 7,828 1

WindowAgg (cost=36,987.79..37,246.13 rows=7,949 width=55) (actual time=80.013..87.640 rows=7,828 loops=1)

46. 8.326 81.634 ↑ 1.0 7,828 1

Sort (cost=36,987.79..37,007.66 rows=7,949 width=25) (actual time=80.007..81.634 rows=7,828 loops=1)

  • Sort Key: mttr_bllbl_entty_2.mttr_id, (CASE WHEN (mttr_bllbl_entty_2.bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[])) THEN 1 ELSE 2 END), ((mttr_bllbl_entty_2.last_updt_dt)::date) DESC
  • Sort Method: quicksort Memory: 804kB
47. 5.478 73.308 ↑ 1.0 7,828 1

Gather (cost=1,005.16..36,472.83 rows=7,949 width=25) (actual time=3.583..73.308 rows=7,828 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
48. 14.495 67.830 ↑ 1.2 3,914 2

Nested Loop (cost=5.17..34,645.20 rows=4,676 width=25) (actual time=1.154..67.830 rows=3,914 loops=2)

49. 3.477 53.333 ↑ 1.2 3,914 2

Hash Left Join (cost=4.74..32,231.59 rows=4,676 width=54) (actual time=1.140..53.333 rows=3,914 loops=2)

  • Hash Cond: (mttr_bllbl_entty_2.bllbl_entty_id = b2_1.bllbl_entty_id)
50. 17.345 49.779 ↑ 1.2 3,914 2

Nested Loop (cost=0.85..32,169.16 rows=4,676 width=49) (actual time=1.031..49.779 rows=3,914 loops=2)

  • Join Filter: ((mttr_2.dstrt_id <> '35'::numeric) OR ((mttr_2.dstrt_id = '35'::numeric) AND ((mttr_bllbl_entty_2.tm_trckg_fl)::text = 'N'::text)))
  • Rows Removed by Join Filter: 200
51. 15.810 32.431 ↑ 1.2 4,114 2

Nested Loop (cost=0.42..28,845.04 rows=4,732 width=39) (actual time=0.289..32.431 rows=4,114 loops=2)

52. 16.618 16.618 ↑ 1.2 4,114 2

Parallel Seq Scan on mttr_bllbl_entty mttr_bllbl_entty_2 (cost=0.00..4,180.60 rows=4,732 width=27) (actual time=0.270..16.618 rows=4,114 loops=2)

  • Filter: ((crrnt_fl)::text = 'N'::text)
  • Rows Removed by Filter: 103236
53. 0.003 0.003 ↑ 1.0 1 8,227

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_5 (cost=0.42..5.21 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=8,227)

  • Index Cond: (mttr_id = (mttr_bllbl_entty_2.mttr_id)::text)
  • Heap Fetches: 3880
54. 0.003 0.003 ↑ 1.0 1 8,227

Index Scan using mttr_pkey on mttr mttr_2 (cost=0.42..0.68 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=8,227)

  • Index Cond: ((mttr_id)::text = (x_5.mttr_id)::text)
55. 0.041 0.077 ↑ 1.0 84 2

Hash (cost=2.84..2.84 rows=84 width=10) (actual time=0.077..0.077 rows=84 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
56. 0.036 0.036 ↑ 1.0 84 2

Seq Scan on bllbl_entty_lk b2_1 (cost=0.00..2.84 rows=84 width=10) (actual time=0.007..0.036 rows=84 loops=2)

57. 0.002 0.002 ↑ 1.0 1 7,828

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_6 (cost=0.42..0.51 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=7,828)

  • Index Cond: (mttr_id = (x_5.mttr_id)::text)
  • Heap Fetches: 3677
58. 0.012 0.124 ↑ 1.1 23 1

Hash (cost=10.05..10.05 rows=25 width=27) (actual time=0.124..0.124 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
59. 0.112 0.112 ↑ 1.1 23 1

Seq Scan on dstrt_lk d (cost=0.00..10.05 rows=25 width=27) (actual time=0.007..0.112 rows=23 loops=1)

  • Filter: ((dstrt_cd)::text = ANY ('{OL,OO,CI,DF,FQ,MI,OT,OH,TR,TA,TP,TC,TD,MB,FI,SI,SS,OC,SH,BT,RL,TC,TD,SV,AX}'::text[]))
  • Rows Removed by Filter: 148
60. 0.394 3.708 ↑ 1.0 1 618

Nested Loop (cost=0.84..10.74 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=618)

61. 2.472 2.472 ↑ 1.0 1 618

Index Scan using mttr_bllbl_entty_mttr_id_idx on mttr_bllbl_entty (cost=0.42..9.03 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=618)

  • Index Cond: ((mttr.mttr_id)::text = (mttr_id)::text)
  • Filter: (bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[]))
  • Rows Removed by Filter: 3
62. 0.842 0.842 ↑ 1.0 1 421

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_1 (cost=0.42..1.71 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=421)

  • Index Cond: (mttr_id = (mttr_bllbl_entty.mttr_id)::text)
  • Heap Fetches: 421
63. 0.591 0.591 ↑ 1.0 1 197

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x (cost=0.42..8.44 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=197)

  • Index Cond: (mttr_id = (mttr.mttr_id)::text)
  • Heap Fetches: 197
64. 0.000 0.000 ↓ 0.0 0

Subquery Scan on zz6 (cost=231,345.32..261,298.98 rows=4,608 width=34) (never executed)

  • Filter: (zz6.rn = 1)
65. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=231,345.32..249,778.34 rows=921,651 width=50) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Sort (cost=231,345.32..233,649.45 rows=921,651 width=42) (never executed)

  • Sort Key: mttr_cause.mttr_id, mttr_cause.last_updt_dt DESC
67. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26,135.42..83,331.00 rows=921,651 width=42) (never executed)

  • Hash Cond: (mttr_cause.cause_code_id = c2.cause_code_id)
68. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26,104.27..71,673.62 rows=921,651 width=25) (never executed)

  • Hash Cond: ((mttr_cause.mttr_id)::text = (x_7.mttr_id)::text)
69. 0.000 0.000 ↓ 0.0 0

Seq Scan on mttr_cause (cost=0.00..17,443.51 rows=921,651 width=25) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Seq Scan on xcld_fp_mttr_id_tbl x_7 (cost=0.00..12,265.01 rows=796,101 width=12) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Hash (cost=20.51..20.51 rows=851 width=27) (never executed)

73. 0.000 0.000 ↓ 0.0 0

Seq Scan on cause_code_lk c2 (cost=0.00..20.51 rows=851 width=27) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Subquery Scan on zz7 (cost=137,604.03..141,303.61 rows=569 width=16) (never executed)

  • Filter: (zz7.rn = 1)
75. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=137,604.03..139,880.69 rows=113,833 width=80) (never executed)

76. 0.000 0.000 ↓ 0.0 0

Sort (cost=137,604.03..137,888.62 rows=113,833 width=18) (never executed)

  • Sort Key: mttr_cntct.mttr_id, mttr_cntct.prmry_fl DESC NULLS LAST
77. 0.000 0.000 ↓ 0.0 0

Gather (cost=3,263.72..125,709.52 rows=113,833 width=18) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
78. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2,263.72..113,326.22 rows=47,430 width=18) (never executed)

79. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2,263.29..89,387.68 rows=47,430 width=18) (never executed)

  • Hash Cond: ((mttr_cntct.mp_id)::text = (c2_1.mkt_prtcp_id)::text)
80. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on mttr_cntct (cost=0.00..62,037.93 rows=339,478 width=18) (never executed)

  • Filter: (((actv_ind)::text = 'A'::text) AND ((row_type)::text = 'F'::text))
81. 0.000 0.000 ↓ 0.0 0

Hash (cost=2,189.47..2,189.47 rows=5,906 width=4) (never executed)

82. 0.000 0.000 ↓ 0.0 0

Seq Scan on mkt_prtcp_eqty_hs c2_1 (cost=0.00..2,189.47 rows=5,906 width=4) (never executed)

  • Filter: (((mkt_cntr_id)::text ~~ 'NASDAQ'::text) AND (xprtn_dt = '2399-12-31'::date))
83. 0.000 0.000 ↓ 0.0 0

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_8 (cost=0.42..0.50 rows=1 width=12) (never executed)

  • Index Cond: (mttr_id = (mttr_cntct.mttr_id)::text)
  • Heap Fetches: 0
84. 0.000 0.000 ↓ 0.0 0

Subquery Scan on zz8 (cost=119,071.25..120,625.93 rows=194 width=44) (never executed)

  • Filter: (zz8.rn = 1)
85. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=119,071.25..120,140.09 rows=38,867 width=58) (never executed)

86. 0.000 0.000 ↓ 0.0 0

Sort (cost=119,071.25..119,168.41 rows=38,867 width=299) (never executed)

  • Sort Key: mttr_cmmnt.mttr_id, mttr_cmmnt.mttr_cmmnt_id DESC
87. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,838.68..110,658.86 rows=38,867 width=299) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
88. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=838.68..105,772.16 rows=16,195 width=299) (never executed)

89. 0.000 0.000 ↓ 0.0 0

Parallel Bitmap Heap Scan on mttr_cmmnt (cost=838.25..77,485.33 rows=16,195 width=299) (never executed)

  • Recheck Cond: (mttr_cmmnt_type_id = '14'::numeric)
  • Filter: ((actv_ind)::text = 'A'::text)
90. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on mttr_cmmnt_idx2 (cost=0.00..828.54 rows=39,481 width=0) (never executed)

  • Index Cond: (mttr_cmmnt_type_id = '14'::numeric)
91. 0.000 0.000 ↓ 0.0 0

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_9 (cost=0.42..1.75 rows=1 width=12) (never executed)

  • Index Cond: (mttr_id = (mttr_cmmnt.mttr_id)::text)
  • Heap Fetches: 0
92. 0.000 0.000 ↓ 0.0 0

Subquery Scan on zz9 (cost=606,072.16..711,232.32 rows=13,145 width=44) (never executed)

  • Filter: (zz9.rn = 1)
93. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=606,072.16..678,369.77 rows=2,629,004 width=54) (never executed)

94. 0.000 0.000 ↓ 0.0 0

Sort (cost=606,072.16..612,644.67 rows=2,629,004 width=31) (never executed)

  • Sort Key: mttr_staff.mttr_id, mttr_staff.prmry_fl DESC NULLS LAST
95. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26,498.95..199,936.35 rows=2,629,004 width=31) (never executed)

  • Hash Cond: (mttr_staff.staff_id = s2.staff_id)
96. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26,104.27..166,352.97 rows=2,629,004 width=18) (never executed)

  • Hash Cond: ((mttr_staff.mttr_id)::text = (x_10.mttr_id)::text)
97. 0.000 0.000 ↓ 0.0 0

Seq Scan on mttr_staff (cost=0.00..72,359.55 rows=2,629,004 width=18) (never executed)

  • Filter: ((actv_ind)::text = 'A'::text)
98. 0.000 0.000 ↓ 0.0 0

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (never executed)

99. 0.000 0.000 ↓ 0.0 0

Seq Scan on xcld_fp_mttr_id_tbl x_10 (cost=0.00..12,265.01 rows=796,101 width=12) (never executed)

100. 0.000 0.000 ↓ 0.0 0

Hash (cost=277.08..277.08 rows=9,408 width=22) (never executed)

101. 0.000 0.000 ↓ 0.0 0

Seq Scan on staff s2 (cost=0.00..277.08 rows=9,408 width=22) (never executed)

102. 0.000 0.000 ↓ 0.0 0

Subquery Scan on zz10 (cost=313,086.36..352,633.99 rows=6,084 width=37) (never executed)

  • Filter: (zz10.rn = 1)
103. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=313,086.36..337,423.36 rows=1,216,850 width=51) (never executed)

104. 0.000 0.000 ↓ 0.0 0

Sort (cost=313,086.36..316,128.49 rows=1,216,850 width=43) (never executed)

  • Sort Key: mttr_prdct.mttr_id, mttr_prdct.mttr_prdct_id DESC
105. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=26,108.46..115,229.95 rows=1,216,850 width=43) (never executed)

  • Hash Cond: (mttr_prdct.prdct_id = p2.prdct_id)
106. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=26,104.27..99,969.30 rows=1,216,850 width=27) (never executed)

  • Hash Cond: ((mttr_prdct.mttr_id)::text = (x_11.mttr_id)::text)
107. 0.000 0.000 ↓ 0.0 0

Seq Scan on mttr_prdct (cost=0.00..37,976.31 rows=1,216,850 width=27) (never executed)

  • Filter: ((actv_ind)::text = 'A'::text)
108. 0.000 0.000 ↓ 0.0 0

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (never executed)

109. 0.000 0.000 ↓ 0.0 0

Seq Scan on xcld_fp_mttr_id_tbl x_11 (cost=0.00..12,265.01 rows=796,101 width=12) (never executed)

110. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.97..2.97 rows=97 width=26) (never executed)

111. 0.000 0.000 ↓ 0.0 0

Seq Scan on prdct_lk p2 (cost=0.00..2.97 rows=97 width=26) (never executed)

112. 0.000 0.000 ↓ 0.0 0

Unique (cost=535,892.70..536,491.39 rows=79,825 width=44) (never executed)

113. 0.000 0.000 ↓ 0.0 0

Sort (cost=535,892.70..536,092.27 rows=79,825 width=44) (never executed)

  • Sort Key: tm_entry.mttr_id, (sum(CASE WHEN (tm_entry.bllbl_entty_id = ANY ('{8,15}'::numeric[])) THEN tm_entry.actvy_hours_ct WHEN (tm_entry.bllbl_entty_id = '23'::numeric) THEN (tm_entry.actvy_hours_ct * 0.05) WHEN (tm_entry.bllbl_entty_id = '24'::numeric) THEN (tm_entry.actvy_hours_ct * 0.04) WHEN (tm_entry.bllbl_entty_id = '25'::numeric) THEN (tm_entry.actvy_hours_ct * 0.03) WHEN (tm_entry.bllbl_entty_id = '26'::numeric) THEN (tm_entry.actvy_hours_ct * 0.02) ELSE NULL::numeric END))
114. 0.000 0.000 ↓ 0.0 0

Finalize GroupAggregate (cost=497,065.32..526,936.13 rows=79,825 width=44) (never executed)

  • Group Key: tm_entry.mttr_id
115. 0.000 0.000 ↓ 0.0 0

Gather Merge (cost=497,065.32..524,740.95 rows=159,650 width=44) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
116. 0.000 0.000 ↓ 0.0 0

Partial GroupAggregate (cost=496,065.30..505,313.35 rows=79,825 width=44) (never executed)

  • Group Key: tm_entry.mttr_id
117. 0.000 0.000 ↓ 0.0 0

Sort (cost=496,065.30..496,752.82 rows=275,008 width=22) (never executed)

  • Sort Key: tm_entry.mttr_id
118. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=48,708.64..465,577.54 rows=275,008 width=22) (never executed)

  • Hash Cond: ((tm_entry.mttr_id)::text = (x_12.mttr_id)::text)
  • Filter: ((tm_entry.mttr_id IS NULL) OR (x_12.mttr_id IS NOT NULL))
119. 0.000 0.000 ↓ 0.0 0

Parallel Bitmap Heap Scan on tm_entry (cost=22,604.37..428,889.29 rows=275,008 width=22) (never executed)

  • Recheck Cond: (bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[]))
  • Filter: ((mttr_id IS NOT NULL) AND (actvy_hours_ct > '0'::numeric) AND ((actv_ind)::text = 'A'::text))
120. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on tm_entry_idx2 (cost=0.00..22,439.36 rows=1,072,632 width=0) (never executed)

  • Index Cond: (bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[]))
121. 0.000 0.000 ↓ 0.0 0

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (never executed)

122. 0.000 0.000 ↓ 0.0 0

Seq Scan on xcld_fp_mttr_id_tbl x_12 (cost=0.00..12,265.01 rows=796,101 width=12) (never executed)

123. 2.525 609,125.523 ↓ 0.0 0 1

Merge Join (cost=2,177,814.14..2,288,068.64 rows=1 width=301) (actual time=609,125.523..609,125.523 rows=0 loops=1)

  • Merge Cond: ((mttr_3.asctd_mttr_id)::text = (x_14.mttr_id)::text)
124. 2,620.901 608,958.521 ↓ 84.2 337 1

Nested Loop Left Join (cost=2,162,118.33..2,602,828.23 rows=4 width=265) (actual time=35,367.840..608,958.521 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (m8.mttr_id)::text)
  • Rows Removed by Join Filter: 10683076
125. 62,653.348 600,697.251 ↓ 84.2 337 1

Nested Loop Left Join (cost=1,626,225.62..2,057,741.53 rows=4 width=233) (actual time=31,957.221..600,697.251 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (zz19.mttr_id)::text)
  • Rows Removed by Join Filter: 218130025
126. 75,147.591 474,402.801 ↓ 84.2 337 1

Nested Loop Left Join (cost=1,313,139.26..1,704,727.29 rows=4 width=208) (actual time=25,529.063..474,402.801 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (zz18.mttr_id)::text)
  • Rows Removed by Join Filter: 267969965
127. 3,657.529 314,284.704 ↓ 84.2 337 1

Nested Loop Left Join (cost=707,067.10..992,673.40 rows=4 width=176) (actual time=12,184.902..314,284.704 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (zz_1.mttr_id)::text)
  • Rows Removed by Join Filter: 12895961
128. 29,851.485 306,510.720 ↓ 84.2 337 1

Nested Loop Left Join (cost=587,995.85..872,035.35 rows=4 width=144) (actual time=11,915.361..306,510.720 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (zz17.mttr_id)::text)
  • Rows Removed by Join Filter: 105651536
129. 61,478.625 248,164.874 ↓ 84.2 337 1

Nested Loop Left Join (cost=450,391.82..730,696.18 rows=4 width=140) (actual time=9,929.131..248,164.874 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (zz.mttr_id)::text)
  • Rows Removed by Join Filter: 221038975
130. 53,239.057 122,772.177 ↓ 84.2 337 1

Nested Loop Left Join (cost=219,046.49..469,109.21 rows=4 width=118) (actual time=5,264.490..122,772.177 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (zz16.mttr_id)::text)
  • Rows Removed by Join Filter: 190575186
131. 367.634 16,651.754 ↓ 84.2 337 1

Nested Loop Left Join (cost=83,903.96..315,043.32 rows=4 width=103) (actual time=1,815.745..16,651.754 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (zz15.mttr_id)::text)
  • Rows Removed by Join Filter: 1472664
132. 7,342.679 15,904.321 ↓ 84.2 337 1

Nested Loop Left Join (cost=46,916.17..277,695.32 rows=4 width=94) (actual time=1,725.952..15,904.321 rows=337 loops=1)

  • Join Filter: ((mttr_3.mttr_id)::text = (zz13.mttr_id)::text)
  • Rows Removed by Join Filter: 25737936
133. 0.000 27.662 ↓ 31.5 126 1

Gather Merge (cost=1,001.02..113,590.73 rows=4 width=85) (actual time=27.108..27.662 rows=126 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
134. 0.225 298.794 ↓ 46.5 93 3

Nested Loop (cost=0.99..112,590.25 rows=2 width=85) (actual time=21.311..99.598 rows=93 loops=3)

135. 1.597 297.732 ↓ 46.5 93 3

Nested Loop (cost=0.57..112,573.36 rows=2 width=85) (actual time=21.289..99.244 rows=93 loops=3)

136. 292.815 292.815 ↓ 46.1 553 3

Parallel Index Scan using mttr_idx13 on mttr mttr_3 (cost=0.42..112,562.41 rows=12 width=73) (actual time=19.319..97.605 rows=553 loops=3)

  • Filter: ((mttr_st_id = '1'::numeric) AND ((crtd_dt)::date >= to_date('2018-11-01'::text, 'yyyy-mm-dd'::text)) AND ((crtd_dt)::date <= to_date('2018-12-03'::text, 'yyyy-mm-dd'::text)))
  • Rows Removed by Filter: 264805
137. 3.320 3.320 ↓ 0.0 0 1,660

Index Scan using dstrt_lk_pkey on dstrt_lk d_1 (cost=0.14..0.91 rows=1 width=27) (actual time=0.002..0.002 rows=0 loops=1,660)

  • Index Cond: (dstrt_id = mttr_3.dstrt_id)
  • Filter: ((dstrt_cd)::text = ANY ('{OL,OO,CI,DF,FQ,MI,OT,OH,TR,TA,TP,TC,TD,MB,FI,SI,SS,OC,SH,BT,RL,TC,TD,SV,AX}'::text[]))
  • Rows Removed by Filter: 1
138. 0.837 0.837 ↑ 1.0 1 279

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_13 (cost=0.42..8.44 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=279)

  • Index Cond: (mttr_id = (mttr_3.mttr_id)::text)
  • Heap Fetches: 1
139. 6,802.399 8,533.980 ↓ 1.0 204,272 126

Materialize (cost=45,915.16..148,770.71 rows=204,224 width=21) (actual time=8.203..67.730 rows=204,272 loops=126)

140. 95.958 1,731.581 ↓ 1.0 204,272 1

Subquery Scan on zz13 (cost=45,915.16..146,552.59 rows=204,224 width=21) (actual time=1,033.274..1,731.581 rows=204,272 loops=1)

141. 25.645 1,635.623 ↓ 1.0 204,272 1

Gather (cost=45,915.16..144,510.35 rows=204,224 width=55) (actual time=1,033.272..1,635.623 rows=204,272 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
142. 78.247 1,609.978 ↑ 1.2 68,091 3

Hash Left Join (cost=44,915.16..121,896.64 rows=85,093 width=55) (actual time=1,029.542..1,609.978 rows=68,091 loops=3)

  • Hash Cond: (mttr_bllbl_entty_3.bllbl_entty_id = b2_2.bllbl_entty_id)
143. 258.851 1,531.650 ↑ 1.2 68,091 3

Nested Loop (cost=44,911.27..119,976.51 rows=85,093 width=25) (actual time=1,029.427..1,531.650 rows=68,091 loops=3)

  • Join Filter: ((mttr_4.dstrt_id <> '35'::numeric) OR ((mttr_4.dstrt_id = '35'::numeric) AND ((mttr_bllbl_entty_3.tm_trckg_fl)::text = 'Y'::text)))
  • Rows Removed by Join Filter: 728
144. 167.524 1,272.796 ↑ 1.3 68,819 3

Hash Join (cost=44,910.84..59,491.86 rows=86,102 width=51) (actual time=1,029.402..1,272.796 rows=68,819 loops=3)

  • Hash Cond: ((x_16.mttr_id)::text = (x_15.mttr_id)::text)
145. 76.374 76.374 ↑ 1.3 265,284 3

Parallel Seq Scan on xcld_fp_mttr_id_tbl x_16 (cost=0.00..7,621.09 rows=331,709 width=12) (actual time=0.006..76.374 rows=265,284 loops=3)

146. 99.735 1,028.898 ↑ 1.0 206,456 3

Hash (cost=40,712.78..40,712.78 rows=206,645 width=39) (actual time=1,028.898..1,028.898 rows=206,456 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 2412kB
147. 254.162 929.163 ↑ 1.0 206,456 3

Hash Join (cost=26,104.27..40,712.78 rows=206,645 width=39) (actual time=581.817..929.163 rows=206,456 loops=3)

  • Hash Cond: ((mttr_bllbl_entty_3.mttr_id)::text = (x_15.mttr_id)::text)
148. 93.760 93.760 ↑ 1.0 206,472 3

Seq Scan on mttr_bllbl_entty mttr_bllbl_entty_3 (cost=0.00..5,285.61 rows=206,645 width=27) (actual time=0.010..93.760 rows=206,472 loops=3)

  • Filter: ((crrnt_fl)::text = 'Y'::text)
  • Rows Removed by Filter: 8227
149. 328.578 581.241 ↑ 1.0 795,852 3

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (actual time=581.241..581.241 rows=795,852 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3166kB
150. 252.663 252.663 ↑ 1.0 795,852 3

Seq Scan on xcld_fp_mttr_id_tbl x_15 (cost=0.00..12,265.01 rows=796,101 width=12) (actual time=0.006..252.663 rows=795,852 loops=3)

151. 0.003 0.003 ↑ 1.0 1 206,456

Index Scan using mttr_pkey on mttr mttr_4 (cost=0.42..0.68 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=206,456)

  • Index Cond: ((mttr_id)::text = (x_15.mttr_id)::text)
152. 0.040 0.081 ↑ 1.0 84 3

Hash (cost=2.84..2.84 rows=84 width=10) (actual time=0.081..0.081 rows=84 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
153. 0.041 0.041 ↑ 1.0 84 3

Seq Scan on bllbl_entty_lk b2_2 (cost=0.00..2.84 rows=84 width=10) (actual time=0.010..0.041 rows=84 loops=3)

154. 293.064 379.799 ↓ 109.2 4,370 337

Materialize (cost=36,987.79..37,345.69 rows=40 width=21) (actual time=0.227..1.127 rows=4,370 loops=337)

155. 2.611 86.735 ↓ 109.2 4,370 1

Subquery Scan on zz15 (cost=36,987.79..37,345.49 rows=40 width=21) (actual time=76.410..86.735 rows=4,370 loops=1)

  • Filter: (zz15.rn = 1)
  • Rows Removed by Filter: 3458
156. 6.091 84.124 ↑ 1.0 7,828 1

WindowAgg (cost=36,987.79..37,246.13 rows=7,949 width=55) (actual time=76.408..84.124 rows=7,828 loops=1)

157. 7.920 78.033 ↑ 1.0 7,828 1

Sort (cost=36,987.79..37,007.66 rows=7,949 width=25) (actual time=76.402..78.033 rows=7,828 loops=1)

  • Sort Key: mttr_bllbl_entty_4.mttr_id, (CASE WHEN (mttr_bllbl_entty_4.bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[])) THEN 1 ELSE 2 END), ((mttr_bllbl_entty_4.last_updt_dt)::date) DESC
  • Sort Method: quicksort Memory: 804kB
158. 5.606 70.113 ↑ 1.0 7,828 1

Gather (cost=1,005.16..36,472.83 rows=7,949 width=25) (actual time=3.673..70.113 rows=7,828 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
159. 13.813 64.507 ↑ 1.2 3,914 2

Nested Loop (cost=5.17..34,645.20 rows=4,676 width=25) (actual time=1.060..64.507 rows=3,914 loops=2)

160. 3.063 50.692 ↑ 1.2 3,914 2

Hash Left Join (cost=4.74..32,231.59 rows=4,676 width=54) (actual time=1.047..50.692 rows=3,914 loops=2)

  • Hash Cond: (mttr_bllbl_entty_4.bllbl_entty_id = b2_3.bllbl_entty_id)
161. 16.103 47.556 ↑ 1.2 3,914 2

Nested Loop (cost=0.85..32,169.16 rows=4,676 width=49) (actual time=0.938..47.556 rows=3,914 loops=2)

  • Join Filter: ((mttr_5.dstrt_id <> '35'::numeric) OR ((mttr_5.dstrt_id = '35'::numeric) AND ((mttr_bllbl_entty_4.tm_trckg_fl)::text = 'N'::text)))
  • Rows Removed by Join Filter: 200
162. 15.183 31.450 ↑ 1.2 4,114 2

Nested Loop (cost=0.42..28,845.04 rows=4,732 width=39) (actual time=0.292..31.450 rows=4,114 loops=2)

163. 16.264 16.264 ↑ 1.2 4,114 2

Parallel Seq Scan on mttr_bllbl_entty mttr_bllbl_entty_4 (cost=0.00..4,180.60 rows=4,732 width=27) (actual time=0.272..16.264 rows=4,114 loops=2)

  • Filter: ((crrnt_fl)::text = 'N'::text)
  • Rows Removed by Filter: 103236
164. 0.003 0.003 ↑ 1.0 1 8,227

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_17 (cost=0.42..5.21 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=8,227)

  • Index Cond: (mttr_id = (mttr_bllbl_entty_4.mttr_id)::text)
  • Heap Fetches: 3968
165. 0.003 0.003 ↑ 1.0 1 8,227

Index Scan using mttr_pkey on mttr mttr_5 (cost=0.42..0.68 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=8,227)

  • Index Cond: ((mttr_id)::text = (x_17.mttr_id)::text)
166. 0.039 0.073 ↑ 1.0 84 2

Hash (cost=2.84..2.84 rows=84 width=10) (actual time=0.073..0.073 rows=84 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
167. 0.034 0.034 ↑ 1.0 84 2

Seq Scan on bllbl_entty_lk b2_3 (cost=0.00..2.84 rows=84 width=10) (actual time=0.007..0.034 rows=84 loops=2)

168. 0.002 0.002 ↑ 1.0 1 7,828

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_18 (cost=0.42..0.51 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=7,828)

  • Index Cond: (mttr_id = (x_17.mttr_id)::text)
  • Heap Fetches: 3793
169. 49,852.018 52,881.366 ↓ 196.1 565,506 337

Materialize (cost=135,142.53..153,900.06 rows=2,884 width=27) (actual time=6.056..156.918 rows=565,506 loops=337)

170. 239.763 3,029.348 ↓ 196.1 565,506 1

Subquery Scan on zz16 (cost=135,142.53..153,885.64 rows=2,884 width=27) (actual time=2,039.652..3,029.348 rows=565,506 loops=1)

  • Filter: (zz16.rn = 1)
  • Rows Removed by Filter: 11143
171. 494.866 2,789.585 ↑ 1.0 576,649 1

WindowAgg (cost=135,142.53..146,676.75 rows=576,711 width=43) (actual time=2,039.650..2,789.585 rows=576,649 loops=1)

172. 754.462 2,294.719 ↑ 1.0 576,649 1

Sort (cost=135,142.53..136,584.31 rows=576,711 width=35) (actual time=2,039.644..2,294.719 rows=576,649 loops=1)

  • Sort Key: mttr_orgn_2.mttr_id, mttr_orgn_2.last_updt_dt DESC
  • Sort Method: external merge Disk: 24928kB
173. 384.491 1,540.257 ↑ 1.0 576,649 1

Hash Join (cost=26,139.68..64,187.53 rows=576,711 width=35) (actual time=443.836..1,540.257 rows=576,649 loops=1)

  • Hash Cond: (mttr_orgn_2.orgn_id = o2_2.orgn_id)
174. 534.908 1,154.856 ↑ 1.0 576,649 1

Hash Join (cost=26,104.27..56,222.35 rows=576,711 width=25) (actual time=442.917..1,154.856 rows=576,649 loops=1)

  • Hash Cond: ((mttr_orgn_2.mttr_id)::text = (x_19.mttr_id)::text)
175. 177.408 177.408 ↑ 1.0 576,711 1

Seq Scan on mttr_orgn mttr_orgn_2 (cost=0.00..11,063.11 rows=576,711 width=25) (actual time=0.006..177.408 rows=576,711 loops=1)

176. 256.335 442.540 ↑ 1.0 795,852 1

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (actual time=442.540..442.540 rows=795,852 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3156kB
177. 186.205 186.205 ↑ 1.0 795,852 1

Seq Scan on xcld_fp_mttr_id_tbl x_19 (cost=0.00..12,265.01 rows=796,101 width=12) (actual time=0.006..186.205 rows=795,852 loops=1)

178. 0.234 0.910 ↑ 1.0 674 1

Hash (cost=26.98..26.98 rows=674 width=20) (actual time=0.910..0.910 rows=674 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
179. 0.438 0.676 ↑ 1.0 674 1

Hash Join (cost=2.80..26.98 rows=674 width=20) (actual time=0.078..0.676 rows=674 loops=1)

  • Hash Cond: (o2_2.orgn_ctgry_id = o3_2.orgn_ctgry_id)
180. 0.172 0.172 ↑ 1.0 674 1

Seq Scan on orgn_lk o2_2 (cost=0.00..15.74 rows=674 width=10) (actual time=0.007..0.172 rows=674 loops=1)

181. 0.035 0.066 ↑ 1.0 80 1

Hash (cost=1.80..1.80 rows=80 width=20) (actual time=0.066..0.066 rows=80 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
182. 0.031 0.031 ↑ 1.0 80 1

Seq Scan on orgn_ctgry_lk o3_2 (cost=0.00..1.80 rows=80 width=20) (actual time=0.009..0.031 rows=80 loops=1)

183. 59,454.762 63,914.072 ↓ 142.3 655,903 337

Materialize (cost=231,345.32..261,322.02 rows=4,608 width=34) (actual time=8.983..189.656 rows=655,903 loops=337)

184. 336.167 4,459.310 ↓ 142.3 655,903 1

Subquery Scan on zz (cost=231,345.32..261,298.98 rows=4,608 width=34) (actual time=3,026.023..4,459.310 rows=655,903 loops=1)

  • Filter: (zz.rn = 1)
  • Rows Removed by Filter: 265667
185. 741.819 4,123.143 ↑ 1.0 921,570 1

WindowAgg (cost=231,345.32..249,778.34 rows=921,651 width=50) (actual time=3,026.021..4,123.143 rows=921,570 loops=1)

186. 1,271.276 3,381.324 ↑ 1.0 921,570 1

Sort (cost=231,345.32..233,649.45 rows=921,651 width=42) (actual time=3,026.015..3,381.324 rows=921,570 loops=1)

  • Sort Key: mttr_cause_1.mttr_id, mttr_cause_1.last_updt_dt DESC
  • Sort Method: external merge Disk: 49096kB
187. 592.141 2,110.048 ↑ 1.0 921,570 1

Hash Join (cost=26,135.42..83,331.00 rows=921,651 width=42) (actual time=437.479..2,110.048 rows=921,570 loops=1)

  • Hash Cond: (mttr_cause_1.cause_code_id = c2_2.cause_code_id)
188. 798.094 1,517.309 ↑ 1.0 921,570 1

Hash Join (cost=26,104.27..71,673.62 rows=921,651 width=25) (actual time=436.871..1,517.309 rows=921,570 loops=1)

  • Hash Cond: ((mttr_cause_1.mttr_id)::text = (x_20.mttr_id)::text)
189. 282.531 282.531 ↑ 1.0 921,651 1

Seq Scan on mttr_cause mttr_cause_1 (cost=0.00..17,443.51 rows=921,651 width=25) (actual time=0.006..282.531 rows=921,651 loops=1)

190. 251.648 436.684 ↑ 1.0 795,852 1

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (actual time=436.684..436.684 rows=795,852 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3156kB
191. 185.036 185.036 ↑ 1.0 795,852 1

Seq Scan on xcld_fp_mttr_id_tbl x_20 (cost=0.00..12,265.01 rows=796,101 width=12) (actual time=0.006..185.036 rows=795,852 loops=1)

192. 0.326 0.598 ↑ 1.0 851 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 58kB
193. 0.272 0.272 ↑ 1.0 851 1

Seq Scan on cause_code_lk c2_2 (cost=0.00..20.51 rows=851 width=27) (actual time=0.011..0.272 rows=851 loops=1)

194. 26,746.083 28,494.361 ↓ 551.0 313,507 337

Materialize (cost=137,604.03..141,306.45 rows=569 width=16) (actual time=3.575..84.553 rows=313,507 loops=337)

195. 142.214 1,748.278 ↓ 551.0 313,507 1

Subquery Scan on zz17 (cost=137,604.03..141,303.61 rows=569 width=16) (actual time=1,203.507..1,748.278 rows=313,507 loops=1)

  • Filter: (zz17.rn = 1)
  • Rows Removed by Filter: 51014
196. 301.270 1,606.064 ↓ 3.2 364,521 1

WindowAgg (cost=137,604.03..139,880.69 rows=113,833 width=80) (actual time=1,203.505..1,606.064 rows=364,521 loops=1)

197. 710.420 1,304.794 ↓ 3.2 364,521 1

Sort (cost=137,604.03..137,888.62 rows=113,833 width=18) (actual time=1,203.499..1,304.794 rows=364,521 loops=1)

  • Sort Key: mttr_cntct_1.mttr_id, mttr_cntct_1.prmry_fl DESC NULLS LAST
  • Sort Method: external merge Disk: 10352kB
198. 0.000 594.374 ↓ 3.2 364,521 1

Gather (cost=3,263.72..125,709.52 rows=113,833 width=18) (actual time=14.576..594.374 rows=364,521 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
199. 428.355 848.130 ↓ 2.6 121,507 3

Nested Loop (cost=2,263.72..113,326.22 rows=47,430 width=18) (actual time=13.213..848.130 rows=121,507 loops=3)

200. 132.770 419.773 ↓ 2.6 121,507 3

Hash Join (cost=2,263.29..89,387.68 rows=47,430 width=18) (actual time=13.189..419.773 rows=121,507 loops=3)

  • Hash Cond: ((mttr_cntct_1.mp_id)::text = (c2_3.mkt_prtcp_id)::text)
201. 273.922 273.922 ↑ 1.3 264,781 3

Parallel Seq Scan on mttr_cntct mttr_cntct_1 (cost=0.00..62,037.93 rows=339,478 width=18) (actual time=0.007..273.922 rows=264,781 loops=3)

  • Filter: (((actv_ind)::text = 'A'::text) AND ((row_type)::text = 'F'::text))
  • Rows Removed by Filter: 430215
202. 2.493 13.081 ↓ 1.1 6,596 3

Hash (cost=2,189.47..2,189.47 rows=5,906 width=4) (actual time=13.081..13.081 rows=6,596 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 303kB
203. 10.588 10.588 ↓ 1.1 6,596 3

Seq Scan on mkt_prtcp_eqty_hs c2_3 (cost=0.00..2,189.47 rows=5,906 width=4) (actual time=0.813..10.588 rows=6,596 loops=3)

  • Filter: (((mkt_cntr_id)::text ~~ 'NASDAQ'::text) AND (xprtn_dt = '2399-12-31'::date))
  • Rows Removed by Filter: 55102
204. 0.002 0.002 ↑ 1.0 1 364,522

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_21 (cost=0.42..0.50 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=364,522)

  • Index Cond: (mttr_id = (mttr_cntct_1.mttr_id)::text)
  • Heap Fetches: 40775
205. 3,884.434 4,116.455 ↓ 197.3 38,267 337

Materialize (cost=119,071.25..120,626.90 rows=194 width=44) (actual time=0.414..12.215 rows=38,267 loops=337)

206. 16.943 232.021 ↓ 197.3 38,267 1

Subquery Scan on zz_1 (cost=119,071.25..120,625.93 rows=194 width=44) (actual time=138.188..232.021 rows=38,267 loops=1)

  • Filter: (zz_1.rn = 1)
  • Rows Removed by Filter: 2513
207. 63.058 215.078 ↓ 1.0 40,780 1

WindowAgg (cost=119,071.25..120,140.09 rows=38,867 width=58) (actual time=138.185..215.078 rows=40,780 loops=1)

208. 83.377 152.020 ↓ 1.0 40,780 1

Sort (cost=119,071.25..119,168.41 rows=38,867 width=299) (actual time=138.171..152.020 rows=40,780 loops=1)

  • Sort Key: mttr_cmmnt_1.mttr_id, mttr_cmmnt_1.mttr_cmmnt_id DESC
  • Sort Method: external merge Disk: 6456kB
209. 0.000 68.643 ↓ 1.0 40,780 1

Gather (cost=1,838.68..110,658.86 rows=38,867 width=299) (actual time=17.817..68.643 rows=40,780 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
210. 53.893 85.563 ↑ 1.2 13,593 3

Nested Loop (cost=838.68..105,772.16 rows=16,195 width=299) (actual time=13.401..85.563 rows=13,593 loops=3)

211. 21.549 31.667 ↑ 1.2 13,598 3

Parallel Bitmap Heap Scan on mttr_cmmnt mttr_cmmnt_1 (cost=838.25..77,485.33 rows=16,195 width=299) (actual time=13.377..31.667 rows=13,598 loops=3)

  • Recheck Cond: (mttr_cmmnt_type_id = '14'::numeric)
  • Filter: ((actv_ind)::text = 'A'::text)
  • Rows Removed by Filter: 80
  • Heap Blocks: exact=3179
212. 10.118 10.118 ↓ 1.0 41,034 1

Bitmap Index Scan on mttr_cmmnt_idx2 (cost=0.00..828.54 rows=39,481 width=0) (actual time=10.118..10.118 rows=41,034 loops=1)

  • Index Cond: (mttr_cmmnt_type_id = '14'::numeric)
213. 0.003 0.003 ↑ 1.0 1 40,793

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_22 (cost=0.42..1.75 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=40,793)

  • Index Cond: (mttr_id = (mttr_cmmnt_1.mttr_id)::text)
  • Heap Fetches: 4534
214. 71,219.280 84,970.506 ↓ 60.5 795,164 337

Materialize (cost=606,072.16..711,298.05 rows=13,145 width=44) (actual time=24.610..252.138 rows=795,164 loops=337)

215. 764.916 13,751.226 ↓ 60.5 795,732 1

Subquery Scan on zz18 (cost=606,072.16..711,232.32 rows=13,145 width=44) (actual time=8,292.216..13,751.226 rows=795,732 loops=1)

  • Filter: (zz18.rn = 1)
  • Rows Removed by Filter: 1829623
216. 3,752.400 12,986.310 ↑ 1.0 2,625,355 1

WindowAgg (cost=606,072.16..678,369.77 rows=2,629,004 width=54) (actual time=8,292.214..12,986.310 rows=2,625,355 loops=1)

217. 3,866.966 9,233.910 ↑ 1.0 2,625,355 1

Sort (cost=606,072.16..612,644.67 rows=2,629,004 width=31) (actual time=8,292.195..9,233.910 rows=2,625,355 loops=1)

  • Sort Key: mttr_staff_1.mttr_id, mttr_staff_1.prmry_fl DESC NULLS LAST
  • Sort Method: external merge Disk: 99784kB
218. 1,701.919 5,366.944 ↑ 1.0 2,625,355 1

Hash Join (cost=26,498.95..199,936.35 rows=2,629,004 width=31) (actual time=449.570..5,366.944 rows=2,625,355 loops=1)

  • Hash Cond: (mttr_staff_1.staff_id = s2_1.staff_id)
219. 2,149.425 3,658.021 ↑ 1.0 2,625,355 1

Hash Join (cost=26,104.27..166,352.97 rows=2,629,004 width=18) (actual time=442.532..3,658.021 rows=2,625,355 loops=1)

  • Hash Cond: ((mttr_staff_1.mttr_id)::text = (x_23.mttr_id)::text)
220. 1,066.276 1,066.276 ↑ 1.0 2,626,614 1

Seq Scan on mttr_staff mttr_staff_1 (cost=0.00..72,359.55 rows=2,629,004 width=18) (actual time=0.006..1,066.276 rows=2,626,614 loops=1)

  • Filter: ((actv_ind)::text = 'A'::text)
221. 255.894 442.320 ↑ 1.0 795,852 1

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (actual time=442.320..442.320 rows=795,852 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3156kB
222. 186.426 186.426 ↑ 1.0 795,852 1

Seq Scan on xcld_fp_mttr_id_tbl x_23 (cost=0.00..12,265.01 rows=796,101 width=12) (actual time=0.006..186.426 rows=795,852 loops=1)

223. 3.697 7.004 ↑ 1.0 9,408 1

Hash (cost=277.08..277.08 rows=9,408 width=22) (actual time=7.004..7.004 rows=9,408 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 635kB
224. 3.307 3.307 ↑ 1.0 9,408 1

Seq Scan on staff s2_1 (cost=0.00..277.08 rows=9,408 width=22) (actual time=0.009..3.307 rows=9,408 loops=1)

225. 57,492.188 63,641.102 ↓ 106.4 647,271 337

Materialize (cost=313,086.36..352,664.41 rows=6,084 width=37) (actual time=12.934..188.846 rows=647,271 loops=337)

226. 405.501 6,148.914 ↓ 106.4 647,543 1

Subquery Scan on zz19 (cost=313,086.36..352,633.99 rows=6,084 width=37) (actual time=4,357.431..6,148.914 rows=647,543 loops=1)

  • Filter: (zz19.rn = 1)
  • Rows Removed by Filter: 565254
227. 931.128 5,743.413 ↑ 1.0 1,212,797 1

WindowAgg (cost=313,086.36..337,423.36 rows=1,216,850 width=51) (actual time=4,357.429..5,743.413 rows=1,212,797 loops=1)

228. 2,019.178 4,812.285 ↑ 1.0 1,212,797 1

Sort (cost=313,086.36..316,128.49 rows=1,216,850 width=43) (actual time=4,357.422..4,812.285 rows=1,212,797 loops=1)

  • Sort Key: mttr_prdct_1.mttr_id, mttr_prdct_1.mttr_prdct_id DESC
  • Sort Method: external merge Disk: 55456kB
229. 767.826 2,793.107 ↑ 1.0 1,212,797 1

Hash Left Join (cost=26,108.46..115,229.95 rows=1,216,850 width=43) (actual time=448.832..2,793.107 rows=1,212,797 loops=1)

  • Hash Cond: (mttr_prdct_1.prdct_id = p2_1.prdct_id)
230. 1,060.693 2,025.204 ↑ 1.0 1,212,797 1

Hash Join (cost=26,104.27..99,969.30 rows=1,216,850 width=27) (actual time=448.743..2,025.204 rows=1,212,797 loops=1)

  • Hash Cond: ((mttr_prdct_1.mttr_id)::text = (x_24.mttr_id)::text)
231. 515.978 515.978 ↑ 1.0 1,212,935 1

Seq Scan on mttr_prdct mttr_prdct_1 (cost=0.00..37,976.31 rows=1,216,850 width=27) (actual time=0.013..515.978 rows=1,212,935 loops=1)

  • Filter: ((actv_ind)::text = 'A'::text)
  • Rows Removed by Filter: 107703
232. 262.552 448.533 ↑ 1.0 795,852 1

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (actual time=448.533..448.533 rows=795,852 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3166kB
233. 185.981 185.981 ↑ 1.0 795,852 1

Seq Scan on xcld_fp_mttr_id_tbl x_24 (cost=0.00..12,265.01 rows=796,101 width=12) (actual time=0.007..185.981 rows=795,852 loops=1)

234. 0.042 0.077 ↑ 1.0 97 1

Hash (cost=2.97..2.97 rows=97 width=26) (actual time=0.077..0.077 rows=97 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
235. 0.035 0.035 ↑ 1.0 97 1

Seq Scan on prdct_lk p2_1 (cost=0.00..2.97 rows=97 width=26) (actual time=0.008..0.035 rows=97 loops=1)

236. 2,251.868 5,640.369 ↑ 2.5 31,701 337

Materialize (cost=535,892.70..538,390.77 rows=79,825 width=44) (actual time=9.949..16.737 rows=31,701 loops=337)

237. 12.888 3,388.501 ↑ 2.5 31,728 1

Subquery Scan on m8 (cost=535,892.70..537,289.64 rows=79,825 width=44) (actual time=3,352.559..3,388.501 rows=31,728 loops=1)

238. 16.746 3,375.613 ↑ 2.5 31,728 1

Unique (cost=535,892.70..536,491.39 rows=79,825 width=44) (actual time=3,352.557..3,375.613 rows=31,728 loops=1)

239. 20.444 3,358.867 ↑ 2.5 31,728 1

Sort (cost=535,892.70..536,092.27 rows=79,825 width=44) (actual time=3,352.556..3,358.867 rows=31,728 loops=1)

  • Sort Key: tm_entry_1.mttr_id, (sum(CASE WHEN (tm_entry_1.bllbl_entty_id = ANY ('{8,15}'::numeric[])) THEN tm_entry_1.actvy_hours_ct WHEN (tm_entry_1.bllbl_entty_id = '23'::numeric) THEN (tm_entry_1.actvy_hours_ct * 0.05) WHEN (tm_entry_1.bllbl_entty_id = '24'::numeric) THEN (tm_entry_1.actvy_hours_ct * 0.04) WHEN (tm_entry_1.bllbl_entty_id = '25'::numeric) THEN (tm_entry_1.actvy_hours_ct * 0.03) WHEN (tm_entry_1.bllbl_entty_id = '26'::numeric) THEN (tm_entry_1.actvy_hours_ct * 0.02) ELSE NULL::numeric END))
  • Sort Method: quicksort Memory: 3247kB
240. 83.896 3,338.423 ↑ 2.5 31,728 1

Finalize GroupAggregate (cost=497,065.32..526,936.13 rows=79,825 width=44) (actual time=2,909.908..3,338.423 rows=31,728 loops=1)

  • Group Key: tm_entry_1.mttr_id
241. 0.000 3,254.527 ↑ 1.9 86,273 1

Gather Merge (cost=497,065.32..524,740.95 rows=159,650 width=44) (actual time=2,909.883..3,254.527 rows=86,273 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
242. 529.458 9,562.305 ↑ 2.8 28,758 3

Partial GroupAggregate (cost=496,065.30..505,313.35 rows=79,825 width=44) (actual time=2,899.112..3,187.435 rows=28,758 loops=3)

  • Group Key: tm_entry_1.mttr_id
243. 1,018.563 9,032.847 ↓ 1.1 291,685 3

Sort (cost=496,065.30..496,752.82 rows=275,008 width=22) (actual time=2,899.096..3,010.949 rows=291,685 loops=3)

  • Sort Key: tm_entry_1.mttr_id
  • Sort Method: external merge Disk: 8976kB
244. 1,012.320 8,014.284 ↓ 1.1 291,685 3

Hash Left Join (cost=48,708.64..465,577.54 rows=275,008 width=22) (actual time=744.188..2,671.428 rows=291,685 loops=3)

  • Hash Cond: ((tm_entry_1.mttr_id)::text = (x_25.mttr_id)::text)
  • Filter: ((tm_entry_1.mttr_id IS NULL) OR (x_25.mttr_id IS NOT NULL))
245. 5,037.804 5,154.906 ↓ 1.1 291,685 3

Parallel Bitmap Heap Scan on tm_entry tm_entry_1 (cost=22,604.37..428,889.29 rows=275,008 width=22) (actual time=127.669..1,718.302 rows=291,685 loops=3)

  • Recheck Cond: (bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[]))
  • Rows Removed by Index Recheck: 3954501
  • Filter: ((mttr_id IS NOT NULL) AND (actvy_hours_ct > '0'::numeric) AND ((actv_ind)::text = 'A'::text))
  • Rows Removed by Filter: 33748
  • Heap Blocks: exact=18657 lossy=85313
246. 117.102 117.102 ↑ 1.1 976,299 1

Bitmap Index Scan on tm_entry_idx2 (cost=0.00..22,439.36 rows=1,072,632 width=0) (actual time=117.102..117.102 rows=976,299 loops=1)

  • Index Cond: (bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[]))
247. 1,043.781 1,847.058 ↑ 1.0 795,852 3

Hash (cost=12,265.01..12,265.01 rows=796,101 width=12) (actual time=615.686..615.686 rows=795,852 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3166kB
248. 803.277 803.277 ↑ 1.0 795,852 3

Seq Scan on xcld_fp_mttr_id_tbl x_25 (cost=0.00..12,265.01 rows=796,101 width=12) (actual time=0.012..267.759 rows=795,852 loops=3)

249. 7.808 164.477 ↑ 2.0 7,808 1

Sort (cost=15,695.81..15,734.32 rows=15,402 width=24) (actual time=162.860..164.477 rows=7,808 loops=1)

  • Sort Key: mttr_orgn_1.mttr_id
  • Sort Method: quicksort Memory: 806kB
250. 4.738 156.669 ↑ 2.0 7,852 1

Gather (cost=1,016.47..14,624.54 rows=15,402 width=24) (actual time=1.791..156.669 rows=7,852 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
251. 11.138 151.931 ↑ 2.5 2,617 3

Nested Loop (cost=16.47..12,084.34 rows=6,418 width=24) (actual time=0.195..151.931 rows=2,617 loops=3)

252. 75.211 140.790 ↑ 2.5 2,617 3

Hash Join (cost=16.04..8,680.29 rows=6,418 width=12) (actual time=0.175..140.790 rows=2,617 loops=3)

  • Hash Cond: (mttr_orgn_1.orgn_id = o2_1.orgn_id)
253. 65.506 65.506 ↑ 1.2 192,237 3

Parallel Seq Scan on mttr_orgn mttr_orgn_1 (cost=0.00..7,698.96 rows=240,296 width=17) (actual time=0.007..65.506 rows=192,237 loops=3)

254. 0.016 0.073 ↑ 1.0 18 3

Hash (cost=15.82..15.82 rows=18 width=5) (actual time=0.073..0.073 rows=18 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
255. 0.014 0.057 ↑ 1.0 18 3

Nested Loop (cost=4.41..15.82 rows=18 width=5) (actual time=0.031..0.057 rows=18 loops=3)

256. 0.018 0.018 ↑ 1.0 1 3

Seq Scan on orgn_ctgry_lk o3_1 (cost=0.00..2.00 rows=1 width=5) (actual time=0.011..0.018 rows=1 loops=3)

  • Filter: (orgn_ctgry_id = '18'::numeric)
  • Rows Removed by Filter: 79
257. 0.013 0.025 ↑ 1.0 18 3

Bitmap Heap Scan on orgn_lk o2_1 (cost=4.41..13.64 rows=18 width=10) (actual time=0.016..0.025 rows=18 loops=3)

  • Recheck Cond: (orgn_ctgry_id = '18'::numeric)
  • Heap Blocks: exact=4
258. 0.012 0.012 ↑ 1.0 18 3

Bitmap Index Scan on tmporgn_lk_idx1 (cost=0.00..4.41 rows=18 width=0) (actual time=0.012..0.012 rows=18 loops=3)

  • Index Cond: (orgn_ctgry_id = '18'::numeric)
259. 0.003 0.003 ↑ 1.0 1 7,852

Index Only Scan using xcld_fp_mttr_id_tbl_pkey on xcld_fp_mttr_id_tbl x_14 (cost=0.42..0.53 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=7,852)

  • Index Cond: (mttr_id = (mttr_orgn_1.mttr_id)::text)
  • Heap Fetches: 2535