explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mT5N : Optimization for: LG4605J; SG4606DI

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.456 41,114.574 ↑ 56.8 23,980 1

Append (cost=2,436,500.56..3,447,143.86 rows=1,361,869 width=784) (actual time=40,969.110..41,114.574 rows=23,980 loops=1)

2.          

CTE provider_accounts_list

3. 13.548 118.219 ↓ 5.0 145,152 1

Nested Loop (cost=1,085.11..68,200.48 rows=28,810 width=4) (actual time=1.995..118.219 rows=145,152 loops=1)

4. 0.015 0.015 ↑ 1.0 1 1

Index Only Scan using providers_pkey on providers public_2 (cost=0.27..8.29 rows=1 width=4) (actual time=0.012..0.015 rows=1 loops=1)

  • Index Cond: (id = 202)
  • Heap Fetches: 0
5. 0.000 104.656 ↓ 5.0 145,152 1

Gather (cost=1,084.84..67,904.09 rows=28,810 width=8) (actual time=1.982..104.656 rows=145,152 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 52.560 381.378 ↓ 4.0 48,384 3 / 3

Hash Join (cost=84.84..64,023.09 rows=12,004 width=8) (actual time=0.148..381.378 rows=48,384 loops=3)

  • Hash Cond: (public.company_id = public_1.id)
7. 328.775 328.775 ↑ 1.2 683,837 3 / 3

Parallel Seq Scan on provider_accounts public (cost=0.00..61,700.73 rows=850,073 width=12) (actual time=0.006..328.775 rows=683,837 loops=3)

8. 0.005 0.043 ↑ 1.0 22 3 / 3

Hash (cost=84.57..84.57 rows=22 width=8) (actual time=0.043..0.043 rows=22 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.025 0.038 ↑ 1.0 22 3 / 3

Bitmap Heap Scan on companies public_1 (cost=4.45..84.57 rows=22 width=8) (actual time=0.019..0.038 rows=22 loops=3)

  • Recheck Cond: (provider_id = 202)
  • Heap Blocks: exact=18
10. 0.013 0.013 ↑ 1.0 22 3 / 3

Bitmap Index Scan on index_companies_on_provider_id (cost=0.00..4.44 rows=22 width=0) (actual time=0.013..0.013 rows=22 loops=3)

  • Index Cond: (provider_id = 202)
11.          

CTE payday

12. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

13.          

CTE data

14. 9.186 40,909.751 ↑ 56.8 23,978 1

Hash Left Join (cost=2,361,618.88..2,368,300.07 rows=1,361,867 width=243) (actual time=40,702.774..40,909.751 rows=23,978 loops=1)

  • Hash Cond: (provider_accounts_list.provider_account_id = greatest_gross_earnings.provider_account_id)
15. 9.389 38,746.172 ↑ 7.6 23,978 1

Merge Left Join (cost=2,270,101.97..2,272,888.48 rows=182,801 width=235) (actual time=38,548.358..38,746.172 rows=23,978 loops=1)

  • Merge Cond: (provider_accounts_list.provider_account_id = public_26.provider_account_id)
16. 9.376 36,900.597 ↑ 1.0 23,978 1

Merge Left Join (cost=2,178,719.47..2,179,579.38 rows=24,537 width=231) (actual time=36,885.067..36,900.597 rows=23,978 loops=1)

  • Merge Cond: (provider_accounts_list.provider_account_id = public_24.provider_account_id)
17. 18.246 35,895.378 ↓ 85.6 23,978 1

Sort (cost=906,568.85..906,569.55 rows=280 width=215) (actual time=35,892.123..35,895.378 rows=23,978 loops=1)

  • Sort Key: public_3.id
  • Sort Method: quicksort Memory: 5,885kB
18. 10.614 35,877.132 ↓ 85.6 23,978 1

Hash Left Join (cost=893,802.92..906,557.47 rows=280 width=215) (actual time=33,413.724..35,877.132 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = settlements_agg.pay_period_id)
19. 4.899 35,428.577 ↓ 85.6 23,978 1

Hash Left Join (cost=805,540.73..818,294.55 rows=280 width=179) (actual time=32,975.768..35,428.577 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = net_earnings_reported.pay_period_id)
20. 5.580 31,325.901 ↓ 85.6 23,978 1

Hash Left Join (cost=715,490.39..728,243.47 rows=280 width=175) (actual time=28,877.982..31,325.901 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = pre_settlement_exclusions.pay_period_id)
21. 15.748 30,881.074 ↓ 85.6 23,978 1

Merge Left Join (cost=627,365.56..640,117.90 rows=280 width=143) (actual time=28,438.723..30,881.074 rows=23,978 loops=1)

  • Merge Cond: (public_6.id = public_13.user_id)
22. 21.445 3,478.361 ↓ 85.6 23,978 1

Sort (cost=274,272.54..274,273.24 rows=280 width=139) (actual time=3,474.456..3,478.361 rows=23,978 loops=1)

  • Sort Key: public_6.id
  • Sort Method: quicksort Memory: 5,809kB
23. 19.965 3,456.916 ↓ 85.6 23,978 1

Hash Left Join (cost=267,622.11..274,261.16 rows=280 width=139) (actual time=2,500.110..3,456.916 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = provider_payments_list.pay_period_id)
  • Filter: ((advance_stats.user_advanced > 0) OR (advance_stats.pre_settlements > 0) OR (provider_payments_list.provider_paid > 0) OR ((public_3.bank_link_status)::text = 'verified'::text))
  • Rows Removed by Filter: 34,731
24. 18.967 3,104.987 ↓ 152.5 58,709 1

Hash Left Join (cost=179,509.72..186,147.76 rows=385 width=131) (actual time=2,168.054..3,104.987 rows=58,709 loops=1)

  • Hash Cond: (public_7.id = advance_stats.pay_period_id)
25. 35.735 1,085.636 ↓ 152.5 58,709 1

Nested Loop (cost=86,890.87..93,527.89 rows=385 width=115) (actual time=167.659..1,085.636 rows=58,709 loops=1)

26. 23.239 873.774 ↓ 152.5 58,709 1

Nested Loop (cost=86,890.44..93,316.26 rows=385 width=108) (actual time=167.644..873.774 rows=58,709 loops=1)

27. 20.105 615.699 ↓ 152.5 58,709 1

Hash Join (cost=86,889.88..91,505.33 rows=385 width=94) (actual time=167.627..615.699 rows=58,709 loops=1)

  • Hash Cond: (public_3.provider_id = public_4.id)
28. 28.209 595.123 ↓ 152.5 58,709 1

Nested Loop (cost=86,804.14..91,418.56 rows=385 width=81) (actual time=167.148..595.123 rows=58,709 loops=1)

29. 34.367 449.496 ↓ 145.3 58,709 1

Nested Loop (cost=86,803.86..91,298.22 rows=404 width=70) (actual time=167.134..449.496 rows=58,709 loops=1)

  • Join Filter: (provider_accounts_list.provider_account_id = public_3.id)
30. 54.679 239.002 ↓ 56.6 58,709 1

Hash Join (cost=86,803.44..90,811.19 rows=1,037 width=16) (actual time=167.108..239.002 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list.provider_account_id = public_7.provider_account_id)
31. 19.358 19.358 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list (cost=0.00..576.20 rows=28,810 width=4) (actual time=1.997..19.358 rows=145,152 loops=1)

32. 34.637 164.965 ↓ 6.7 192,118 1

Hash (cost=86,445.91..86,445.91 rows=28,602 width=12) (actual time=164.965..164.965 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 10,304kB
33. 36.318 130.328 ↓ 6.7 192,118 1

Nested Loop (cost=862.23..86,445.91 rows=28,602 width=12) (actual time=22.702..130.328 rows=192,118 loops=1)

34. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on payday payday_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

35. 78.344 94.008 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_7 (cost=862.23..86,159.87 rows=28,602 width=12) (actual time=22.698..94.008 rows=192,118 loops=1)

  • Recheck Cond: (payment_expected_on = payday_1.payday)
  • Heap Blocks: exact=42,202
36. 15.664 15.664 ↓ 6.7 192,140 1

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.08 rows=28,602 width=0) (actual time=15.663..15.664 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_1.payday)
37. 176.127 176.127 ↑ 1.0 1 58,709

Index Scan using provider_accounts_pkey on provider_accounts public_3 (cost=0.43..0.46 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=58,709)

  • Index Cond: (id = public_7.provider_account_id)
38. 117.418 117.418 ↑ 1.0 1 58,709

Index Scan using companies_pkey on companies public_5 (cost=0.28..0.30 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=58,709)

  • Index Cond: (id = public_3.company_id)
39. 0.075 0.471 ↑ 1.1 393 1

Hash (cost=80.33..80.33 rows=433 width=21) (actual time=0.471..0.471 rows=393 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
40. 0.396 0.396 ↑ 1.1 393 1

Seq Scan on providers public_4 (cost=0.00..80.33 rows=433 width=21) (actual time=0.007..0.396 rows=393 loops=1)

41. 234.836 234.836 ↑ 1.0 1 58,709

Index Scan using users_pkey on users public_6 (cost=0.55..4.70 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=58,709)

  • Index Cond: (id = public_3.user_id)
42. 176.127 176.127 ↑ 1.0 1 58,709

Index Scan using employee_profiles_pkey on employee_profiles public_8 (cost=0.43..0.55 rows=1 width=15) (actual time=0.003..0.003 rows=1 loops=58,709)

  • Index Cond: (id = public_3.profile_id)
43. 1.074 2,000.384 ↓ 7.8 7,049 1

Hash (cost=92,607.62..92,607.62 rows=899 width=20) (actual time=2,000.384..2,000.384 rows=7,049 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 367kB
44. 0.695 1,999.310 ↓ 7.8 7,049 1

Subquery Scan on advance_stats (cost=92,573.90..92,607.62 rows=899 width=20) (actual time=1,991.633..1,999.310 rows=7,049 loops=1)

45. 5.518 1,998.615 ↓ 7.8 7,049 1

GroupAggregate (cost=92,573.90..92,598.63 rows=899 width=20) (actual time=1,991.632..1,998.615 rows=7,049 loops=1)

  • Group Key: public_9.pay_period_id
46. 8.557 1,993.097 ↓ 26.0 23,376 1

Sort (cost=92,573.90..92,576.15 rows=899 width=15) (actual time=1,991.623..1,993.097 rows=23,376 loops=1)

  • Sort Key: public_9.pay_period_id
  • Sort Method: quicksort Memory: 1,865kB
47. 45.798 1,984.540 ↓ 26.0 23,376 1

Nested Loop (cost=86,804.01..92,529.80 rows=899 width=15) (actual time=147.361..1,984.540 rows=23,376 loops=1)

48. 55.952 353.599 ↓ 56.6 58,709 1

Hash Join (cost=86,803.44..87,606.08 rows=1,037 width=4) (actual time=147.082..353.599 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_1.provider_account_id = public_10.provider_account_id)
49. 150.590 150.590 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_1 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.001..150.590 rows=145,152 loops=1)

50. 31.647 147.057 ↓ 6.7 192,118 1

Hash (cost=86,445.91..86,445.91 rows=28,602 width=8) (actual time=147.057..147.057 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
51. 22.219 115.410 ↓ 6.7 192,118 1

Nested Loop (cost=862.23..86,445.91 rows=28,602 width=8) (actual time=22.899..115.410 rows=192,118 loops=1)

52. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on payday payday_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

53. 77.428 93.189 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_10 (cost=862.23..86,159.87 rows=28,602 width=12) (actual time=22.895..93.189 rows=192,118 loops=1)

  • Recheck Cond: (payment_expected_on = payday_2.payday)
  • Heap Blocks: exact=42,202
54. 15.761 15.761 ↓ 6.7 192,140 1

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.08 rows=28,602 width=0) (actual time=15.761..15.761 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_2.payday)
55. 1,585.143 1,585.143 ↓ 0.0 0 58,709

Index Scan using index_advances_on_pay_period_id on advances public_9 (cost=0.57..4.70 rows=5 width=15) (actual time=0.024..0.027 rows=0 loops=58,709)

  • Index Cond: (pay_period_id = public_10.id)
  • Filter: ((status)::text = 'paid'::text)
  • Rows Removed by Filter: 22
56. 1.738 331.964 ↓ 54.8 11,625 1

Hash (cost=88,109.74..88,109.74 rows=212 width=12) (actual time=331.964..331.964 rows=11,625 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 628kB
57. 1.047 330.226 ↓ 54.8 11,625 1

Subquery Scan on provider_payments_list (cost=88,103.91..88,109.74 rows=212 width=12) (actual time=325.465..330.226 rows=11,625 loops=1)

58. 3.040 329.179 ↓ 54.8 11,625 1

GroupAggregate (cost=88,103.91..88,107.62 rows=212 width=12) (actual time=325.465..329.179 rows=11,625 loops=1)

  • Group Key: public_12.id
59. 4.188 326.139 ↓ 57.1 12,106 1

Sort (cost=88,103.91..88,104.44 rows=212 width=8) (actual time=325.456..326.139 rows=12,106 loops=1)

  • Sort Key: public_12.id
  • Sort Method: quicksort Memory: 952kB
60. 13.109 321.951 ↓ 57.1 12,106 1

Nested Loop (cost=86,803.87..88,095.72 rows=212 width=8) (actual time=146.144..321.951 rows=12,106 loops=1)

61. 28.561 191.424 ↓ 56.6 58,709 1

Hash Join (cost=86,803.44..87,606.08 rows=1,037 width=4) (actual time=146.065..191.424 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_2.provider_account_id = public_12.provider_account_id)
62. 16.841 16.841 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_2 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.001..16.841 rows=145,152 loops=1)

63. 31.125 146.022 ↓ 6.7 192,118 1

Hash (cost=86,445.91..86,445.91 rows=28,602 width=8) (actual time=146.022..146.022 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
64. 21.987 114.897 ↓ 6.7 192,118 1

Nested Loop (cost=862.23..86,445.91 rows=28,602 width=8) (actual time=22.741..114.897 rows=192,118 loops=1)

65. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on payday payday_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

66. 77.132 92.908 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_12 (cost=862.23..86,159.87 rows=28,602 width=12) (actual time=22.735..92.908 rows=192,118 loops=1)

  • Recheck Cond: (payment_expected_on = payday_3.payday)
  • Heap Blocks: exact=42,202
67. 15.776 15.776 ↓ 6.7 192,140 1

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.08 rows=28,602 width=0) (actual time=15.776..15.776 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_3.payday)
68. 117.418 117.418 ↓ 0.0 0 58,709

Index Scan using index_provider_payments_on_pay_period_id on provider_payments public_11 (cost=0.43..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=58,709)

  • Index Cond: (pay_period_id = public_12.id)
69. 764.836 27,386.965 ↑ 11.8 51,604 1

Unique (cost=353,093.02..358,213.84 rows=610,341 width=20) (actual time=24,964.239..27,386.965 rows=51,604 loops=1)

70. 8,136.133 26,622.129 ↓ 12.3 12,645,828 1

Sort (cost=353,093.02..355,653.43 rows=1,024,165 width=20) (actual time=24,964.236..26,622.129 rows=12,645,828 loops=1)

  • Sort Key: public_13.user_id, public_13.created_at DESC
  • Sort Method: external merge Disk: 421,232kB
71. 2,705.615 18,485.996 ↓ 12.4 12,660,143 1

Nested Loop (cost=99,104.53..250,850.54 rows=1,024,165 width=20) (actual time=1,145.481..18,485.996 rows=12,660,143 loops=1)

72. 100.677 1,265.181 ↓ 5.0 145,152 1

Hash Join (cost=99,103.96..99,755.79 rows=28,810 width=4) (actual time=1,145.406..1,265.181 rows=145,152 loops=1)

  • Hash Cond: (provider_accounts_list_3.provider_account_id = public_14.id)
73. 26.581 26.581 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_3 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.001..26.581 rows=145,152 loops=1)

74. 414.087 1,137.923 ↓ 1.0 2,051,510 1

Hash (cost=73,601.76..73,601.76 rows=2,040,176 width=8) (actual time=1,137.923..1,137.923 rows=2,051,510 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 96,522kB
75. 723.836 723.836 ↓ 1.0 2,051,510 1

Seq Scan on provider_accounts public_14 (cost=0.00..73,601.76 rows=2,040,176 width=8) (actual time=0.007..723.836 rows=2,051,510 loops=1)

76. 14,515.200 14,515.200 ↑ 1.2 87 145,152

Index Scan using index_available_balance_changes_on_user_id on available_balance_changes public_13 (cost=0.56..4.17 rows=107 width=20) (actual time=0.010..0.100 rows=87 loops=145,152)

  • Index Cond: (user_id = public_14.user_id)
77. 0.050 439.247 ↓ 39.2 235 1

Hash (cost=88,124.75..88,124.75 rows=6 width=36) (actual time=439.247..439.247 rows=235 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
78. 0.028 439.197 ↓ 39.2 235 1

Subquery Scan on pre_settlement_exclusions (cost=88,124.56..88,124.75 rows=6 width=36) (actual time=438.831..439.197 rows=235 loops=1)

79. 0.361 439.169 ↓ 39.2 235 1

GroupAggregate (cost=88,124.56..88,124.69 rows=6 width=36) (actual time=438.829..439.169 rows=235 loops=1)

  • Group Key: public_15.noteable_id
80. 0.206 438.808 ↓ 39.2 235 1

Sort (cost=88,124.56..88,124.57 rows=6 width=85) (actual time=438.790..438.808 rows=235 loops=1)

  • Sort Key: public_15.noteable_id
  • Sort Method: quicksort Memory: 84kB
81. 52.194 438.602 ↓ 39.2 235 1

Nested Loop (cost=86,803.87..88,124.48 rows=6 width=85) (actual time=153.098..438.602 rows=235 loops=1)

82. 42.358 210.281 ↓ 56.6 58,709 1

Hash Join (cost=86,803.44..87,606.08 rows=1,037 width=4) (actual time=151.324..210.281 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_4.provider_account_id = public_16.provider_account_id)
83. 16.643 16.643 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_4 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.001..16.643 rows=145,152 loops=1)

84. 29.997 151.280 ↓ 6.7 192,118 1

Hash (cost=86,445.91..86,445.91 rows=28,602 width=8) (actual time=151.280..151.280 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
85. 25.477 121.283 ↓ 6.7 192,118 1

Nested Loop (cost=862.23..86,445.91 rows=28,602 width=8) (actual time=25.782..121.283 rows=192,118 loops=1)

86. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on payday payday_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

87. 77.064 95.804 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_16 (cost=862.23..86,159.87 rows=28,602 width=12) (actual time=25.776..95.804 rows=192,118 loops=1)

  • Recheck Cond: (payment_expected_on = payday_4.payday)
  • Heap Blocks: exact=42,202
88. 18.740 18.740 ↓ 6.7 192,140 1

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.08 rows=28,602 width=0) (actual time=18.740..18.740 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_4.payday)
89. 176.127 176.127 ↓ 0.0 0 58,709

Index Scan using index_notes_on_noteable_type_and_noteable_id on notes public_15 (cost=0.43..0.49 rows=1 width=85) (actual time=0.003..0.003 rows=0 loops=58,709)

  • Index Cond: (((noteable_type)::text = 'PayPeriod'::text) AND (noteable_id = public_16.id))
90. 0.001 4,097.777 ↓ 0.0 0 1

Hash (cost=90,050.34..90,050.34 rows=1 width=8) (actual time=4,097.777..4,097.777 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
91. 0.001 4,097.776 ↓ 0.0 0 1

Subquery Scan on net_earnings_reported (cost=90,050.31..90,050.34 rows=1 width=8) (actual time=4,097.776..4,097.776 rows=0 loops=1)

92. 0.001 4,097.775 ↓ 0.0 0 1

GroupAggregate (cost=90,050.31..90,050.33 rows=1 width=8) (actual time=4,097.775..4,097.775 rows=0 loops=1)

  • Group Key: public_17.id
93. 0.003 4,097.774 ↓ 0.0 0 1

Sort (cost=90,050.31..90,050.31 rows=1 width=8) (actual time=4,097.774..4,097.774 rows=0 loops=1)

  • Sort Key: public_17.id
  • Sort Method: quicksort Memory: 25kB
94. 0.001 4,097.771 ↓ 0.0 0 1

Nested Loop (cost=86,804.59..90,050.30 rows=1 width=8) (actual time=4,097.771..4,097.771 rows=0 loops=1)

95. 92.644 4,097.770 ↓ 0.0 0 1

Nested Loop (cost=86,804.30..90,048.72 rows=5 width=12) (actual time=4,097.770..4,097.770 rows=0 loops=1)

  • Join Filter: (public_17.ends_at = public_19.period_ends_at)
  • Rows Removed by Join Filter: 495,778
96. 60.349 482.586 ↓ 56.6 58,709 1

Nested Loop (cost=86,803.86..88,080.15 rows=1,037 width=28) (actual time=162.185..482.586 rows=58,709 loops=1)

97. 64.901 246.110 ↓ 56.6 58,709 1

Hash Join (cost=86,803.44..87,606.08 rows=1,037 width=20) (actual time=162.158..246.110 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_5.provider_account_id = public_17.provider_account_id)
98. 19.147 19.147 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_5 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.000..19.147 rows=145,152 loops=1)

99. 36.610 162.062 ↓ 6.7 192,118 1

Hash (cost=86,445.91..86,445.91 rows=28,602 width=16) (actual time=162.062..162.062 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
100. 28.694 125.452 ↓ 6.7 192,118 1

Nested Loop (cost=862.23..86,445.91 rows=28,602 width=16) (actual time=23.241..125.452 rows=192,118 loops=1)

101. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on payday payday_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

102. 80.634 96.756 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_17 (cost=862.23..86,159.87 rows=28,602 width=20) (actual time=23.236..96.756 rows=192,118 loops=1)

  • Recheck Cond: (payment_expected_on = payday_5.payday)
  • Heap Blocks: exact=42,202
103. 16.122 16.122 ↓ 6.7 192,140 1

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.08 rows=28,602 width=0) (actual time=16.122..16.122 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_5.payday)
104. 176.127 176.127 ↑ 1.0 1 58,709

Index Scan using provider_accounts_pkey on provider_accounts public_18 (cost=0.43..0.46 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=58,709)

  • Index Cond: (id = public_17.provider_account_id)
105. 3,522.540 3,522.540 ↓ 8.0 8 58,709

Index Scan using index_archived_payroll_entries_on_external_id on archived_payroll_entries public_19 (cost=0.44..1.89 rows=1 width=31) (actual time=0.017..0.060 rows=8 loops=58,709)

  • Index Cond: ((external_id)::text = (public_18.external_id)::text)
  • Filter: (public_18.company_id = company_id)
  • Rows Removed by Filter: 35
106. 0.000 0.000 ↓ 0.0 0

Index Scan using payroll_journals_pkey on payroll_journals public_20 (cost=0.29..0.31 rows=1 width=4) (never executed)

  • Index Cond: (id = public_19.payroll_journal_id)
  • Filter: ((status)::text = ANY ('{presettlement,initial_presettlement,net_pay_only}'::text[]))
107. 2.024 437.941 ↓ 529.3 11,645 1

Hash (cost=88,261.91..88,261.91 rows=22 width=44) (actual time=437.941..437.941 rows=11,645 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 740kB
108. 1.193 435.917 ↓ 529.3 11,645 1

Subquery Scan on settlements_agg (cost=88,261.20..88,261.91 rows=22 width=44) (actual time=419.210..435.917 rows=11,645 loops=1)

109. 14.547 434.724 ↓ 529.3 11,645 1

GroupAggregate (cost=88,261.20..88,261.69 rows=22 width=44) (actual time=419.209..434.724 rows=11,645 loops=1)

  • Group Key: public_21.pay_period_id
110. 5.473 420.177 ↓ 542.7 11,940 1

Sort (cost=88,261.20..88,261.25 rows=22 width=22) (actual time=419.183..420.177 rows=11,940 loops=1)

  • Sort Key: public_21.pay_period_id
  • Sort Method: quicksort Memory: 1,317kB
111. 5.855 414.704 ↓ 542.7 11,940 1

Nested Loop (cost=86,804.44..88,260.70 rows=22 width=22) (actual time=147.983..414.704 rows=11,940 loops=1)

112. 0.000 373.029 ↓ 542.7 11,940 1

Nested Loop (cost=86,803.87..88,157.70 rows=22 width=20) (actual time=147.962..373.029 rows=11,940 loops=1)

113. 38.181 202.845 ↓ 56.6 58,709 1

Hash Join (cost=86,803.44..87,606.08 rows=1,037 width=4) (actual time=147.859..202.845 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_6.provider_account_id = public_23.provider_account_id)
114. 16.850 16.850 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_6 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.001..16.850 rows=145,152 loops=1)

115. 31.293 147.814 ↓ 6.7 192,118 1

Hash (cost=86,445.91..86,445.91 rows=28,602 width=8) (actual time=147.814..147.814 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
116. 23.427 116.521 ↓ 6.7 192,118 1

Nested Loop (cost=862.23..86,445.91 rows=28,602 width=8) (actual time=22.824..116.521 rows=192,118 loops=1)

117. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on payday payday_6 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

118. 77.419 93.092 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_23 (cost=862.23..86,159.87 rows=28,602 width=12) (actual time=22.819..93.092 rows=192,118 loops=1)

  • Recheck Cond: (payment_expected_on = payday_6.payday)
  • Heap Blocks: exact=42,202
119. 15.673 15.673 ↓ 6.7 192,140 1

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.08 rows=28,602 width=0) (actual time=15.673..15.673 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_6.payday)
120. 176.127 176.127 ↓ 0.0 0 58,709

Index Scan using index_settlements_on_pay_period_id on settlements public_21 (cost=0.44..0.52 rows=1 width=20) (actual time=0.002..0.003 rows=0 loops=58,709)

  • Index Cond: (pay_period_id = public_23.id)
  • Filter: ((total_amount_cents <> 0) AND ((status)::text = 'paid'::text))
  • Rows Removed by Filter: 1
121. 35.820 35.820 ↑ 1.0 1 11,940

Index Scan using payments_pkey on payments public_22 (cost=0.56..4.68 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=11,940)

  • Index Cond: (id = public_21.payment_id)
122. 2.424 995.843 ↑ 4.1 4,247 1

GroupAggregate (cost=1,272,150.62..1,272,544.84 rows=17,520 width=20) (actual time=992.940..995.843 rows=4,247 loops=1)

  • Group Key: public_24.provider_account_id
123. 1.897 993.418 ↑ 3.3 5,256 1

Sort (cost=1,272,150.62..1,272,194.42 rows=17,520 width=12) (actual time=992.924..993.418 rows=5,256 loops=1)

  • Sort Key: public_24.provider_account_id
  • Sort Method: quicksort Memory: 439kB
124. 23.148 991.521 ↑ 3.3 5,267 1

Hash Join (cost=1,267,679.49..1,270,915.75 rows=17,520 width=12) (actual time=956.857..991.521 rows=5,267 loops=1)

  • Hash Cond: (provider_accounts_list_7.provider_account_id = public_24.provider_account_id)
125. 13.450 13.450 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_7 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.001..13.450 rows=145,152 loops=1)

126. 22.552 954.923 ↑ 5.5 87,449 1

Hash (cost=1,261,636.49..1,261,636.49 rows=483,440 width=12) (actual time=954.923..954.923 rows=87,449 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 7,854kB
127. 7.243 932.371 ↑ 5.5 87,449 1

Gather (cost=1,000.56..1,261,636.49 rows=483,440 width=12) (actual time=3.120..932.371 rows=87,449 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
128. 3.284 925.128 ↑ 6.9 29,150 3 / 3

Nested Loop (cost=0.56..1,212,292.49 rows=201,433 width=12) (actual time=0.596..925.128 rows=29,150 loops=3)

129. 805.183 805.183 ↑ 7.2 29,165 3 / 3

Parallel Seq Scan on settlements public_25 (cost=0.00..382,764.66 rows=209,351 width=8) (actual time=0.577..805.183 rows=29,165 loops=3)

  • Filter: ((total_amount_cents <> 0) AND ((status)::text = 'unprocessed'::text))
  • Rows Removed by Filter: 7,106,237
130. 116.661 116.661 ↑ 1.0 1 87,496 / 3

Index Scan using pay_periods_pkey on pay_periods public_24 (cost=0.56..3.96 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=87,496)

  • Index Cond: (id = public_25.pay_period_id)
  • Filter: (payment_expected_on <= CURRENT_DATE)
  • Rows Removed by Filter: 0
131.          

SubPlan (for GroupAggregate)

132. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on payday (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

133. 34.794 1,836.186 ↓ 28.6 42,645 1

Unique (cost=91,382.50..91,401.13 rows=1,490 width=16) (actual time=1,663.274..1,836.186 rows=42,645 loops=1)

134. 103.890 1,801.392 ↓ 415.3 618,740 1

Group (cost=91,382.50..91,397.40 rows=1,490 width=16) (actual time=1,663.272..1,801.392 rows=618,740 loops=1)

  • Group Key: public_26.provider_account_id, public_27.created_at, public_27.total_amount_cents
135. 304.053 1,697.502 ↓ 415.3 618,740 1

Sort (cost=91,382.50..91,386.23 rows=1,490 width=16) (actual time=1,663.266..1,697.502 rows=618,740 loops=1)

  • Sort Key: public_26.provider_account_id, public_27.created_at DESC, public_27.total_amount_cents
  • Sort Method: quicksort Memory: 53,580kB
136. 111.643 1,393.449 ↓ 415.3 618,740 1

Nested Loop (cost=86,804.01..91,303.97 rows=1,490 width=16) (actual time=154.274..1,393.449 rows=618,740 loops=1)

137. 53.608 225.044 ↓ 56.6 58,709 1

Hash Join (cost=86,803.44..87,606.08 rows=1,037 width=16) (actual time=154.231..225.044 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_8.provider_account_id = public_26.provider_account_id)
138. 17.341 17.341 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_8 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.000..17.341 rows=145,152 loops=1)

139. 33.205 154.095 ↓ 6.7 192,118 1

Hash (cost=86,445.91..86,445.91 rows=28,602 width=12) (actual time=154.095..154.095 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
140. 26.733 120.890 ↓ 6.7 192,118 1

Nested Loop (cost=862.23..86,445.91 rows=28,602 width=12) (actual time=23.174..120.890 rows=192,118 loops=1)

141. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on payday payday_7 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.002 rows=1 loops=1)

142. 78.130 94.155 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_26 (cost=862.23..86,159.87 rows=28,602 width=16) (actual time=23.168..94.155 rows=192,118 loops=1)

  • Recheck Cond: (payment_expected_on = payday_7.payday)
  • Heap Blocks: exact=42,202
143. 16.025 16.025 ↓ 6.7 192,140 1

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.08 rows=28,602 width=0) (actual time=16.025..16.025 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_7.payday)
144. 1,056.762 1,056.762 ↓ 11.0 11 58,709

Index Scan using index_invoices_on_provider_account_id_and_period_ends_at on invoices public_27 (cost=0.57..3.56 rows=1 width=24) (actual time=0.006..0.018 rows=11 loops=58,709)

  • Index Cond: ((provider_account_id = public_26.provider_account_id) AND (period_ends_at = public_26.ends_at))
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 0
145. 6.242 2,154.393 ↓ 28.6 42,645 1

Hash (cost=91,498.28..91,498.28 rows=1,490 width=8) (actual time=2,154.393..2,154.393 rows=42,645 loops=1)

  • Buckets: 65,536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 2,178kB
146. 4.332 2,148.151 ↓ 28.6 42,645 1

Subquery Scan on greatest_gross_earnings (cost=91,475.93..91,498.28 rows=1,490 width=8) (actual time=2,069.536..2,148.151 rows=42,645 loops=1)

147. 39.961 2,143.819 ↓ 28.6 42,645 1

Unique (cost=91,475.93..91,483.38 rows=1,490 width=20) (actual time=2,069.534..2,143.819 rows=42,645 loops=1)

148. 281.567 2,103.858 ↓ 415.3 618,740 1

Sort (cost=91,475.93..91,479.66 rows=1,490 width=20) (actual time=2,069.532..2,103.858 rows=618,740 loops=1)

  • Sort Key: public_28.provider_account_id, public_29.total_amount_cents DESC
  • Sort Method: quicksort Memory: 72,916kB
149. 106.982 1,822.291 ↓ 415.3 618,740 1

Group (cost=91,382.50..91,397.40 rows=1,490 width=20) (actual time=1,670.801..1,822.291 rows=618,740 loops=1)

  • Group Key: public_28.provider_account_id, public_29.total_amount_cents, public_29.created_at
150. 324.921 1,715.309 ↓ 415.3 618,740 1

Sort (cost=91,382.50..91,386.23 rows=1,490 width=16) (actual time=1,670.795..1,715.309 rows=618,740 loops=1)

  • Sort Key: public_28.provider_account_id, public_29.total_amount_cents, public_29.created_at
  • Sort Method: quicksort Memory: 53,580kB
151. 106.445 1,390.388 ↓ 415.3 618,740 1

Nested Loop (cost=86,804.01..91,303.97 rows=1,490 width=16) (actual time=156.178..1,390.388 rows=618,740 loops=1)

152. 53.490 227.181 ↓ 56.6 58,709 1

Hash Join (cost=86,803.44..87,606.08 rows=1,037 width=16) (actual time=156.135..227.181 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_9.provider_account_id = public_28.provider_account_id)
153. 17.606 17.606 ↓ 5.0 145,152 1

CTE Scan on provider_accounts_list provider_accounts_list_9 (cost=0.00..576.20 rows=28,810 width=4) (actual time=0.001..17.606 rows=145,152 loops=1)

154. 33.232 156.085 ↓ 6.7 192,118 1

Hash (cost=86,445.91..86,445.91 rows=28,602 width=12) (actual time=156.085..156.085 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
155. 28.584 122.853 ↓ 6.7 192,118 1

Nested Loop (cost=862.23..86,445.91 rows=28,602 width=12) (actual time=21.947..122.853 rows=192,118 loops=1)

156. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on payday payday_8 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

157. 79.321 94.267 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_28 (cost=862.23..86,159.87 rows=28,602 width=16) (actual time=21.941..94.267 rows=192,118 loops=1)

  • Recheck Cond: (payment_expected_on = payday_8.payday)
  • Heap Blocks: exact=42,202
158. 14.946 14.946 ↓ 6.7 192,140 1

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.08 rows=28,602 width=0) (actual time=14.946..14.946 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_8.payday)
159. 1,056.762 1,056.762 ↓ 11.0 11 58,709

Index Scan using index_invoices_on_provider_account_id_and_period_ends_at on invoices public_29 (cost=0.57..3.56 rows=1 width=24) (actual time=0.006..0.018 rows=11 loops=58,709)

  • Index Cond: ((provider_account_id = public_28.provider_account_id) AND (period_ends_at = public_28.ends_at))
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 0
160. 0.004 40,969.110 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..142,996.10 rows=1 width=784) (actual time=40,969.110..40,969.110 rows=1 loops=1)

161. 33.242 40,969.106 ↑ 1.0 1 1

GroupAggregate (cost=0.00..142,996.07 rows=1 width=520) (actual time=40,969.106..40,969.106 rows=1 loops=1)

  • Group Key: 'Count'::character varying, NULL::text, NULL::text, NULL::text, NULL::text
162. 40,935.864 40,935.864 ↑ 56.8 23,978 1

CTE Scan on data (cost=0.00..27,237.34 rows=1,361,867 width=368) (actual time=40,702.780..40,935.864 rows=23,978 loops=1)

163. 0.001 14.876 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..91,926.12 rows=1 width=784) (actual time=14.876..14.876 rows=1 loops=1)

164. 7.737 14.875 ↑ 1.0 1 1

GroupAggregate (cost=0.00..91,926.10 rows=1 width=776) (actual time=14.875..14.875 rows=1 loops=1)

  • Group Key: 'Total $$'::character varying, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::text, NULL::text
165. 7.138 7.138 ↑ 56.8 23,978 1

CTE Scan on data data_1 (cost=0.00..27,237.34 rows=1,361,867 width=300) (actual time=0.002..7.138 rows=23,978 loops=1)

166. 4.018 129.132 ↑ 56.8 23,978 1

Subquery Scan on *SELECT* 3 (cost=738,269.74..775,721.08 rows=1,361,867 width=784) (actual time=116.533..129.132 rows=23,978 loops=1)

167. 7.044 125.114 ↑ 56.8 23,978 1

Subquery Scan on t1 (cost=738,269.74..755,293.07 rows=1,361,867 width=776) (actual time=116.531..125.114 rows=23,978 loops=1)

168. 52.490 118.070 ↑ 56.8 23,978 1

Sort (cost=738,269.74..741,674.40 rows=1,361,867 width=784) (actual time=116.529..118.070 rows=23,978 loops=1)

  • Sort Key: data_2.pre_settlements DESC, data_2.exclusion, data_2.user_id, data_2.provider_account_id
  • Sort Method: quicksort Memory: 7,204kB
169. 65.580 65.580 ↑ 56.8 23,978 1

CTE Scan on data data_2 (cost=0.00..129,377.36 rows=1,361,867 width=784) (actual time=0.020..65.580 rows=23,978 loops=1)

Planning time : 23.434 ms
Execution time : 41,227.230 ms