explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2soW : enable_nestloop_on_no_union_no_vpd

Settings
# exclusive inclusive rows x rows loops node
1. 2.475 600,506.018 ↓ 0.0 0 1

Merge Join (cost=1,823,849.18..1,876,126.66 rows=1 width=301) (actual time=600,506.018..600,506.018 rows=0 loops=1)

  • Merge Cond: ((m.asctd_mttr_id)::text = (o1.mttr_id)::text)
2. 2,617.993 600,351.376 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,811,555.42..2,281,359.47 rows=9 width=265) (actual time=27,165.094..600,351.376 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (m8.mttr_id)::text)
  • Rows Removed by Join Filter: 10683064
3. 62,580.638 592,700.962 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,253,747.04..1,666,636.20 rows=9 width=233) (actual time=24,363.524..592,700.962 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (zz19.mttr_id)::text)
  • Rows Removed by Join Filter: 218190683
4. 75,113.016 467,258.703 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,003,357.43..1,375,989.17 rows=9 width=208) (actual time=19,569.766..467,258.703 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (zz18.mttr_id)::text)
  • Rows Removed by Join Filter: 268076426
5. 3,702.185 309,965.204 ↓ 37.4 337 1

Nested Loop Left Join (cost=491,640.34..757,385.78 rows=9 width=176) (actual time=8,965.032..309,965.204 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (zz_1.mttr_id)::text)
  • Rows Removed by Join Filter: 12900342
6. 29,328.122 302,166.447 ↓ 37.4 337 1

Nested Loop Left Join (cost=403,459.45..667,561.72 rows=9 width=144) (actual time=8,703.268..302,166.447 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (zz17.mttr_id)::text)
  • Rows Removed by Join Filter: 105682265
7. 61,829.029 244,318.015 ↓ 37.4 337 1

Nested Loop Left Join (cost=288,797.73..548,959.04 rows=9 width=140) (actual time=6,981.346..244,318.015 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (zz.mttr_id)::text)
  • Rows Removed by Join Filter: 220939314
8. 52,786.175 120,812.594 ↓ 37.4 337 1

Nested Loop Left Join (cost=111,682.52..341,256.57 rows=9 width=118) (actual time=3,661.259..120,812.594 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (zz16.mttr_id)::text)
  • Rows Removed by Join Filter: 190586089
9. 365.717 15,768.503 ↓ 37.4 337 1

Nested Loop Left Join (cost=21,696.39..232,130.79 rows=9 width=103) (actual time=1,136.964..15,768.503 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (zz15.mttr_id)::text)
  • Rows Removed by Join Filter: 1472664
10. 7,301.584 15,057.698 ↓ 37.4 337 1

Nested Loop Left Join (cost=1,005.48..211,068.57 rows=9 width=94) (actual time=1,079.661..15,057.698 rows=337 loops=1)

  • Join Filter: ((m.mttr_id)::text = (zz13.mttr_id)::text)
  • Rows Removed by Join Filter: 25739769
11. 0.000 24.754 ↓ 14.0 126 1

Gather Merge (cost=1,000.59..113,178.01 rows=9 width=85) (actual time=24.184..24.754 rows=126 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 1.346 294.306 ↓ 23.2 93 3

Nested Loop (cost=0.57..112,176.95 rows=4 width=85) (actual time=20.249..98.102 rows=93 loops=3)

13. 289.626 289.626 ↓ 22.2 556 3

Parallel Index Scan using mttr_idx13 on mttr m (cost=0.42..112,163.74 rows=25 width=73) (actual time=19.401..96.542 rows=556 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: 264834
14. 3.334 3.334 ↓ 0.0 0 1,667

Index Scan using dstrt_lk_pkey on dstrt_lk d (cost=0.14..0.53 rows=1 width=27) (actual time=0.002..0.002 rows=0 loops=1,667)

  • Index Cond: (dstrt_id = m.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
15. 6,771.716 7,731.360 ↓ 1.0 204,287 126

Materialize (cost=4.88..61,296.80 rows=203,968 width=21) (actual time=0.004..61.360 rows=204,287 loops=126)

16. 85.312 959.644 ↓ 1.0 204,288 1

Subquery Scan on zz13 (cost=4.88..59,080.96 rows=203,968 width=21) (actual time=0.141..959.644 rows=204,288 loops=1)

17. 198.874 874.332 ↓ 1.0 204,288 1

Hash Left Join (cost=4.88..57,041.28 rows=203,968 width=55) (actual time=0.140..874.332 rows=204,288 loops=1)

  • Hash Cond: (b1.bllbl_entty_id = b2.bllbl_entty_id)
18. 317.099 675.383 ↓ 1.0 204,288 1

Merge Join (cost=0.99..52,444.16 rows=203,968 width=25) (actual time=0.048..675.383 rows=204,288 loops=1)

  • Merge Cond: ((b1.mttr_id)::text = (m_1.mttr_id)::text)
  • Join Filter: ((m_1.dstrt_id <> '35'::numeric) OR ((m_1.dstrt_id = '35'::numeric) AND ((b1.tm_trckg_fl)::text = 'Y'::text)))
  • Rows Removed by Join Filter: 2184
19. 129.838 129.838 ↑ 1.0 206,472 1

Index Scan using mttr_bllbl_entty_mttr_id_idx on mttr_bllbl_entty b1 (cost=0.42..11,248.89 rows=206,472 width=27) (actual time=0.011..129.838 rows=206,472 loops=1)

  • Filter: ((crrnt_fl)::text = 'Y'::text)
  • Rows Removed by Filter: 8227
20. 228.446 228.446 ↑ 1.1 746,679 1

Index Only Scan using mttr_idx15 on mttr m_1 (cost=0.42..37,630.96 rows=796,169 width=17) (actual time=0.027..228.446 rows=746,679 loops=1)

  • Heap Fetches: 96
21. 0.044 0.075 ↑ 1.0 84 1

Hash (cost=2.84..2.84 rows=84 width=10) (actual time=0.075..0.075 rows=84 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
22. 0.031 0.031 ↑ 1.0 84 1

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

23. 290.881 345.088 ↓ 106.6 4,370 337

Materialize (cost=20,690.91..21,056.79 rows=41 width=21) (actual time=0.131..1.024 rows=4,370 loops=337)

24. 2.640 54.207 ↓ 106.6 4,370 1

Subquery Scan on zz15 (cost=20,690.91..21,056.58 rows=41 width=21) (actual time=43.847..54.207 rows=4,370 loops=1)

  • Filter: (zz15.rn = 1)
  • Rows Removed by Filter: 3458
25. 6.069 51.567 ↑ 1.0 7,828 1

WindowAgg (cost=20,690.91..20,955.01 rows=8,126 width=55) (actual time=43.845..51.567 rows=7,828 loops=1)

26. 8.020 45.498 ↑ 1.0 7,828 1

Sort (cost=20,690.91..20,711.23 rows=8,126 width=25) (actual time=43.839..45.498 rows=7,828 loops=1)

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

Gather (cost=1,004.32..20,163.20 rows=8,126 width=25) (actual time=2.433..37.478 rows=7,828 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
28. 4.236 33.702 ↑ 1.2 3,914 2

Hash Left Join (cost=4.31..18,317.14 rows=4,780 width=25) (actual time=1.071..33.702 rows=3,914 loops=2)

  • Hash Cond: (b1_1.bllbl_entty_id = b2_1.bllbl_entty_id)
29. 14.401 29.394 ↑ 1.2 3,914 2

Nested Loop (cost=0.42..18,205.61 rows=4,780 width=25) (actual time=0.956..29.394 rows=3,914 loops=2)

30. 14.990 14.990 ↑ 1.2 4,114 2

Parallel Seq Scan on mttr_bllbl_entty b1_1 (cost=0.00..4,180.67 rows=4,839 width=27) (actual time=0.264..14.990 rows=4,114 loops=2)

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

Index Only Scan using mttr_idx15 on mttr m_2 (cost=0.42..2.89 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=8,227)

  • Index Cond: (mttr_id = (b1_1.mttr_id)::text)
  • Filter: ((dstrt_id <> '35'::numeric) OR ((dstrt_id = '35'::numeric) AND ((b1_1.tm_trckg_fl)::text = 'N'::text)))
  • Rows Removed by Filter: 0
  • Heap Fetches: 10
32. 0.040 0.072 ↑ 1.0 84 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
33. 0.032 0.032 ↑ 1.0 84 2

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

34. 50,164.001 52,257.916 ↓ 196.1 565,538 337

Materialize (cost=89,986.13..108,743.65 rows=2,884 width=27) (actual time=3.424..155.068 rows=565,538 loops=337)

35. 248.331 2,093.915 ↓ 196.1 565,568 1

Subquery Scan on zz16 (cost=89,986.13..108,729.23 rows=2,884 width=27) (actual time=1,152.688..2,093.915 rows=565,568 loops=1)

  • Filter: (zz16.rn = 1)
  • Rows Removed by Filter: 11143
36. 493.769 1,845.584 ↑ 1.0 576,711 1

WindowAgg (cost=89,986.13..101,520.35 rows=576,711 width=43) (actual time=1,152.686..1,845.584 rows=576,711 loops=1)

37. 803.902 1,351.815 ↑ 1.0 576,711 1

Sort (cost=89,986.13..91,427.90 rows=576,711 width=35) (actual time=1,152.680..1,351.815 rows=576,711 loops=1)

  • Sort Key: o1_1.mttr_id, o1_1.last_updt_dt DESC
  • Sort Method: external merge Disk: 24920kB
38. 411.501 547.913 ↑ 1.0 576,711 1

Hash Join (cost=38.24..19,031.12 rows=576,711 width=35) (actual time=0.952..547.913 rows=576,711 loops=1)

  • Hash Cond: (o1_1.orgn_id = o2_1.orgn_id)
39. 135.477 135.477 ↑ 1.0 576,711 1

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

40. 0.253 0.935 ↑ 1.0 674 1

Hash (cost=29.81..29.81 rows=674 width=20) (actual time=0.935..0.935 rows=674 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
41. 0.440 0.682 ↑ 1.0 674 1

Hash Join (cost=5.60..29.81 rows=674 width=20) (actual time=0.076..0.682 rows=674 loops=1)

  • Hash Cond: (o2_1.orgn_ctgry_id = o3_1.orgn_ctgry_id)
42. 0.176 0.176 ↑ 1.0 674 1

Seq Scan on orgn_lk o2_1 (cost=0.00..15.74 rows=674 width=10) (actual time=0.005..0.176 rows=674 loops=1)

43. 0.037 0.066 ↑ 2.0 81 1

Hash (cost=3.60..3.60 rows=160 width=20) (actual time=0.066..0.066 rows=81 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
44. 0.029 0.029 ↑ 2.0 81 1

Seq Scan on orgn_ctgry_lk o3_1 (cost=0.00..3.60 rows=160 width=20) (actual time=0.006..0.029 rows=81 loops=1)

45. 58,582.513 61,676.392 ↓ 142.3 655,607 337

Materialize (cost=177,115.21..207,091.91 rows=4,608 width=34) (actual time=5.057..183.016 rows=655,607 loops=337)

46. 339.604 3,093.879 ↓ 142.4 655,962 1

Subquery Scan on zz (cost=177,115.21..207,068.87 rows=4,608 width=34) (actual time=1,703.084..3,093.879 rows=655,962 loops=1)

  • Filter: (zz.rn = 1)
  • Rows Removed by Filter: 265689
47. 743.832 2,754.275 ↑ 1.0 921,651 1

WindowAgg (cost=177,115.21..195,548.23 rows=921,651 width=50) (actual time=1,703.082..2,754.275 rows=921,651 loops=1)

48. 1,174.259 2,010.443 ↑ 1.0 921,651 1

Sort (cost=177,115.21..179,419.34 rows=921,651 width=42) (actual time=1,703.073..2,010.443 rows=921,651 loops=1)

  • Sort Key: c1.mttr_id, c1.last_updt_dt DESC
  • Sort Method: external merge Disk: 49096kB
49. 623.836 836.184 ↑ 1.0 921,651 1

Hash Join (cost=31.15..29,100.89 rows=921,651 width=42) (actual time=0.602..836.184 rows=921,651 loops=1)

  • Hash Cond: (c1.cause_code_id = c2.cause_code_id)
50. 211.765 211.765 ↑ 1.0 921,651 1

Seq Scan on mttr_cause c1 (cost=0.00..17,443.51 rows=921,651 width=25) (actual time=0.009..211.765 rows=921,651 loops=1)

51. 0.316 0.583 ↑ 1.0 851 1

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

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

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

53. 27,037.281 28,520.310 ↓ 527.9 313,598 337

Materialize (cost=114,661.72..118,523.97 rows=594 width=16) (actual time=2.788..84.630 rows=313,598 loops=337)

54. 143.921 1,483.029 ↓ 527.9 313,600 1

Subquery Scan on zz17 (cost=114,661.72..118,521.00 rows=594 width=16) (actual time=938.156..1,483.029 rows=313,600 loops=1)

  • Filter: (zz17.rn = 1)
  • Rows Removed by Filter: 51021
55. 296.760 1,339.108 ↓ 3.1 364,621 1

WindowAgg (cost=114,661.72..117,036.66 rows=118,747 width=80) (actual time=938.154..1,339.108 rows=364,621 loops=1)

56. 720.484 1,042.348 ↓ 3.1 364,621 1

Sort (cost=114,661.72..114,958.59 rows=118,747 width=18) (actual time=938.147..1,042.348 rows=364,621 loops=1)

  • Sort Key: c1_1.mttr_id, c1_1.prmry_fl DESC NULLS LAST
  • Sort Method: external merge Disk: 10352kB
57. 0.000 321.864 ↓ 3.1 364,621 1

Gather (cost=3,263.29..102,216.81 rows=118,747 width=18) (actual time=13.652..321.864 rows=364,621 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
58. 123.562 405.625 ↓ 2.5 121,540 3

Hash Join (cost=2,263.29..89,342.11 rows=49,478 width=18) (actual time=12.807..405.625 rows=121,540 loops=3)

  • Hash Cond: ((c1_1.mp_id)::text = (c2_1.mkt_prtcp_id)::text)
59. 269.394 269.394 ↑ 1.3 264,814 3

Parallel Seq Scan on mttr_cntct c1_1 (cost=0.00..62,048.81 rows=338,417 width=18) (actual time=0.005..269.394 rows=264,814 loops=3)

  • Filter: (((actv_ind)::text = 'A'::text) AND ((row_type)::text = 'F'::text))
  • Rows Removed by Filter: 430217
60. 2.356 12.669 ↓ 1.1 6,596 3

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

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

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

  • Filter: (((mkt_cntr_id)::text ~~ 'NASDAQ'::text) AND (xprtn_dt = '2399-12-31'::date))
  • Rows Removed by Filter: 55102
62. 3,872.844 4,096.572 ↓ 189.5 38,280 337

Materialize (cost=88,180.89..89,797.30 rows=202 width=44) (actual time=0.382..12.156 rows=38,280 loops=337)

63. 17.159 223.728 ↓ 189.5 38,280 1

Subquery Scan on zz_1 (cost=88,180.89..89,796.29 rows=202 width=44) (actual time=127.358..223.728 rows=38,280 loops=1)

  • Filter: (zz_1.rn = 1)
  • Rows Removed by Filter: 2513
64. 65.467 206.569 ↓ 1.0 40,793 1

WindowAgg (cost=88,180.89..89,291.48 rows=40,385 width=58) (actual time=127.356..206.569 rows=40,793 loops=1)

65. 81.998 141.102 ↓ 1.0 40,793 1

Sort (cost=88,180.89..88,281.85 rows=40,385 width=300) (actual time=127.267..141.102 rows=40,793 loops=1)

  • Sort Key: mttr_cmmnt.mttr_id, mttr_cmmnt.mttr_cmmnt_id DESC
  • Sort Method: external merge Disk: 6456kB
66. 50.573 59.104 ↓ 1.0 40,793 1

Bitmap Heap Scan on mttr_cmmnt (cost=893.97..79,431.63 rows=40,385 width=300) (actual time=13.346..59.104 rows=40,793 loops=1)

  • Recheck Cond: (mttr_cmmnt_type_id = '14'::numeric)
  • Filter: ((actv_ind)::text = 'A'::text)
  • Rows Removed by Filter: 241
  • Heap Blocks: exact=29945
67. 8.531 8.531 ↓ 1.0 41,034 1

Bitmap Index Scan on mttr_cmmnt_idx2 (cost=0.00..883.88 rows=40,993 width=0) (actual time=8.531..8.531 rows=41,034 loops=1)

  • Index Cond: (mttr_cmmnt_type_id = '14'::numeric)
68. 71,177.689 82,180.483 ↓ 60.6 795,480 337

Materialize (cost=511,717.09..616,863.00 rows=13,135 width=44) (actual time=16.338..243.859 rows=795,480 loops=337)

69. 757.647 11,002.794 ↓ 60.6 796,050 1

Subquery Scan on zz18 (cost=511,717.09..616,797.33 rows=13,135 width=44) (actual time=5,504.679..11,002.794 rows=796,050 loops=1)

  • Filter: (zz18.rn = 1)
  • Rows Removed by Filter: 1830957
70. 3,781.443 10,245.147 ↓ 1.0 2,627,007 1

WindowAgg (cost=511,717.09..583,959.75 rows=2,627,006 width=54) (actual time=5,504.677..10,245.147 rows=2,627,007 loops=1)

71. 3,777.365 6,463.704 ↓ 1.0 2,627,007 1

Sort (cost=511,717.09..518,284.60 rows=2,627,006 width=31) (actual time=5,504.660..6,463.704 rows=2,627,007 loops=1)

  • Sort Key: s1.mttr_id, s1.prmry_fl DESC NULLS LAST
  • Sort Method: external merge Disk: 99848kB
72. 1,714.254 2,686.339 ↓ 1.0 2,627,007 1

Hash Join (cost=394.68..105,899.73 rows=2,627,006 width=31) (actual time=7.114..2,686.339 rows=2,627,007 loops=1)

  • Hash Cond: (s1.staff_id = s2.staff_id)
73. 965.002 965.002 ↓ 1.0 2,627,007 1

Seq Scan on mttr_staff s1 (cost=0.00..72,341.58 rows=2,627,006 width=19) (actual time=0.008..965.002 rows=2,627,007 loops=1)

  • Filter: ((actv_ind)::text = 'A'::text)
74. 3.754 7.083 ↓ 1.0 9,409 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 635kB
75. 3.329 3.329 ↓ 1.0 9,409 1

Seq Scan on staff s2 (cost=0.00..277.08 rows=9,408 width=22) (actual time=0.006..3.329 rows=9,409 loops=1)

76. 58,359.393 62,861.621 ↓ 106.8 647,451 337

Materialize (cost=250,389.62..289,843.42 rows=6,065 width=37) (actual time=8.205..186.533 rows=647,451 loops=337)

77. 400.404 4,502.228 ↓ 106.8 647,725 1

Subquery Scan on zz19 (cost=250,389.62..289,813.09 rows=6,065 width=37) (actual time=2,763.647..4,502.228 rows=647,725 loops=1)

  • Filter: (zz19.rn = 1)
  • Rows Removed by Filter: 565305
78. 930.804 4,101.824 ↑ 1.0 1,213,030 1

WindowAgg (cost=250,389.62..274,650.22 rows=1,213,030 width=51) (actual time=2,763.645..4,101.824 rows=1,213,030 loops=1)

79. 1,930.555 3,171.020 ↑ 1.0 1,213,030 1

Sort (cost=250,389.62..253,422.19 rows=1,213,030 width=43) (actual time=2,763.638..3,171.020 rows=1,213,030 loops=1)

  • Sort Key: p1.mttr_id, p1.mttr_prdct_id DESC
  • Sort Method: external merge Disk: 55456kB
80. 822.710 1,240.465 ↑ 1.0 1,213,030 1

Hash Left Join (cost=4.18..53,177.82 rows=1,213,030 width=43) (actual time=0.089..1,240.465 rows=1,213,030 loops=1)

  • Hash Cond: (p1.prdct_id = p2.prdct_id)
81. 417.682 417.682 ↑ 1.0 1,213,030 1

Seq Scan on mttr_prdct p1 (cost=0.00..37,965.16 rows=1,213,030 width=27) (actual time=0.008..417.682 rows=1,213,030 loops=1)

  • Filter: ((actv_ind)::text = 'A'::text)
  • Rows Removed by Filter: 107703
82. 0.040 0.073 ↑ 1.0 97 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
83. 0.033 0.033 ↑ 1.0 97 1

Seq Scan on prdct_lk p2 (cost=0.00..2.97 rows=97 width=26) (actual time=0.006..0.033 rows=97 loops=1)

84. 2,252.941 5,032.421 ↑ 7.7 31,701 337

Materialize (cost=557,808.37..565,415.33 rows=243,109 width=44) (actual time=8.142..14.933 rows=31,701 loops=337)

85. 12.976 2,779.480 ↑ 7.7 31,728 1

Subquery Scan on m8 (cost=557,808.37..562,062.78 rows=243,109 width=44) (actual time=2,743.618..2,779.480 rows=31,728 loops=1)

86. 16.521 2,766.504 ↑ 7.7 31,728 1

Unique (cost=557,808.37..559,631.69 rows=243,109 width=44) (actual time=2,743.617..2,766.504 rows=31,728 loops=1)

87. 20.715 2,749.983 ↑ 7.7 31,728 1

Sort (cost=557,808.37..558,416.15 rows=243,109 width=44) (actual time=2,743.615..2,749.983 rows=31,728 loops=1)

  • 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))
  • Sort Method: quicksort Memory: 3247kB
88. 86.358 2,729.268 ↑ 7.7 31,728 1

Finalize GroupAggregate (cost=455,167.03..528,581.26 rows=243,109 width=44) (actual time=2,290.556..2,729.268 rows=31,728 loops=1)

  • Group Key: tm_entry.mttr_id
89. 0.000 2,642.910 ↑ 5.6 86,293 1

Gather Merge (cost=455,167.03..521,895.76 rows=486,218 width=44) (actual time=2,290.531..2,642.910 rows=86,293 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
90. 563.640 7,764.765 ↑ 8.5 28,764 3

Partial GroupAggregate (cost=454,167.01..464,774.12 rows=243,109 width=44) (actual time=2,287.325..2,588.255 rows=28,764 loops=3)

  • Group Key: tm_entry.mttr_id
91. 1,905.588 7,201.125 ↓ 1.2 291,685 3

Sort (cost=454,167.01..454,797.69 rows=252,275 width=22) (actual time=2,287.309..2,400.375 rows=291,685 loops=3)

  • Sort Key: tm_entry.mttr_id
  • Sort Method: external merge Disk: 9176kB
92. 5,172.952 5,295.537 ↓ 1.2 291,685 3

Parallel Bitmap Heap Scan on tm_entry (cost=20,915.15..426,355.59 rows=252,275 width=22) (actual time=134.065..1,765.179 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=18847 lossy=87350
93. 122.585 122.585 ↑ 1.0 976,299 1

Bitmap Index Scan on tm_entry_idx2 (cost=0.00..20,763.78 rows=982,554 width=0) (actual time=122.585..122.585 rows=976,299 loops=1)

  • Index Cond: (bllbl_entty_id = ANY ('{23,24,25,26,8,15}'::numeric[]))
94. 7.519 152.167 ↑ 2.0 7,808 1

Sort (cost=12,293.76..12,332.27 rows=15,402 width=12) (actual time=150.574..152.167 rows=7,808 loops=1)

  • Sort Key: o1.mttr_id
  • Sort Method: quicksort Memory: 561kB
95. 3.122 144.648 ↑ 2.0 7,852 1

Gather (cost=1,018.04..11,222.49 rows=15,402 width=12) (actual time=0.802..144.648 rows=7,852 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
96. 78.231 141.526 ↑ 2.5 2,617 3

Hash Join (cost=18.04..8,682.29 rows=6,418 width=12) (actual time=0.221..141.526 rows=2,617 loops=3)

  • Hash Cond: (o1.orgn_id = o2.orgn_id)
97. 63.219 63.219 ↑ 1.2 192,237 3

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

98. 0.015 0.076 ↑ 1.0 18 3

Hash (cost=17.82..17.82 rows=18 width=5) (actual time=0.076..0.076 rows=18 loops=3)

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

Nested Loop (cost=4.41..17.82 rows=18 width=5) (actual time=0.034..0.061 rows=18 loops=3)

100. 0.020 0.020 ↑ 1.0 1 3

Seq Scan on orgn_ctgry_lk o3 (cost=0.00..4.00 rows=1 width=5) (actual time=0.012..0.020 rows=1 loops=3)

  • Filter: (orgn_ctgry_id = '18'::numeric)
  • Rows Removed by Filter: 80
101. 0.014 0.027 ↑ 1.0 18 3

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

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

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

  • Index Cond: (orgn_ctgry_id = '18'::numeric)