explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BWZd

Settings
# exclusive inclusive rows x rows loops node
1. 2,631.031 2,631.031 ↑ 73.2 1,874 1

CTE Scan on group_metrics (cost=212,786,101.11..213,403,318.17 rows=137,159 width=164) (actual time=2,622.497..2,631.031 rows=1,874 loops=1)

  • Filter: (client_id = 220)
  • Rows Removed by Filter: 790
2.          

CTE group_metrics

3. 5.189 2,629.070 ↑ 10,297.2 2,664 1

Merge Left Join (cost=208,510,828.68..212,786,101.11 rows=27,431,869 width=164) (actual time=2,622.494..2,629.070 rows=2,664 loops=1)

  • Merge Cond: (psumm.modality = ds.modality)
  • Join Filter: ((ds.client_id = psumm.client_id) AND (ds.type = psumm.type) AND ((CASE WHEN (ds.organization_id > 0) THEN org.organization_name WHEN (ds.organization_id = 0) THEN 'All'::text ELSE NULL::text END) = psumm.organization))
  • Rows Removed by Join Filter: 15035
4. 1.489 815.774 ↑ 10,297.2 2,664 1

Sort (cost=63,295,421.20..63,364,000.87 rows=27,431,869 width=148) (actual time=815.417..815.774 rows=2,664 loops=1)

  • Sort Key: psumm.modality
  • Sort Method: quicksort Memory: 388kB
5.          

CTE cur_timestamp_table

6. 0.016 0.016 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1)

7.          

CTE cur_time

8. 0.012 0.063 ↑ 1.0 3 1

Nested Loop (cost=0.00..30.10 rows=3 width=20) (actual time=0.028..0.063 rows=3 loops=1)

9. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on cur_timestamp_table (cost=0.00..0.02 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=1)

10. 0.033 0.033 ↑ 1.0 3 1

Seq Scan on client (cost=0.00..30.03 rows=3 width=20) (actual time=0.003..0.033 rows=3 loops=1)

11.          

CTE historical_time

12. 0.026 0.028 ↑ 250.0 12 1

ProjectSet (cost=0.00..15.20 rows=3,000 width=28) (actual time=0.017..0.028 rows=12 loops=1)

13. 0.002 0.002 ↑ 1.0 3 1

CTE Scan on cur_time ct (cost=0.00..0.06 rows=3 width=20) (actual time=0.001..0.002 rows=3 loops=1)

14.          

CTE historical_boundaries

15. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=75.00..75.01 rows=1 width=16) (never executed)

16. 0.000 0.000 ↓ 0.0 0

CTE Scan on historical_time (cost=0.00..60.00 rows=3,000 width=16) (never executed)

17.          

CTE historical_procedures_of_interest

18. 238.218 439.590 ↑ 40.4 8,352 1

Merge Join (cost=233.68..79,896.76 rows=337,163 width=63) (actual time=9.982..439.590 rows=8,352 loops=1)

  • Merge Cond: (p.client_id = ht.client_id)
  • Join Filter: ((p.release_time >= ht.start_time) AND (p.release_time < ht.end_time))
  • Rows Removed by Join Filter: 800468
19. 138.840 138.840 ↑ 1.0 202,205 1

Index Scan using idx__procedure__organization on procedure p (cost=0.42..16,782.54 rows=202,298 width=62) (actual time=0.022..138.840 rows=202,205 loops=1)

20. 62.495 62.532 ↓ 261.4 784,269 1

Sort (cost=233.26..240.76 rows=3,000 width=28) (actual time=0.046..62.532 rows=784,269 loops=1)

  • Sort Key: ht.client_id
  • Sort Method: quicksort Memory: 25kB
21. 0.037 0.037 ↑ 250.0 12 1

CTE Scan on historical_time ht (cost=0.00..60.00 rows=3,000 width=28) (actual time=0.019..0.037 rows=12 loops=1)

22.          

CTE todays_procedures_of_interest

23. 16.792 62.551 ↑ 4.8 1,743 1

Hash Join (cost=0.10..7,056.21 rows=8,422 width=62) (actual time=0.254..62.551 rows=1,743 loops=1)

  • Hash Cond: (p_1.client_id = ct_1.client_id)
  • Join Filter: ((p_1.release_time >= ct_1.today_start_of_day) AND (p_1.release_time <= ct_1."current_time"))
  • Rows Removed by Join Filter: 74645
24. 45.690 45.690 ↓ 1.0 76,388 1

Seq Scan on procedure p_1 (cost=0.00..5,634.98 rows=75,794 width=62) (actual time=0.013..45.690 rows=76,388 loops=1)

  • Filter: (release_time IS NOT NULL)
  • Rows Removed by Filter: 125817
25. 0.003 0.069 ↑ 1.0 3 1

Hash (cost=0.06..0.06 rows=3 width=20) (actual time=0.069..0.069 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.066 0.066 ↑ 1.0 3 1

CTE Scan on cur_time ct_1 (cost=0.00..0.06 rows=3 width=20) (actual time=0.030..0.066 rows=3 loops=1)

27.          

CTE todays_procedures

28. 0.823 68.466 ↑ 244.0 6,972 1

Append (cost=0.00..26,066.09 rows=1,701,244 width=108) (actual time=0.259..68.466 rows=6,972 loops=1)

29. 1.319 64.695 ↑ 483.2 1,743 1

ProjectSet (cost=0.00..4,442.61 rows=842,200 width=108) (actual time=0.258..64.695 rows=1,743 loops=1)

30. 63.376 63.376 ↑ 4.8 1,743 1

CTE Scan on todays_procedures_of_interest p_4 (cost=0.00..168.44 rows=8,422 width=108) (actual time=0.255..63.376 rows=1,743 loops=1)

31. 0.458 0.458 ↑ 4.8 1,743 1

CTE Scan on todays_procedures_of_interest p_2 (cost=0.00..168.44 rows=8,422 width=108) (actual time=0.001..0.458 rows=1,743 loops=1)

32. 1.749 2.025 ↑ 483.2 1,743 1

ProjectSet (cost=0.00..4,442.61 rows=842,200 width=108) (actual time=0.010..2.025 rows=1,743 loops=1)

33. 0.276 0.276 ↑ 4.8 1,743 1

CTE Scan on todays_procedures_of_interest p_5 (cost=0.00..168.44 rows=8,422 width=76) (actual time=0.001..0.276 rows=1,743 loops=1)

34. 0.465 0.465 ↑ 4.8 1,743 1

CTE Scan on todays_procedures_of_interest p_3 (cost=0.00..168.44 rows=8,422 width=108) (actual time=0.001..0.465 rows=1,743 loops=1)

35.          

CTE historical_procedures

36. 4.221 470.670 ↑ 2,038.6 33,408 1

Append (cost=0.00..1,043,519.48 rows=68,106,926 width=117) (actual time=9.996..470.670 rows=33,408 loops=1)

37. 10.327 455.247 ↑ 4,036.9 8,352 1

ProjectSet (cost=0.00..177,853.48 rows=33,716,300 width=117) (actual time=9.995..455.247 rows=8,352 loops=1)

38. 444.920 444.920 ↑ 40.4 8,352 1

CTE Scan on historical_procedures_of_interest p_8 (cost=0.00..6,743.26 rows=337,163 width=117) (actual time=9.986..444.920 rows=8,352 loops=1)

39. 1.884 1.884 ↑ 40.4 8,352 1

CTE Scan on historical_procedures_of_interest p_6 (cost=0.00..6,743.26 rows=337,163 width=117) (actual time=0.002..1.884 rows=8,352 loops=1)

40. 5.819 7.037 ↑ 4,036.9 8,352 1

ProjectSet (cost=0.00..177,853.48 rows=33,716,300 width=117) (actual time=0.008..7.037 rows=8,352 loops=1)

41. 1.218 1.218 ↑ 40.4 8,352 1

CTE Scan on historical_procedures_of_interest p_9 (cost=0.00..6,743.26 rows=337,163 width=85) (actual time=0.000..1.218 rows=8,352 loops=1)

42. 2.281 2.281 ↑ 40.4 8,352 1

CTE Scan on historical_procedures_of_interest p_7 (cost=0.00..6,743.26 rows=337,163 width=117) (actual time=0.001..2.281 rows=8,352 loops=1)

43.          

CTE procedure_summary

44. 0.294 812.527 ↑ 10,349.0 2,664 1

Append (cost=59,543.54..56,135,500.01 rows=27,569,718 width=148) (actual time=77.974..812.527 rows=2,664 loops=1)

45. 5.546 79.306 ↑ 307.6 553 1

HashAggregate (cost=59,543.54..61,670.09 rows=170,124 width=148) (actual time=77.973..79.306 rows=553 loops=1)

  • Group Key: t.reading_group, t.organization, t.client_id, t.modality
46. 73.760 73.760 ↑ 244.0 6,972 1

CTE Scan on todays_procedures t (cost=0.00..34,024.88 rows=1,701,244 width=240) (actual time=0.264..73.760 rows=6,972 loops=1)

47. 15.240 609.813 ↑ 8,845.1 770 1

GroupAggregate (cost=17,905,277.17..19,216,335.50 rows=6,810,693 width=148) (actual time=592.052..609.813 rows=770 loops=1)

  • Group Key: h.reading_group, h.organization, h.client_id, h.modality
48. 95.678 594.573 ↑ 2,038.6 33,408 1

Sort (cost=17,905,277.17..18,075,544.48 rows=68,106,926 width=240) (actual time=590.641..594.573 rows=33,408 loops=1)

  • Sort Key: h.reading_group, h.organization, h.client_id, h.modality
  • Sort Method: quicksort Memory: 6235kB
49. 498.895 498.895 ↑ 2,038.6 33,408 1

CTE Scan on historical_procedures h (cost=0.00..1,362,138.52 rows=68,106,926 width=240) (actual time=10.010..498.895 rows=33,408 loops=1)

50. 4.542 34.170 ↑ 10,664.6 638 1

GroupAggregate (cost=9,463,442.28..10,178,448.62 rows=6,804,042 width=148) (actual time=29.015..34.170 rows=638 loops=1)

  • Group Key: h_1.reading_group, h_1.organization, h_1.client_id, h_1.modality
51. 21.617 29.628 ↑ 3,483.4 9,776 1

Sort (cost=9,463,442.28..9,548,575.94 rows=34,053,463 width=240) (actual time=28.661..29.628 rows=9,776 loops=1)

  • Sort Key: h_1.reading_group, h_1.organization, h_1.client_id, h_1.modality
  • Sort Method: quicksort Memory: 1759kB
52. 8.011 8.011 ↑ 3,483.4 9,776 1

CTE Scan on historical_procedures h_1 (cost=0.00..1,362,138.52 rows=34,053,463 width=240) (actual time=0.020..8.011 rows=9,776 loops=1)

  • Filter: precedes_current_time
  • Rows Removed by Filter: 23632
53. 4.505 7.571 ↑ 859.2 198 1

HashAggregate (cost=55,290.43..57,416.98 rows=170,124 width=148) (actual time=6.465..7.571 rows=198 loops=1)

  • Group Key: t_1.reading_group, t_1.organization, t_1.client_id
54. 3.066 3.066 ↑ 244.0 6,972 1

CTE Scan on todays_procedures t_1 (cost=0.00..34,024.88 rows=1,701,244 width=176) (actual time=0.014..3.066 rows=6,972 loops=1)

55. 9.647 62.191 ↑ 25,318.6 269 1

GroupAggregate (cost=16,042,976.17..17,183,767.19 rows=6,810,693 width=148) (actual time=52.953..62.191 rows=269 loops=1)

  • Group Key: h_2.reading_group, h_2.organization, h_2.client_id
56. 38.382 52.544 ↑ 2,038.6 33,408 1

Sort (cost=16,042,976.17..16,213,243.48 rows=68,106,926 width=176) (actual time=50.434..52.544 rows=33,408 loops=1)

  • Sort Key: h_2.reading_group, h_2.organization, h_2.client_id
  • Sort Method: quicksort Memory: 6235kB
57. 14.162 14.162 ↑ 2,038.6 33,408 1

CTE Scan on historical_procedures h_2 (cost=0.00..1,362,138.52 rows=68,106,926 width=176) (actual time=0.011..14.162 rows=33,408 loops=1)

58. 2.894 19.182 ↑ 28,830.7 236 1

GroupAggregate (cost=8,532,291.78..9,162,164.46 rows=6,804,042 width=148) (actual time=16.350..19.182 rows=236 loops=1)

  • Group Key: h_3.reading_group, h_3.organization, h_3.client_id
59. 9.974 16.288 ↑ 3,483.4 9,776 1

Sort (cost=8,532,291.78..8,617,425.44 rows=34,053,463 width=176) (actual time=15.688..16.288 rows=9,776 loops=1)

  • Sort Key: h_3.reading_group, h_3.organization, h_3.client_id
  • Sort Method: quicksort Memory: 1759kB
60. 6.314 6.314 ↑ 3,483.4 9,776 1

CTE Scan on historical_procedures h_3 (cost=0.00..1,362,138.52 rows=34,053,463 width=176) (actual time=0.017..6.314 rows=9,776 loops=1)

  • Filter: precedes_current_time
  • Rows Removed by Filter: 23632
61. 814.285 814.285 ↑ 10,297.2 2,664 1

CTE Scan on procedure_summary psumm (cost=0.00..551,394.36 rows=27,431,869 width=148) (actual time=77.977..814.285 rows=2,664 loops=1)

  • Filter: (organization IS NOT NULL)
62. 1.048 1,808.107 ↓ 12.2 15,243 1

Materialize (cost=145,215,407.48..145,215,426.26 rows=1,252 width=116) (actual time=1,807.055..1,808.107 rows=15,243 loops=1)

63. 0.104 1,807.059 ↑ 19.9 63 1

Sort (cost=145,215,407.48..145,215,410.61 rows=1,252 width=164) (actual time=1,807.050..1,807.059 rows=63 loops=1)

  • Sort Key: ds.modality
  • Sort Method: quicksort Memory: 31kB
64.          

CTE cur_timestamp_table

65. 0.017 0.017 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1)

66.          

CTE cur_time

67. 0.013 0.062 ↑ 1.0 3 1

Nested Loop (cost=0.00..30.10 rows=3 width=20) (actual time=0.032..0.062 rows=3 loops=1)

68. 0.019 0.019 ↑ 1.0 1 1

CTE Scan on cur_timestamp_table cur_timestamp_table_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=1)

69. 0.030 0.030 ↑ 1.0 3 1

Seq Scan on client client_1 (cost=0.00..30.03 rows=3 width=20) (actual time=0.003..0.030 rows=3 loops=1)

70.          

CTE historical_time

71. 0.025 0.027 ↑ 250.0 12 1

ProjectSet (cost=0.00..15.20 rows=3,000 width=28) (actual time=0.015..0.027 rows=12 loops=1)

72. 0.002 0.002 ↑ 1.0 3 1

CTE Scan on cur_time ct_2 (cost=0.00..0.06 rows=3 width=20) (actual time=0.001..0.002 rows=3 loops=1)

73.          

CTE historical_dictations_of_interest

74. 437.826 829.998 ↑ 39.2 20,332 1

Merge Join (cost=233.68..206,612.16 rows=796,742 width=89) (actual time=0.187..829.998 rows=20,332 loops=1)

  • Merge Cond: (d.client_id = ht_1.client_id)
  • Join Filter: ((d.completed_time >= ht_1.start_time) AND (d.completed_time < ht_1.end_time))
  • Rows Removed by Join Filter: 1893720
75. 257.326 257.326 ↓ 1.0 478,513 1

Index Scan using idx__dictation_client_id on dictation d (cost=0.42..55,794.72 rows=478,045 width=72) (actual time=0.018..257.326 rows=478,513 loops=1)

  • Filter: ((NOT is_addendum) AND (completed_time IS NOT NULL))
  • Rows Removed by Filter: 4255
76. 134.811 134.846 ↓ 629.3 1,888,025 1

Sort (cost=233.26..240.76 rows=3,000 width=28) (actual time=0.042..134.846 rows=1,888,025 loops=1)

  • Sort Key: ht_1.client_id
  • Sort Method: quicksort Memory: 25kB
77. 0.035 0.035 ↑ 250.0 12 1

CTE Scan on historical_time ht_1 (cost=0.00..60.00 rows=3,000 width=28) (actual time=0.017..0.035 rows=12 loops=1)

78.          

CTE todays_dictations_of_interest

79. 102.926 229.446 ↑ 46.3 1,147 1

Hash Join (cost=0.10..34,984.34 rows=53,116 width=88) (actual time=152.769..229.446 rows=1,147 loops=1)

  • Hash Cond: (d_1.client_id = ct_3.client_id)
  • Join Filter: ((d_1.completed_time >= ct_3.today_start_of_day) AND (d_1.completed_time <= ct_3."current_time"))
  • Rows Removed by Join Filter: 477366
80. 126.451 126.451 ↓ 1.0 478,513 1

Seq Scan on dictation d_1 (cost=0.00..25,888.11 rows=478,045 width=72) (actual time=0.018..126.451 rows=478,513 loops=1)

  • Filter: ((NOT is_addendum) AND (completed_time IS NOT NULL))
  • Rows Removed by Filter: 4255
81. 0.003 0.069 ↑ 1.0 3 1

Hash (cost=0.06..0.06 rows=3 width=20) (actual time=0.069..0.069 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
82. 0.066 0.066 ↑ 1.0 3 1

CTE Scan on cur_time ct_3 (cost=0.00..0.06 rows=3 width=20) (actual time=0.033..0.066 rows=3 loops=1)

83.          

CTE todays_dictations

84. 0.468 233.296 ↑ 2,342.7 4,580 1

Append (cost=0.00..164,394.02 rows=10,729,432 width=100) (actual time=152.787..233.296 rows=4,580 loops=1)

85. 1.027 231.064 ↑ 4,647.1 1,143 1

ProjectSet (cost=0.00..28,018.69 rows=5,311,600 width=100) (actual time=152.786..231.064 rows=1,143 loops=1)

86. 230.037 230.037 ↑ 46.3 1,147 1

CTE Scan on todays_dictations_of_interest d_4 (cost=0.00..1,062.32 rows=53,116 width=100) (actual time=152.774..230.037 rows=1,147 loops=1)

87. 0.309 0.309 ↑ 46.3 1,147 1

CTE Scan on todays_dictations_of_interest d_2 (cost=0.00..1,062.32 rows=53,116 width=100) (actual time=0.004..0.309 rows=1,147 loops=1)

88. 0.977 1.151 ↑ 4,647.1 1,143 1

ProjectSet (cost=0.00..28,018.69 rows=5,311,600 width=100) (actual time=0.008..1.151 rows=1,143 loops=1)

89. 0.174 0.174 ↑ 46.3 1,147 1

CTE Scan on todays_dictations_of_interest d_5 (cost=0.00..1,062.32 rows=53,116 width=92) (actual time=0.000..0.174 rows=1,147 loops=1)

90. 0.304 0.304 ↑ 46.3 1,147 1

CTE Scan on todays_dictations_of_interest d_3 (cost=0.00..1,062.32 rows=53,116 width=100) (actual time=0.001..0.304 rows=1,147 loops=1)

91.          

CTE historical_dictations

92. 8.539 908.829 ↑ 2,020.4 79,660 1

Append (cost=0.00..2,465,916.49 rows=160,941,884 width=109) (actual time=11.436..908.829 rows=79,660 loops=1)

93. 21.939 863.439 ↑ 4,086.3 19,498 1

ProjectSet (cost=0.00..420,281.41 rows=79,674,200 width=109) (actual time=11.435..863.439 rows=19,498 loops=1)

94. 841.500 841.500 ↑ 39.2 20,332 1

CTE Scan on historical_dictations_of_interest d_8 (cost=0.00..15,934.84 rows=796,742 width=109) (actual time=0.189..841.500 rows=20,332 loops=1)

95. 6.203 6.203 ↑ 39.2 20,332 1

CTE Scan on historical_dictations_of_interest d_6 (cost=0.00..15,934.84 rows=796,742 width=109) (actual time=0.002..6.203 rows=20,332 loops=1)

96. 20.312 24.417 ↑ 4,086.3 19,498 1

ProjectSet (cost=0.00..420,281.41 rows=79,674,200 width=109) (actual time=0.017..24.417 rows=19,498 loops=1)

97. 4.105 4.105 ↑ 39.2 20,332 1

CTE Scan on historical_dictations_of_interest d_9 (cost=0.00..15,934.84 rows=796,742 width=101) (actual time=0.001..4.105 rows=20,332 loops=1)

98. 6.231 6.231 ↑ 39.2 20,332 1

CTE Scan on historical_dictations_of_interest d_7 (cost=0.00..15,934.84 rows=796,742 width=109) (actual time=0.001..6.231 rows=20,332 loops=1)

99.          

CTE dictation_summary

100. 0.286 1,804.131 ↑ 20,605.5 2,442 1

Append (cost=2,604,381.34..141,210,525.10 rows=50,318,538 width=140) (actual time=245.001..1,804.131 rows=2,442 loops=1)

101. 3.583 248.649 ↑ 2,395.0 448 1

GroupAggregate (cost=2,604,381.34..2,896,758.35 rows=1,072,943 width=140) (actual time=244.999..248.649 rows=448 loops=1)

  • Group Key: t_2.reading_group, t_2.organization_id, t_2.client_id, t_2.modality
102. 8.090 245.066 ↑ 2,342.7 4,580 1

Sort (cost=2,604,381.34..2,631,204.92 rows=10,729,432 width=224) (actual time=244.649..245.066 rows=4,580 loops=1)

  • Sort Key: t_2.reading_group, t_2.organization_id, t_2.client_id, t_2.modality
  • Sort Method: quicksort Memory: 1043kB
103. 236.976 236.976 ↑ 2,342.7 4,580 1

CTE Scan on todays_dictations t_2 (cost=0.00..214,588.64 rows=10,729,432 width=224) (actual time=152.807..236.976 rows=4,580 loops=1)

104. 59.001 1,204.674 ↑ 22,991.7 700 1

GroupAggregate (cost=42,209,724.89..46,675,862.16 rows=16,094,188 width=140) (actual time=1,139.264..1,204.674 rows=700 loops=1)

  • Group Key: h_4.reading_group, h_4.organization_id, h_4.client_id, h_4.modality
105. 161.770 1,145.673 ↑ 2,020.4 79,660 1

Sort (cost=42,209,724.89..42,612,079.60 rows=160,941,884 width=224) (actual time=1,133.041..1,145.673 rows=79,660 loops=1)

  • Sort Key: h_4.reading_group, h_4.organization_id, h_4.client_id, h_4.modality
  • Sort Method: quicksort Memory: 24024kB
106. 983.903 983.903 ↑ 2,020.4 79,660 1

CTE Scan on historical_dictations h_4 (cost=0.00..3,218,837.68 rows=160,941,884 width=224) (actual time=11.454..983.903 rows=79,660 loops=1)

107. 21.019 86.777 ↑ 25,440.6 632 1

GroupAggregate (cost=22,311,928.32..24,765,859.83 rows=16,078,471 width=140) (actual time=63.782..86.777 rows=632 loops=1)

  • Group Key: h_5.reading_group, h_5.organization_id, h_5.client_id, h_5.modality
108. 47.525 65.758 ↑ 3,198.6 25,158 1

Sort (cost=22,311,928.32..22,513,105.68 rows=80,470,942 width=224) (actual time=61.881..65.758 rows=25,158 loops=1)

  • Sort Key: h_5.reading_group, h_5.organization_id, h_5.client_id, h_5.modality
  • Sort Method: quicksort Memory: 7445kB
109. 18.233 18.233 ↑ 3,198.6 25,158 1

CTE Scan on historical_dictations h_5 (cost=0.00..3,218,837.68 rows=80,470,942 width=224) (actual time=0.019..18.233 rows=25,158 loops=1)

  • Filter: precedes_current_time
  • Rows Removed by Filter: 54502
110. 2.909 8.610 ↑ 6,348.8 169 1

GroupAggregate (cost=2,311,000.84..2,576,554.27 rows=1,072,943 width=140) (actual time=6.048..8.610 rows=169 loops=1)

  • Group Key: t_3.reading_group, t_3.organization_id, t_3.client_id
111. 3.755 5.701 ↑ 2,342.7 4,580 1

Sort (cost=2,311,000.84..2,337,824.42 rows=10,729,432 width=160) (actual time=5.363..5.701 rows=4,580 loops=1)

  • Sort Key: t_3.reading_group, t_3.organization_id, t_3.client_id
  • Sort Method: quicksort Memory: 1043kB
112. 1.946 1.946 ↑ 2,342.7 4,580 1

CTE Scan on todays_dictations t_3 (cost=0.00..214,588.64 rows=10,729,432 width=160) (actual time=0.015..1.946 rows=4,580 loops=1)

113. 50.257 193.289 ↑ 31,128.4 257 1

GroupAggregate (cost=37,808,968.39..41,650,160.78 rows=8,000,000 width=140) (actual time=144.929..193.289 rows=257 loops=1)

  • Group Key: h_6.reading_group, h_6.organization_id, h_6.client_id
114. 101.602 143.032 ↑ 2,020.4 79,660 1

Sort (cost=37,808,968.39..38,211,323.10 rows=160,941,884 width=160) (actual time=133.103..143.032 rows=79,660 loops=1)

  • Sort Key: h_6.reading_group, h_6.organization_id, h_6.client_id
  • Sort Method: quicksort Memory: 24024kB
115. 41.430 41.430 ↑ 2,020.4 79,660 1

CTE Scan on historical_dictations h_6 (cost=0.00..3,218,837.68 rows=160,941,884 width=160) (actual time=0.019..41.430 rows=79,660 loops=1)

116. 16.047 61.846 ↑ 33,898.3 236 1

GroupAggregate (cost=20,111,548.32..22,142,144.33 rows=7,999,993 width=140) (actual time=46.526..61.846 rows=236 loops=1)

  • Group Key: h_7.reading_group, h_7.organization_id, h_7.client_id
117. 26.958 45.799 ↑ 3,198.6 25,158 1

Sort (cost=20,111,548.32..20,312,725.68 rows=80,470,942 width=160) (actual time=42.995..45.799 rows=25,158 loops=1)

  • Sort Key: h_7.reading_group, h_7.organization_id, h_7.client_id
  • Sort Method: quicksort Memory: 7445kB
118. 18.841 18.841 ↑ 3,198.6 25,158 1

CTE Scan on historical_dictations h_7 (cost=0.00..3,218,837.68 rows=80,470,942 width=160) (actual time=0.019..18.841 rows=25,158 loops=1)

  • Filter: precedes_current_time
  • Rows Removed by Filter: 54502
119. 0.541 1,806.955 ↑ 18.7 67 1

Hash Left Join (cost=5.31..1,132,865.38 rows=1,252 width=164) (actual time=245.094..1,806.955 rows=67 loops=1)

  • Hash Cond: (ds.organization_id = org.organization_id)
  • Filter: (CASE WHEN (ds.organization_id > 0) THEN org.organization_name WHEN (ds.organization_id = 0) THEN 'All'::text ELSE NULL::text END = 'All'::text)
  • Rows Removed by Filter: 1006
120. 1,806.346 1,806.346 ↑ 233.3 1,073 1

CTE Scan on dictation_summary ds (cost=0.00..1,132,167.10 rows=250,335 width=100) (actual time=245.006..1,806.346 rows=1,073 loops=1)

  • Filter: ((organization_id IS NOT NULL) AND ((reading_group)::text = 'All'::text))
  • Rows Removed by Filter: 1369
121. 0.030 0.068 ↑ 1.0 147 1

Hash (cost=3.47..3.47 rows=147 width=14) (actual time=0.068..0.068 rows=147 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
122. 0.038 0.038 ↑ 1.0 147 1

Seq Scan on organization org (cost=0.00..3.47 rows=147 width=14) (actual time=0.014..0.038 rows=147 loops=1)