explain.depesz.com

PostgreSQL's explain analyze made readable

Result: srmE

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 7,137.032 ↑ 1.0 20 1

Limit (cost=28,908.60..28,908.65 rows=20 width=544) (actual time=7,137.001..7,137.032 rows=20 loops=1)

2. 64.490 7,137.006 ↑ 44.0 20 1

Sort (cost=28,908.60..28,910.80 rows=880 width=544) (actual time=7,137.000..7,137.006 rows=20 loops=1)

  • Sort Key: user_program_status_history.created_at DESC
  • Sort Method: top-N heapsort Memory: 35kB
3. 288.570 7,072.516 ↓ 41.4 36,421 1

Nested Loop Left Join (cost=23,918.99..28,876.38 rows=880 width=544) (actual time=4,802.931..7,072.516 rows=36,421 loops=1)

4.          

CTE soft_search

5. 213.978 278.334 ↑ 1.0 38,489 1

Hash Join (cost=1,405.39..5,795.95 rows=38,489 width=48) (actual time=48.557..278.334 rows=38,489 loops=1)

  • Hash Cond: (u_1.recipient_id = r.id)
6. 16.170 16.170 ↑ 1.0 38,489 1

Seq Scan on users u_1 (cost=0.00..2,802.89 rows=38,489 width=32) (actual time=0.005..16.170 rows=38,489 loops=1)

7. 26.251 48.186 ↓ 1.0 40,815 1

Hash (cost=901.84..901.84 rows=40,284 width=29) (actual time=48.185..48.186 rows=40,815 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2998kB
8. 21.935 21.935 ↓ 1.0 40,815 1

Seq Scan on recipients r (cost=0.00..901.84 rows=40,284 width=29) (actual time=0.008..21.935 rows=40,815 loops=1)

9. 35.178 6,783.946 ↓ 41.4 36,421 1

Hash Left Join (cost=18,122.62..22,475.31 rows=880 width=472) (actual time=4,802.915..6,783.946 rows=36,421 loops=1)

  • Hash Cond: (up.id = user_program_status_history_8.user_program_id)
10. 270.392 6,748.768 ↓ 41.4 36,421 1

Nested Loop Left Join (cost=18,114.31..22,463.69 rows=880 width=464) (actual time=4,802.897..6,748.768 rows=36,421 loops=1)

11. 269.021 6,478.376 ↓ 43.6 36,421 1

Nested Loop Left Join (cost=18,113.89..21,890.91 rows=836 width=456) (actual time=4,802.890..6,478.376 rows=36,421 loops=1)

12. 325.533 6,209.355 ↓ 44.0 36,421 1

Nested Loop Left Join (cost=18,113.47..21,324.30 rows=827 width=448) (actual time=4,802.883..6,209.355 rows=36,421 loops=1)

13. 156.264 5,883.822 ↓ 44.1 36,421 1

Nested Loop Left Join (cost=18,113.05..20,759.06 rows=825 width=440) (actual time=4,802.875..5,883.822 rows=36,421 loops=1)

14. 226.074 5,727.558 ↓ 44.1 36,421 1

Nested Loop Left Join (cost=18,112.63..20,193.82 rows=825 width=432) (actual time=4,802.869..5,727.558 rows=36,421 loops=1)

15. 277.195 5,501.484 ↓ 44.1 36,421 1

Nested Loop Left Join (cost=18,112.21..19,628.58 rows=825 width=424) (actual time=4,802.862..5,501.484 rows=36,421 loops=1)

16. 254.886 5,224.289 ↓ 44.1 36,421 1

Nested Loop Left Join (cost=18,111.79..19,063.33 rows=825 width=416) (actual time=4,802.855..5,224.289 rows=36,421 loops=1)

17. 150.664 4,969.403 ↓ 44.1 36,421 1

Hash Right Join (cost=18,111.37..18,498.09 rows=825 width=408) (actual time=4,802.843..4,969.403 rows=36,421 loops=1)

  • Hash Cond: (upmt.user_program_id = up.id)
18. 46.817 119.184 ↑ 1.0 15,924 1

HashAggregate (cost=2,020.75..2,182.12 rows=16,137 width=12) (actual time=103.171..119.184 rows=15,924 loops=1)

  • Group Key: upmt.user_program_id
19. 38.354 72.367 ↑ 1.0 15,924 1

Hash Join (cost=812.08..1,940.06 rows=16,137 width=12) (actual time=20.947..72.367 rows=15,924 loops=1)

  • Hash Cond: (upmt.uid = lr.user_program_measurement_type_uid)
20. 13.117 13.117 ↑ 1.0 38,360 1

Seq Scan on user_program_measurement_types upmt (cost=0.00..821.08 rows=38,808 width=20) (actual time=0.010..13.117 rows=38,360 loops=1)

21. 12.051 20.896 ↑ 1.0 15,924 1

Hash (cost=610.37..610.37 rows=16,137 width=24) (actual time=20.896..20.896 rows=15,924 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 999kB
22. 8.845 8.845 ↑ 1.0 15,924 1

Seq Scan on user_program_latest_readings lr (cost=0.00..610.37 rows=16,137 width=24) (actual time=0.006..8.845 rows=15,924 loops=1)

23. 102.883 4,699.555 ↓ 44.1 36,421 1

Hash (cost=16,080.31..16,080.31 rows=825 width=400) (actual time=4,699.555..4,699.555 rows=36,421 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3969kB
24. 0.000 4,596.672 ↓ 44.1 36,421 1

Merge Join (cost=16,060.70..16,080.31 rows=825 width=400) (actual time=4,270.660..4,596.672 rows=36,421 loops=1)

  • Merge Cond: ((user_program_status_history_10.user_program_id = user_program_measurement_types.user_program_id) AND ((max(user_program_status_history_10.user_program_status_id)) = upsh.user_program_status_id))
  • Merge Cond: ((user_program_status_history_10.user_program_id = user_program_measurement_types.user_program_id) AND ((max(user_program_status_history_10.user_program_status_id)) = upsh.user_program_status_id))
25. 47.522 371.543 ↓ 22.8 38,360 1

Sort (cost=5,624.40..5,628.61 rows=1,685 width=44) (actual time=361.360..371.543 rows=38,360 loops=1)

  • Sort Key: user_program_status_history_10.user_program_id, (max(user_program_status_history_10.user_program_status_id))
  • Sort Method: quicksort Memory: 3943kB
26. 27.479 324.021 ↓ 22.8 38,360 1

Hash Join (cost=4,627.69..5,534.10 rows=1,685 width=44) (actual time=265.887..324.021 rows=38,360 loops=1)

  • Hash Cond: ((max(user_program_status_history_10.user_program_status_id)) = ups.id)
27. 236.954 296.524 ↓ 1.0 38,360 1

HashAggregate (cost=4,626.49..5,001.04 rows=37,455 width=8) (actual time=265.844..296.524 rows=38,360 loops=1)

  • Group Key: user_program_status_history_10.user_program_id
28. 59.570 59.570 ↑ 1.0 189,594 1

Seq Scan on user_program_status_history user_program_status_history_10 (cost=0.00..3,657.66 rows=193,766 width=8) (actual time=0.006..59.570 rows=189,594 loops=1)

29. 0.006 0.018 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=36) (actual time=0.018..0.018 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.012 0.012 ↑ 1.0 9 1

Seq Scan on user_program_statuses ups (cost=0.00..1.09 rows=9 width=36) (actual time=0.010..0.012 rows=9 loops=1)

31. 1,270.392 4,059.238 ↓ 171.9 151,234 1

Sort (cost=10,436.30..10,438.50 rows=880 width=376) (actual time=3,909.286..4,059.238 rows=151,234 loops=1)

  • Sort Key: up.id, upsh.user_program_status_id
  • Sort Method: external sort Disk: 64280kB
32. 614.845 2,788.846 ↓ 171.9 151,234 1

Nested Loop (cost=9,890.48..10,393.26 rows=880 width=376) (actual time=1,690.007..2,788.846 rows=151,234 loops=1)

33. 335.438 2,174.001 ↓ 165.6 36,421 1

Nested Loop (cost=9,890.06..10,219.29 rows=220 width=368) (actual time=1,689.995..2,174.001 rows=36,421 loops=1)

34. 137.295 1,838.563 ↓ 176.0 38,360 1

Hash Right Join (cost=9,889.64..10,070.52 rows=218 width=356) (actual time=1,689.980..1,838.563 rows=38,360 loops=1)

  • Hash Cond: (sl.user_uid = u.uid)
35. 28.051 30.750 ↑ 1.0 7,444 1

HashAggregate (cost=483.43..559.41 rows=7,598 width=32) (actual time=19.443..30.750 rows=7,444 loops=1)

  • Group Key: sl.user_uid
36. 2.699 2.699 ↑ 1.0 8,979 1

Seq Scan on support_logs sl (cost=0.00..416.09 rows=8,979 width=25) (actual time=0.009..2.699 rows=8,979 loops=1)

  • Filter: outreach_attempted
  • Rows Removed by Filter: 3030
37. 159.900 1,670.518 ↓ 176.0 38,360 1

Hash (cost=9,403.48..9,403.48 rows=218 width=356) (actual time=1,670.518..1,670.518 rows=38,360 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3969kB
38. 1,510.618 1,510.618 ↓ 176.0 38,360 1

Hash Join (cost=5,438.33..9,403.48 rows=218 width=356) (actual time=999.232..1,510.618 rows=38,360 loops=1)

39. 47.522 371.543 ↓ 22.8 38,360 1

Sort (cost=5,624.40..5,628.61 rows=1,685 width=44) (actual time=361.360..371.543 rows=38,360 loops=1)

  • Sort Key: user_program_status_history_10.user_program_id, (max(user_program_status_history_10.user_program_status_id))
  • Sort Method: quicksort Memory: 3943kB
40. 27.479 324.021 ↓ 22.8 38,360 1

Hash Join (cost=4,627.69..5,534.10 rows=1,685 width=44) (actual time=265.887..324.021 rows=38,360 loops=1)

  • Hash Cond: ((max(user_program_status_history_10.user_program_status_id)) = ups.id)
41. 236.954 296.524 ↓ 1.0 38,360 1

HashAggregate (cost=4,626.49..5,001.04 rows=37,455 width=8) (actual time=265.844..296.524 rows=38,360 loops=1)

  • Group Key: user_program_status_history_10.user_program_id
42. 59.570 59.570 ↑ 1.0 189,594 1

Seq Scan on user_program_status_history user_program_status_history_10 (cost=0.00..3,657.66 rows=193,766 width=8) (actual time=0.006..59.570 rows=189,594 loops=1)

43. 0.006 0.018 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=36) (actual time=0.018..0.018 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.012 0.012 ↑ 1.0 9 1

Seq Scan on user_program_statuses ups (cost=0.00..1.09 rows=9 width=36) (actual time=0.010..0.012 rows=9 loops=1)

45. 1,270.392 4,059.238 ↓ 171.9 151,234 1

Sort (cost=10,436.30..10,438.50 rows=880 width=376) (actual time=3,909.286..4,059.238 rows=151,234 loops=1)

  • Sort Key: up.id, upsh.user_program_status_id
  • Sort Method: external sort Disk: 64280kB
46. 104.951 2,788.846 ↓ 171.9 151,234 1

Nested Loop (cost=9,890.48..10,393.26 rows=880 width=376) (actual time=1,690.007..2,788.846 rows=151,234 loops=1)

47. 66.918 2,174.001 ↓ 165.6 36,421 1

Nested Loop (cost=9,890.06..10,219.29 rows=220 width=368) (actual time=1,689.995..2,174.001 rows=36,421 loops=1)

48. 137.295 1,838.563 ↓ 176.0 38,360 1

Hash Right Join (cost=9,889.64..10,070.52 rows=218 width=356) (actual time=1,689.980..1,838.563 rows=38,360 loops=1)

  • Hash Cond: (sl.user_uid = u.uid)
49. 28.051 30.750 ↑ 1.0 7,444 1

HashAggregate (cost=483.43..559.41 rows=7,598 width=32) (actual time=19.443..30.750 rows=7,444 loops=1)

  • Group Key: sl.user_uid
50. 2.699 2.699 ↑ 1.0 8,979 1

Seq Scan on support_logs sl (cost=0.00..416.09 rows=8,979 width=25) (actual time=0.009..2.699 rows=8,979 loops=1)

  • Filter: outreach_attempted
  • Rows Removed by Filter: 3030
51. 159.900 1,670.518 ↓ 176.0 38,360 1

Hash (cost=9,403.48..9,403.48 rows=218 width=356) (actual time=1,670.518..1,670.518 rows=38,360 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3969kB
52. 50.834 1,510.618 ↓ 176.0 38,360 1

Hash Join (cost=5,438.33..9,403.48 rows=218 width=356) (actual time=999.232..1,510.618 rows=38,360 loops=1)

  • Hash Cond: ((o.v2_id = ep.customer_id) AND (up.enrolling_provider_id = ep.id))
53. 39.610 1,458.630 ↓ 29.5 38,360 1

Nested Loop Left Join (cost=5,355.06..9,291.99 rows=1,302 width=373) (actual time=998.038..1,458.630 rows=38,360 loops=1)

54. 53.636 1,265.580 ↓ 29.5 38,360 1

Merge Join (cost=5,354.77..8,795.66 rows=1,302 width=331) (actual time=998.025..1,265.580 rows=38,360 loops=1)

  • Merge Cond: (user_program_measurement_types.user_program_id = up.id)
55. 116.390 167.463 ↑ 1.0 38,360 1

GroupAggregate (cost=0.29..2,936.55 rows=38,808 width=36) (actual time=0.045..167.463 rows=38,360 loops=1)

  • Group Key: user_program_measurement_types.user_program_id
56. 51.073 51.073 ↑ 1.0 38,360 1

Index Only Scan using user_program_measurement_types_ou_mti_sn_key on user_program_measurement_types (cost=0.29..2,257.41 rows=38,808 width=8) (actual time=0.036..51.073 rows=38,360 loops=1)

  • Heap Fetches: 38460
57. 206.572 1,044.481 ↓ 29.8 38,360 1

Sort (cost=5,354.48..5,357.69 rows=1,287 width=295) (actual time=997.974..1,044.481 rows=38,360 loops=1)

  • Sort Key: up.id
  • Sort Method: external sort Disk: 13400kB
58. 50.493 837.909 ↓ 29.8 38,360 1

Hash Join (cost=4,361.07..5,288.00 rows=1,287 width=295) (actual time=506.758..837.909 rows=38,360 loops=1)

  • Hash Cond: (s.uid = u.uid)
59. 329.245 329.245 ↑ 1.0 38,489 1

CTE Scan on soft_search s (cost=0.00..769.78 rows=38,489 width=48) (actual time=48.560..329.245 rows=38,489 loops=1)

60. 84.287 458.171 ↓ 29.9 38,360 1

Hash (cost=4,345.04..4,345.04 rows=1,282 width=279) (actual time=458.171..458.171 rows=38,360 loops=1)

  • Buckets: 16384 (originally 2048) Batches: 4 (originally 1) Memory Usage: 3969kB
61. 31.130 373.884 ↓ 29.9 38,360 1

Hash Join (cost=2,967.99..4,345.04 rows=1,282 width=279) (actual time=144.011..373.884 rows=38,360 loops=1)

  • Hash Cond: (up.program_uid = p.uid)
62. 62.101 342.741 ↓ 29.9 38,360 1

Hash Join (cost=2,966.87..4,326.30 rows=1,282 width=247) (actual time=143.985..342.741 rows=38,360 loops=1)

  • Hash Cond: ((up.reviewing_provider_id = rp.id) AND (o.v2_id = rp.customer_id))
63. 123.083 279.364 ↓ 5.0 38,360 1

Hash Join (cost=2,883.60..4,076.77 rows=7,672 width=215) (actual time=142.677..279.364 rows=38,360 loops=1)

  • Hash Cond: (up.user_uid = u.uid)
64. 13.727 13.727 ↑ 1.0 38,360 1

Seq Scan on user_programs up (cost=0.00..972.60 rows=38,360 width=55) (actual time=0.008..13.727 rows=38,360 loops=1)

65. 58.846 142.554 ↓ 5.0 38,489 1

Hash (cost=2,787.37..2,787.37 rows=7,698 width=160) (actual time=142.554..142.554 rows=38,489 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 4 (originally 1) Memory Usage: 3969kB
66. 28.123 83.708 ↓ 5.0 38,489 1

Nested Loop (cost=184.08..2,787.37 rows=7,698 width=160) (actual time=4.895..83.708 rows=38,489 loops=1)

67. 0.025 0.025 ↓ 5.0 5 1

Index Scan using idx_organizations_v2_id on organizations o (cost=0.13..12.22 rows=1 width=128) (actual time=0.011..0.025 rows=5 loops=1)

  • Filter: (account_uid = 'df00fad8-b6b2-48fd-aea0-401e552e0f74'::uuid)
68. 43.720 55.560 ↑ 1.0 7,698 5

Bitmap Heap Scan on users u (cost=183.95..2,698.17 rows=7,698 width=48) (actual time=2.578..11.112 rows=7,698 loops=5)

  • Recheck Cond: (organization_uid = o.uid)
  • Heap Blocks: exact=8064
69. 11.840 11.840 ↓ 1.0 7,869 5

Bitmap Index Scan on idx_users_organization_uid (cost=0.00..182.03 rows=7,698 width=0) (actual time=2.368..2.368 rows=7,869 loops=5)

  • Index Cond: (organization_uid = o.uid)
70. 0.850 1.276 ↑ 1.0 2,251 1

Hash (cost=49.51..49.51 rows=2,251 width=51) (actual time=1.276..1.276 rows=2,251 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 217kB
71. 0.426 0.426 ↑ 1.0 2,251 1

Seq Scan on providers rp (cost=0.00..49.51 rows=2,251 width=51) (actual time=0.006..0.426 rows=2,251 loops=1)

72. 0.007 0.013 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=48) (actual time=0.013..0.013 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
73. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on programs p (cost=0.00..1.05 rows=5 width=48) (actual time=0.005..0.006 rows=5 loops=1)

74. 153.440 153.440 ↑ 1.0 1 38,360

Index Scan using recipients_pkey on recipients rec (cost=0.29..0.37 rows=1 width=50) (actual time=0.003..0.004 rows=1 loops=38,360)

  • Index Cond: (u.recipient_id = id)
75. 0.724 1.154 ↑ 1.0 2,251 1

Hash (cost=49.51..49.51 rows=2,251 width=51) (actual time=1.154..1.154 rows=2,251 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 217kB
76. 0.430 0.430 ↑ 1.0 2,251 1

Seq Scan on providers ep (cost=0.00..49.51 rows=2,251 width=51) (actual time=0.007..0.430 rows=2,251 loops=1)

77. 268.520 268.520 ↑ 1.0 1 38,360

Index Scan using upsh_user_program_status_id_user_program_id_key on user_program_status_history (cost=0.42..0.67 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=38,360)

  • Index Cond: ((user_program_status_id = 2) AND (user_program_id = user_program_measurement_types.user_program_id))
78. 509.894 509.894 ↑ 1.0 4 36,421

Index Scan using idx_upsh_user_program_id on user_program_status_history upsh (cost=0.42..0.75 rows=4 width=8) (actual time=0.007..0.014 rows=4 loops=36,421)

  • Index Cond: (user_program_id = user_program_measurement_types.user_program_id)