explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NDVF : Optimization for: plan #WOQz (by PP)

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.433 206,066.339 ↓ 81.3 23,813 1

Append (cost=2,443,731.20..2,443,830.57 rows=293 width=792) (actual time=205,909.590..206,066.339 rows=23,813 loops=1)

2.          

CTE provider_accounts_list

3. 25.776 313.543 ↓ 4.9 145,807 1

Nested Loop (cost=1,085.11..68,577.90 rows=29,586 width=4) (actual time=3.253..313.543 rows=145,807 loops=1)

4. 0.028 0.028 ↑ 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.026..0.028 rows=1 loops=1)

  • Index Cond: (id = 202)
  • Heap Fetches: 1
5. 0.000 287.739 ↓ 4.9 145,807 1

Gather (cost=1,084.84..68,273.75 rows=29,586 width=8) (actual time=3.226..287.739 rows=145,807 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 87.482 365.913 ↓ 3.9 48,602 3 / 3

Hash Join (cost=84.84..64,315.15 rows=12,328 width=8) (actual time=0.177..365.913 rows=48,602 loops=3)

  • Hash Cond: (public.company_id = public_1.id)
7. 278.376 278.376 ↑ 1.3 690,390 3 / 3

Parallel Seq Scan on provider_accounts public (cost=0.00..61,931.94 rows=873,194 width=12) (actual time=0.006..278.376 rows=690,390 loops=3)

8. 0.006 0.055 ↑ 1.0 22 3 / 3

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

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

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

  • Recheck Cond: (provider_id = 202)
  • Heap Blocks: exact=18
10. 0.020 0.020 ↑ 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.020..0.020 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.000..0.001 rows=1 loops=1)

13.          

CTE data

14. 17.750 205,842.453 ↓ 81.8 23,811 1

Merge Right Join (cost=2,374,459.95..2,375,153.29 rows=291 width=247) (actual time=205,666.372..205,842.453 rows=23,811 loops=1)

  • Merge Cond: (public_9.id = public_7.id)
15. 61.751 163,008.448 ↓ 28.7 42,645 1

Unique (cost=92,373.34..92,388.22 rows=1,488 width=24) (actual time=162,913.672..163,008.448 rows=42,645 loops=1)

16. 163.027 162,946.697 ↓ 415.8 618,740 1

Sort (cost=92,373.34..92,377.06 rows=1,488 width=24) (actual time=162,913.670..162,946.697 rows=618,740 loops=1)

  • Sort Key: public_9.id, (max(public_10.total_amount_cents) OVER (?)), (first_value(public_10.total_amount_cents) OVER (?))
  • Sort Method: quicksort Memory: 72,916kB
17. 226.658 162,783.670 ↓ 415.8 618,740 1

WindowAgg (cost=92,265.17..92,294.93 rows=1,488 width=24) (actual time=162,525.615..162,783.670 rows=618,740 loops=1)

18. 288.402 162,557.012 ↓ 415.8 618,740 1

Sort (cost=92,265.17..92,268.89 rows=1,488 width=20) (actual time=162,525.604..162,557.012 rows=618,740 loops=1)

  • Sort Key: public_9.id, public_10.created_at DESC
  • Sort Method: quicksort Memory: 72,916kB
19. 253.230 162,268.610 ↓ 415.8 618,740 1

WindowAgg (cost=92,149.56..92,186.76 rows=1,488 width=20) (actual time=161,864.297..162,268.610 rows=618,740 loops=1)

20. 103.682 162,015.380 ↓ 415.8 618,740 1

Group (cost=92,149.56..92,164.44 rows=1,488 width=16) (actual time=161,864.284..162,015.380 rows=618,740 loops=1)

  • Group Key: public_9.id, public_10.total_amount_cents, public_10.created_at
21. 462.716 161,911.698 ↓ 415.8 618,740 1

Sort (cost=92,149.56..92,153.28 rows=1,488 width=16) (actual time=161,864.281..161,911.698 rows=618,740 loops=1)

  • Sort Key: public_9.id, public_10.total_amount_cents, public_10.created_at
  • Sort Method: quicksort Memory: 53,580kB
22. 206.713 161,448.982 ↓ 415.8 618,740 1

Nested Loop (cost=87,373.73..92,071.15 rows=1,488 width=16) (actual time=264.370..161,448.982 rows=618,740 loops=1)

23. 135.869 790.572 ↓ 55.3 58,709 1

Hash Join (cost=87,373.16..88,160.41 rows=1,062 width=20) (actual time=260.730..790.572 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_1.provider_account_id = public_9.provider_account_id)
24. 397.389 397.389 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list provider_accounts_list_1 (cost=0.00..591.72 rows=29,586 width=4) (actual time=3.255..397.389 rows=145,807 loops=1)

25. 39.294 257.314 ↓ 6.5 192,118 1

Hash (cost=87,001.99..87,001.99 rows=29,694 width=16) (actual time=257.314..257.314 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
26. 30.178 218.020 ↓ 6.5 192,118 1

Nested Loop (cost=0.56..87,001.99 rows=29,694 width=16) (actual time=0.077..218.020 rows=192,118 loops=1)

27. 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)

28. 187.840 187.840 ↓ 6.5 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_9 (cost=0.56..86,705.03 rows=29,694 width=20) (actual time=0.075..187.840 rows=192,118 loops=1)

  • Index Cond: (payment_expected_on = payday_2.payday)
29. 160,451.697 160,451.697 ↓ 11.0 11 58,709

Index Scan using index_invoices_on_provider_account_id_and_period_ends_at on invoices public_10 (cost=0.57..3.67 rows=1 width=24) (actual time=0.972..2.733 rows=11 loops=58,709)

  • Index Cond: ((provider_account_id = public_9.provider_account_id) AND (period_ends_at = public_9.ends_at))
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 0
30. 8.942 42,816.255 ↓ 81.8 23,811 1

Materialize (cost=2,282,086.61..2,282,745.01 rows=291 width=231) (actual time=42,752.693..42,816.255 rows=23,811 loops=1)

31. 8.760 42,807.313 ↓ 81.8 23,811 1

Merge Left Join (cost=2,282,086.61..2,282,744.28 rows=291 width=231) (actual time=42,752.689..42,807.313 rows=23,811 loops=1)

  • Merge Cond: (public_7.id = public_25.pay_period_id)
32. 4.408 42,204.198 ↓ 81.8 23,811 1

Merge Left Join (cost=2,193,259.73..2,193,915.90 rows=291 width=191) (actual time=42,181.388..42,204.198 rows=23,811 loops=1)

  • Merge Cond: (public_7.id = public_21.id)
33. 4.956 37,554.223 ↓ 81.8 23,811 1

Merge Left Join (cost=2,102,573.58..2,103,228.97 rows=291 width=187) (actual time=37,535.819..37,554.223 rows=23,811 loops=1)

  • Merge Cond: (public_7.id = public_19.noteable_id)
34. 9.226 37,018.255 ↓ 81.8 23,811 1

Merge Left Join (cost=2,013,878.66..2,014,533.11 rows=291 width=155) (actual time=37,005.449..37,018.255 rows=23,811 loops=1)

  • Merge Cond: (public_7.id = public_17.id)
35. 18.521 35,869.814 ↓ 81.8 23,811 1

Sort (cost=670,655.81..670,656.53 rows=291 width=139) (actual time=35,866.269..35,869.814 rows=23,811 loops=1)

  • Sort Key: public_7.id
  • Sort Method: quicksort Memory: 5,709kB
36. 15.126 35,851.293 ↓ 81.8 23,811 1

Merge Left Join (cost=657,170.23..670,643.90 rows=291 width=139) (actual time=33,338.439..35,851.293 rows=23,811 loops=1)

  • Merge Cond: (public_6.id = public_15.user_id)
37. 23.375 4,045.938 ↓ 81.8 23,811 1

Sort (cost=279,855.76..279,856.49 rows=291 width=135) (actual time=4,042.047..4,045.938 rows=23,811 loops=1)

  • Sort Key: public_6.id
  • Sort Method: quicksort Memory: 5,342kB
38. 25.220 4,022.563 ↓ 81.8 23,811 1

Hash Left Join (cost=269,423.86..279,843.86 rows=291 width=135) (actual time=2,932.482..4,022.563 rows=23,811 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,898
39. 23.985 3,522.628 ↓ 146.8 58,709 1

Hash Left Join (cost=180,746.33..191,165.27 rows=400 width=127) (actual time=2,457.753..3,522.628 rows=58,709 loops=1)

  • Hash Cond: (public_7.id = advance_stats.pay_period_id)
40. 71.167 1,306.028 ↓ 146.8 58,709 1

Nested Loop (cost=87,461.11..97,879.01 rows=400 width=111) (actual time=265.129..1,306.028 rows=58,709 loops=1)

41. 22.461 1,058.734 ↓ 146.8 58,709 1

Nested Loop (cost=87,460.68..96,874.75 rows=400 width=104) (actual time=265.111..1,058.734 rows=58,709 loops=1)

42. 19.070 742.728 ↓ 146.8 58,709 1

Hash Join (cost=87,458.73..94,489.43 rows=400 width=90) (actual time=265.088..742.728 rows=58,709 loops=1)

  • Hash Cond: (public_3.provider_id = public_4.id)
43. 36.574 723.206 ↓ 146.8 58,709 1

Nested Loop (cost=87,373.87..94,403.51 rows=400 width=77) (actual time=264.620..723.206 rows=58,709 loops=1)

44. 50.324 569.214 ↓ 140.1 58,709 1

Nested Loop (cost=87,373.59..93,459.00 rows=419 width=66) (actual time=264.599..569.214 rows=58,709 loops=1)

  • Join Filter: (provider_accounts_list.provider_account_id = public_3.id)
45. 59.927 342.763 ↓ 55.3 58,709 1

Hash Join (cost=87,373.16..90,823.15 rows=1,062 width=16) (actual time=264.571..342.763 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list.provider_account_id = public_7.provider_account_id)
46. 18.404 18.404 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.001..18.404 rows=145,807 loops=1)

47. 34.556 264.432 ↓ 6.5 192,118 1

Hash (cost=87,001.99..87,001.99 rows=29,694 width=12) (actual time=264.432..264.432 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 10,304kB
48. 40.959 229.876 ↓ 6.5 192,118 1

Nested Loop (cost=0.56..87,001.99 rows=29,694 width=12) (actual time=0.051..229.876 rows=192,118 loops=1)

49. 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)

50. 188.915 188.915 ↓ 6.5 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_7 (cost=0.56..86,705.03 rows=29,694 width=12) (actual time=0.046..188.915 rows=192,118 loops=1)

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

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

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

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

  • Index Cond: (id = public_3.company_id)
53. 0.076 0.452 ↓ 1.0 397 1

Hash (cost=79.94..79.94 rows=394 width=21) (actual time=0.452..0.452 rows=397 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
54. 0.376 0.376 ↓ 1.0 397 1

Seq Scan on providers public_4 (cost=0.00..79.94 rows=394 width=21) (actual time=0.011..0.376 rows=397 loops=1)

55. 117.418 293.545 ↑ 1.0 1 58,709

Bitmap Heap Scan on users public_6 (cost=1.95..5.96 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=58,709)

  • Recheck Cond: (id = public_3.user_id)
  • Heap Blocks: exact=59,381
56. 176.127 176.127 ↑ 1.0 1 58,709

Bitmap Index Scan on users_pkey (cost=0.00..1.95 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=58,709)

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

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

  • Index Cond: (id = public_3.profile_id)
58. 0.997 2,192.615 ↓ 8.5 7,049 1

Hash (cost=93,274.79..93,274.79 rows=834 width=20) (actual time=2,192.615..2,192.615 rows=7,049 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 367kB
59. 0.637 2,191.618 ↓ 8.5 7,049 1

Subquery Scan on advance_stats (cost=93,243.51..93,274.79 rows=834 width=20) (actual time=2,184.340..2,191.618 rows=7,049 loops=1)

60. 5.276 2,190.981 ↓ 8.5 7,049 1

GroupAggregate (cost=93,243.51..93,266.45 rows=834 width=20) (actual time=2,184.339..2,190.981 rows=7,049 loops=1)

  • Group Key: public_11.pay_period_id
61. 8.831 2,185.705 ↓ 28.0 23,376 1

Sort (cost=93,243.51..93,245.60 rows=834 width=15) (actual time=2,184.331..2,185.705 rows=23,376 loops=1)

  • Sort Key: public_11.pay_period_id
  • Sort Method: quicksort Memory: 1,865kB
62. 29.232 2,176.874 ↓ 28.0 23,376 1

Nested Loop (cost=87,373.73..93,203.05 rows=834 width=15) (actual time=254.056..2,176.874 rows=23,376 loops=1)

63. 55.946 327.663 ↓ 55.3 58,709 1

Hash Join (cost=87,373.16..88,160.41 rows=1,062 width=4) (actual time=253.997..327.663 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_2.provider_account_id = public_12.provider_account_id)
64. 17.744 17.744 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list provider_accounts_list_2 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.001..17.744 rows=145,807 loops=1)

65. 34.027 253.973 ↓ 6.5 192,118 1

Hash (cost=87,001.99..87,001.99 rows=29,694 width=8) (actual time=253.973..253.973 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
66. 23.112 219.946 ↓ 6.5 192,118 1

Nested Loop (cost=0.56..87,001.99 rows=29,694 width=8) (actual time=0.048..219.946 rows=192,118 loops=1)

67. 0.001 0.001 ↑ 1.0 1 1

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

68. 196.833 196.833 ↓ 6.5 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_12 (cost=0.56..86,705.03 rows=29,694 width=12) (actual time=0.045..196.833 rows=192,118 loops=1)

  • Index Cond: (payment_expected_on = payday_3.payday)
69. 1,819.979 1,819.979 ↓ 0.0 0 58,709

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

  • Index Cond: (pay_period_id = public_12.id)
  • Filter: ((status)::text = 'paid'::text)
  • Rows Removed by Filter: 22
70. 1.640 474.715 ↓ 56.2 11,625 1

Hash (cost=88,674.95..88,674.95 rows=207 width=12) (actual time=474.715..474.715 rows=11,625 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 628kB
71. 1.036 473.075 ↓ 56.2 11,625 1

Subquery Scan on provider_payments_list (cost=88,669.25..88,674.95 rows=207 width=12) (actual time=468.413..473.075 rows=11,625 loops=1)

72. 2.870 472.039 ↓ 56.2 11,625 1

GroupAggregate (cost=88,669.25..88,672.88 rows=207 width=12) (actual time=468.412..472.039 rows=11,625 loops=1)

  • Group Key: public_14.id
73. 4.390 469.169 ↓ 58.5 12,106 1

Sort (cost=88,669.25..88,669.77 rows=207 width=8) (actual time=468.407..469.169 rows=12,106 loops=1)

  • Sort Key: public_14.id
  • Sort Method: quicksort Memory: 952kB
74. 7.675 464.779 ↓ 58.5 12,106 1

Nested Loop (cost=87,373.59..88,661.29 rows=207 width=8) (actual time=278.822..464.779 rows=12,106 loops=1)

75. 45.306 339.686 ↓ 55.3 58,709 1

Hash Join (cost=87,373.16..88,160.41 rows=1,062 width=4) (actual time=278.792..339.686 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_3.provider_account_id = public_14.provider_account_id)
76. 15.629 15.629 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list provider_accounts_list_3 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.001..15.629 rows=145,807 loops=1)

77. 38.684 278.751 ↓ 6.5 192,118 1

Hash (cost=87,001.99..87,001.99 rows=29,694 width=8) (actual time=278.751..278.751 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
78. 23.686 240.067 ↓ 6.5 192,118 1

Nested Loop (cost=0.56..87,001.99 rows=29,694 width=8) (actual time=0.048..240.067 rows=192,118 loops=1)

79. 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)

80. 216.379 216.379 ↓ 6.5 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_14 (cost=0.56..86,705.03 rows=29,694 width=12) (actual time=0.045..216.379 rows=192,118 loops=1)

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

Index Scan using index_provider_payments_on_pay_period_id on provider_payments public_13 (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_14.id)
82. 799.560 31,790.229 ↑ 12.3 51,329 1

Unique (cost=377,314.47..382,900.70 rows=630,836 width=20) (actual time=29,296.359..31,790.229 rows=51,329 loops=1)

83. 8,519.281 30,990.669 ↓ 11.8 13,188,509 1

Sort (cost=377,314.47..380,107.58 rows=1,117,246 width=20) (actual time=29,296.357..30,990.669 rows=13,188,509 loops=1)

  • Sort Key: public_15.user_id, public_15.created_at DESC
  • Sort Method: external merge Disk: 439,840kB
84. 2,647.394 22,471.388 ↓ 11.8 13,219,488 1

Nested Loop (cost=100,353.05..265,078.64 rows=1,117,246 width=20) (actual time=887.338..22,471.388 rows=13,219,488 loops=1)

85. 105.126 1,014.891 ↓ 4.9 145,807 1

Hash Join (cost=100,352.49..101,021.87 rows=29,586 width=4) (actual time=887.262..1,014.891 rows=145,807 loops=1)

  • Hash Cond: (provider_accounts_list_4.provider_account_id = public_16.id)
86. 29.938 29.938 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list provider_accounts_list_4 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.001..29.938 rows=145,807 loops=1)

87. 473.807 879.827 ↑ 1.0 2,071,171 1

Hash (cost=74,156.66..74,156.66 rows=2,095,666 width=8) (actual time=879.827..879.827 rows=2,071,171 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 97,290kB
88. 406.020 406.020 ↑ 1.0 2,071,171 1

Seq Scan on provider_accounts public_16 (cost=0.00..74,156.66 rows=2,095,666 width=8) (actual time=0.011..406.020 rows=2,071,171 loops=1)

89. 18,809.103 18,809.103 ↑ 1.3 91 145,807

Index Scan using index_available_balance_changes_on_user_id on available_balance_changes public_15 (cost=0.57..4.40 rows=115 width=20) (actual time=0.011..0.129 rows=91 loops=145,807)

  • Index Cond: (user_id = public_16.user_id)
90. 1.725 1,139.215 ↑ 3.9 4,753 1

GroupAggregate (cost=1,343,222.85..1,343,642.63 rows=18,656 width=20) (actual time=1,137.192..1,139.215 rows=4,753 loops=1)

  • Group Key: public_17.id
91. 1.523 1,137.489 ↑ 3.9 4,754 1

Sort (cost=1,343,222.85..1,343,269.49 rows=18,656 width=12) (actual time=1,137.175..1,137.489 rows=4,754 loops=1)

  • Sort Key: public_17.id
  • Sort Method: quicksort Memory: 429kB
92. 24.984 1,135.966 ↑ 3.7 5,038 1

Hash Join (cost=1,339,050.16..1,341,899.46 rows=18,656 width=12) (actual time=1,100.185..1,135.966 rows=5,038 loops=1)

  • Hash Cond: (provider_accounts_list_5.provider_account_id = public_17.provider_account_id)
93. 13.201 13.201 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list provider_accounts_list_5 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.002..13.201 rows=145,807 loops=1)

94. 22.998 1,097.781 ↑ 6.0 87,591 1

Hash (cost=1,332,532.14..1,332,532.14 rows=521,441 width=16) (actual time=1,097.781..1,097.781 rows=87,591 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 8,202kB
95. 11.578 1,074.783 ↑ 6.0 87,591 1

Gather (cost=1,000.56..1,332,532.14 rows=521,441 width=16) (actual time=4.251..1,074.783 rows=87,591 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
96. 20.762 1,063.205 ↑ 7.4 29,197 3 / 3

Nested Loop (cost=0.56..1,279,388.04 rows=217,267 width=16) (actual time=0.652..1,063.205 rows=29,197 loops=3)

97. 925.615 925.615 ↑ 7.7 29,207 3 / 3

Parallel Seq Scan on settlements public_18 (cost=0.00..387,643.99 rows=225,930 width=8) (actual time=0.628..925.615 rows=29,207 loops=3)

  • Filter: ((total_amount_cents <> 0) AND ((status)::text = 'unprocessed'::text))
  • Rows Removed by Filter: 7,329,656
98. 116.828 116.828 ↑ 1.0 1 87,621 / 3

Index Scan using pay_periods_pkey on pay_periods public_17 (cost=0.56..3.95 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=87,621)

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

SubPlan (for GroupAggregate)

100. 0.001 0.001 ↑ 1.0 1 1

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

101. 0.629 531.012 ↓ 39.2 235 1

GroupAggregate (cost=88,694.92..88,695.05 rows=6 width=36) (actual time=530.366..531.012 rows=235 loops=1)

  • Group Key: public_19.noteable_id
102. 0.254 530.383 ↓ 39.2 235 1

Sort (cost=88,694.92..88,694.93 rows=6 width=88) (actual time=530.333..530.383 rows=235 loops=1)

  • Sort Key: public_19.noteable_id
  • Sort Method: quicksort Memory: 84kB
103. 33.899 530.129 ↓ 39.2 235 1

Nested Loop (cost=87,373.60..88,694.84 rows=6 width=88) (actual time=256.737..530.129 rows=235 loops=1)

104. 47.178 320.103 ↓ 55.3 58,709 1

Hash Join (cost=87,373.16..88,160.41 rows=1,062 width=4) (actual time=256.388..320.103 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_6.provider_account_id = public_20.provider_account_id)
105. 16.586 16.586 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list provider_accounts_list_6 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.001..16.586 rows=145,807 loops=1)

106. 34.333 256.339 ↓ 6.5 192,118 1

Hash (cost=87,001.99..87,001.99 rows=29,694 width=8) (actual time=256.339..256.339 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
107. 24.045 222.006 ↓ 6.5 192,118 1

Nested Loop (cost=0.56..87,001.99 rows=29,694 width=8) (actual time=0.050..222.006 rows=192,118 loops=1)

108. 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.000..0.002 rows=1 loops=1)

109. 197.959 197.959 ↓ 6.5 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_20 (cost=0.56..86,705.03 rows=29,694 width=12) (actual time=0.047..197.959 rows=192,118 loops=1)

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

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

  • Index Cond: (((noteable_type)::text = 'PayPeriod'::text) AND (noteable_id = public_20.id))
111. 0.002 4,645.567 ↓ 0.0 0 1

GroupAggregate (cost=90,686.16..90,686.18 rows=1 width=8) (actual time=4,645.567..4,645.567 rows=0 loops=1)

  • Group Key: public_21.id
112. 0.003 4,645.565 ↓ 0.0 0 1

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

  • Sort Key: public_21.id
  • Sort Method: quicksort Memory: 25kB
113. 0.001 4,645.562 ↓ 0.0 0 1

Nested Loop (cost=87,374.32..90,686.15 rows=1 width=8) (actual time=4,645.562..4,645.562 rows=0 loops=1)

114. 46.745 4,645.561 ↓ 0.0 0 1

Nested Loop (cost=87,374.03..90,684.26 rows=6 width=12) (actual time=4,645.561..4,645.561 rows=0 loops=1)

  • Join Filter: (public_21.ends_at = public_23.period_ends_at)
  • Rows Removed by Join Filter: 534,714
115. 60.939 606.604 ↓ 55.3 58,709 1

Nested Loop (cost=87,373.59..88,645.34 rows=1,062 width=28) (actual time=281.048..606.604 rows=58,709 loops=1)

116. 68.611 369.538 ↓ 55.3 58,709 1

Hash Join (cost=87,373.16..88,160.41 rows=1,062 width=20) (actual time=281.021..369.538 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_7.provider_account_id = public_21.provider_account_id)
117. 20.042 20.042 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list provider_accounts_list_7 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.001..20.042 rows=145,807 loops=1)

118. 40.537 280.885 ↓ 6.5 192,118 1

Hash (cost=87,001.99..87,001.99 rows=29,694 width=16) (actual time=280.885..280.885 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
119. 30.568 240.348 ↓ 6.5 192,118 1

Nested Loop (cost=0.56..87,001.99 rows=29,694 width=16) (actual time=0.046..240.348 rows=192,118 loops=1)

120. 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)

121. 209.778 209.778 ↓ 6.5 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_21 (cost=0.56..86,705.03 rows=29,694 width=20) (actual time=0.043..209.778 rows=192,118 loops=1)

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

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

  • Index Cond: (id = public_21.provider_account_id)
123. 3,992.212 3,992.212 ↓ 9.0 9 58,709

Index Scan using index_archived_payroll_entries_on_external_id on archived_payroll_entries public_23 (cost=0.44..1.91 rows=1 width=31) (actual time=0.018..0.068 rows=9 loops=58,709)

  • Index Cond: ((external_id)::text = (public_22.external_id)::text)
  • Filter: (public_22.company_id = company_id)
  • Rows Removed by Filter: 36
124. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = public_23.payroll_journal_id)
  • Filter: ((status)::text = ANY ('{presettlement,initial_presettlement,net_pay_only}'::text[]))
125. 21.500 594.355 ↓ 530.0 11,660 1

GroupAggregate (cost=88,826.87..88,827.37 rows=22 width=44) (actual time=571.297..594.355 rows=11,660 loops=1)

  • Group Key: public_25.pay_period_id
126. 6.084 572.855 ↓ 544.0 11,968 1

Sort (cost=88,826.87..88,826.93 rows=22 width=23) (actual time=571.268..572.855 rows=11,968 loops=1)

  • Sort Key: public_25.pay_period_id
  • Sort Method: quicksort Memory: 1,320kB
127. 10.649 566.771 ↓ 544.0 11,968 1

Nested Loop (cost=87,374.16..88,826.38 rows=22 width=23) (actual time=265.601..566.771 rows=11,968 loops=1)

128. 13.611 520.218 ↓ 544.0 11,968 1

Nested Loop (cost=87,373.60..88,723.00 rows=22 width=20) (actual time=265.581..520.218 rows=11,968 loops=1)

129. 48.442 330.480 ↓ 55.3 58,709 1

Hash Join (cost=87,373.16..88,160.41 rows=1,062 width=4) (actual time=265.546..330.480 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_8.provider_account_id = public_27.provider_account_id)
130. 16.538 16.538 ↓ 4.9 145,807 1

CTE Scan on provider_accounts_list provider_accounts_list_8 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.001..16.538 rows=145,807 loops=1)

131. 36.757 265.500 ↓ 6.5 192,118 1

Hash (cost=87,001.99..87,001.99 rows=29,694 width=8) (actual time=265.500..265.500 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
132. 22.699 228.743 ↓ 6.5 192,118 1

Nested Loop (cost=0.56..87,001.99 rows=29,694 width=8) (actual time=0.041..228.743 rows=192,118 loops=1)

133. 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.001..0.002 rows=1 loops=1)

134. 206.042 206.042 ↓ 6.5 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_27 (cost=0.56..86,705.03 rows=29,694 width=12) (actual time=0.038..206.042 rows=192,118 loops=1)

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

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

  • Index Cond: (pay_period_id = public_27.id)
  • Filter: ((total_amount_cents <> 0) AND ((status)::text = 'paid'::text))
  • Rows Removed by Filter: 1
136. 35.904 35.904 ↑ 1.0 1 11,968

Index Scan using payments_pkey on payments public_26 (cost=0.56..4.70 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=11,968)

  • Index Cond: (id = public_25.payment_id)
137. 0.004 205,909.590 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..31.35 rows=1 width=792) (actual time=205,909.590..205,909.590 rows=1 loops=1)

138. 39.559 205,909.586 ↑ 1.0 1 1

GroupAggregate (cost=0.00..31.31 rows=1 width=528) (actual time=205,909.585..205,909.586 rows=1 loops=1)

  • Group Key: 'Count'::character varying, NULL::text, NULL::text, NULL::text, NULL::text
139. 205,870.027 205,870.027 ↓ 81.8 23,811 1

CTE Scan on data (cost=0.00..5.82 rows=291 width=372) (actual time=205,666.375..205,870.027 rows=23,811 loops=1)

140. 0.002 18.333 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..19.74 rows=1 width=792) (actual time=18.332..18.333 rows=1 loops=1)

141. 9.391 18.331 ↑ 1.0 1 1

GroupAggregate (cost=0.00..19.72 rows=1 width=780) (actual time=18.330..18.331 rows=1 loops=1)

  • Group Key: 'Total $$'::character varying, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::text, NULL::text
142. 8.940 8.940 ↓ 81.8 23,811 1

CTE Scan on data data_1 (cost=0.00..5.82 rows=291 width=300) (actual time=0.002..8.940 rows=23,811 loops=1)

143. 4.107 136.983 ↓ 81.8 23,811 1

Subquery Scan on *SELECT* 3 (cost=39.55..48.28 rows=291 width=792) (actual time=122.664..136.983 rows=23,811 loops=1)

144. 7.993 132.876 ↓ 81.8 23,811 1

Subquery Scan on t1 (cost=39.55..43.19 rows=291 width=780) (actual time=122.661..132.876 rows=23,811 loops=1)

145. 54.852 124.883 ↓ 81.8 23,811 1

Sort (cost=39.55..40.28 rows=291 width=788) (actual time=122.659..124.883 rows=23,811 loops=1)

  • Sort Key: data_2.pre_settlements DESC, data_2.exclusion, data_2.user_id, data_2.provider_account_id, data_2.pay_period_id
  • Sort Method: quicksort Memory: 7,178kB
146. 70.031 70.031 ↓ 81.8 23,811 1

CTE Scan on data data_2 (cost=0.00..27.64 rows=291 width=788) (actual time=0.163..70.031 rows=23,811 loops=1)

Planning time : 47.769 ms
Execution time : 206,161.819 ms