explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MqFc : new

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.524 52,378.519 ↓ 86.0 23,813 1

Append (cost=2,406,933.76..2,407,027.57 rows=277 width=792) (actual time=52,233.357..52,378.519 rows=23,813 loops=1)

2.          

CTE provider_accounts_list

3. 14.351 110.451 ↓ 4.9 145,814 1

Nested Loop (cost=1,085.11..68,577.90 rows=29,586 width=4) (actual time=1.787..110.451 rows=145,814 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.013..0.015 rows=1 loops=1)

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

Gather (cost=1,084.84..68,273.75 rows=29,586 width=8) (actual time=1.773..96.085 rows=145,814 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 53.492 249.765 ↓ 3.9 48,605 3 / 3

Hash Join (cost=84.84..64,315.15 rows=12,328 width=8) (actual time=0.132..249.765 rows=48,605 loops=3)

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

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

8. 0.005 0.042 ↑ 1.0 22 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.024 0.037 ↑ 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.037 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.000..0.001 rows=1 loops=1)

13.          

CTE data

14. 16.740 52,169.756 ↓ 86.6 23,811 1

Merge Right Join (cost=2,338,224.94..2,338,355.86 rows=275 width=247) (actual time=51,991.507..52,169.756 rows=23,811 loops=1)

  • Merge Cond: (public_9.id = public_7.id)
15. 59.421 3,476.271 ↓ 30.3 42,645 1

Unique (cost=87,659.96..87,674.04 rows=1,408 width=36) (actual time=3,373.440..3,476.271 rows=42,645 loops=1)

16. 281.835 3,416.850 ↓ 439.4 618,740 1

Sort (cost=87,659.96..87,663.48 rows=1,408 width=36) (actual time=3,373.438..3,416.850 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. 259.094 3,135.015 ↓ 439.4 618,740 1

WindowAgg (cost=87,554.64..87,586.32 rows=1,408 width=36) (actual time=2,840.372..3,135.015 rows=618,740 loops=1)

18. 322.393 2,875.921 ↓ 439.4 618,740 1

Sort (cost=87,554.64..87,558.16 rows=1,408 width=32) (actual time=2,840.364..2,875.921 rows=618,740 loops=1)

  • Sort Key: public_10.provider_account_id, public_10.period_ends_at, public_10.created_at DESC
  • Sort Method: quicksort Memory: 72,916kB
19. 255.900 2,553.528 ↓ 439.4 618,740 1

WindowAgg (cost=87,452.85..87,481.01 rows=1,408 width=32) (actual time=2,257.783..2,553.528 rows=618,740 loops=1)

20. 249.360 2,297.628 ↓ 439.4 618,740 1

Sort (cost=87,452.85..87,456.37 rows=1,408 width=28) (actual time=2,257.757..2,297.628 rows=618,740 loops=1)

  • Sort Key: public_10.provider_account_id, public_10.period_ends_at
  • Sort Method: quicksort Memory: 72,916kB
21. 119.636 2,048.268 ↓ 439.4 618,740 1

Group (cost=87,358.09..87,379.21 rows=1,408 width=28) (actual time=1,880.022..2,048.268 rows=618,740 loops=1)

  • Group Key: public_9.id, public_10.provider_account_id, public_10.period_ends_at, public_10.total_amount_cents, public_10.created_at
22. 392.645 1,928.632 ↓ 439.4 618,740 1

Sort (cost=87,358.09..87,361.61 rows=1,408 width=28) (actual time=1,880.019..1,928.632 rows=618,740 loops=1)

  • Sort Key: public_9.id, public_10.provider_account_id, public_10.period_ends_at, public_10.total_amount_cents, public_10.created_at
  • Sort Method: quicksort Memory: 72,916kB
23. 104.039 1,535.987 ↓ 439.4 618,740 1

Nested Loop (cost=82,734.43..87,284.46 rows=1,408 width=28) (actual time=234.380..1,535.987 rows=618,740 loops=1)

24. 57.722 433.895 ↓ 58.5 58,709 1

Hash Join (cost=82,733.85..83,520.52 rows=1,003 width=20) (actual time=234.347..433.895 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_1.provider_account_id = public_9.provider_account_id)
25. 143.743 143.743 ↓ 4.9 145,814 1

CTE Scan on provider_accounts_list provider_accounts_list_1 (cost=0.00..591.72 rows=29,586 width=4) (actual time=1.788..143.743 rows=145,814 loops=1)

26. 35.954 232.430 ↓ 6.9 192,118 1

Hash (cost=82,383.40..82,383.40 rows=28,036 width=16) (actual time=232.430..232.430 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
27. 27.921 196.476 ↓ 6.9 192,118 1

Nested Loop (cost=0.56..82,383.40 rows=28,036 width=16) (actual time=0.040..196.476 rows=192,118 loops=1)

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

29. 168.553 168.553 ↓ 6.9 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_9 (cost=0.56..82,103.02 rows=28,036 width=20) (actual time=0.038..168.553 rows=192,118 loops=1)

  • Index Cond: (payment_expected_on = payday_1.payday)
30. 998.053 998.053 ↓ 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.74 rows=1 width=24) (actual time=0.006..0.017 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
31. 8.855 48,676.745 ↓ 86.6 23,811 1

Materialize (cost=2,250,564.99..2,250,662.83 rows=275 width=231) (actual time=48,618.061..48,676.745 rows=23,811 loops=1)

32. 7.857 48,667.890 ↓ 86.6 23,811 1

Merge Left Join (cost=2,250,564.99..2,250,662.15 rows=275 width=231) (actual time=48,618.056..48,667.890 rows=23,811 loops=1)

  • Merge Cond: (public_7.id = public_25.pay_period_id)
33. 4.143 48,150.609 ↓ 86.6 23,811 1

Merge Left Join (cost=2,166,405.69..2,166,501.38 rows=275 width=191) (actual time=48,129.399..48,150.609 rows=23,811 loops=1)

  • Merge Cond: (public_7.id = public_21.id)
34. 4.614 44,202.040 ↓ 86.6 23,811 1

Merge Left Join (cost=2,080,498.37..2,080,593.34 rows=275 width=187) (actual time=44,184.970..44,202.040 rows=23,811 loops=1)

  • Merge Cond: (public_7.id = public_19.noteable_id)
35. 8.743 43,714.773 ↓ 86.6 23,811 1

Merge Left Join (cost=1,996,470.75..1,996,564.81 rows=275 width=155) (actual time=43,702.915..43,714.773 rows=23,811 loops=1)

  • Merge Cond: (public_7.id = balances.id)
36. 16.638 31,318.677 ↓ 86.6 23,811 1

Sort (cost=657,708.69..657,709.38 rows=275 width=139) (actual time=31,315.276..31,318.677 rows=23,811 loops=1)

  • Sort Key: public_7.id
  • Sort Method: quicksort Memory: 5,709kB
37. 14.085 31,302.039 ↓ 86.6 23,811 1

Merge Left Join (cost=644,195.46..657,697.55 rows=275 width=139) (actual time=28,800.783..31,302.039 rows=23,811 loops=1)

  • Merge Cond: (public_6.id = public_15.user_id)
38. 21.033 3,529.981 ↓ 86.6 23,811 1

Sort (cost=265,195.20..265,195.89 rows=275 width=135) (actual time=3,526.684..3,529.981 rows=23,811 loops=1)

  • Sort Key: public_6.id
  • Sort Method: quicksort Memory: 5,342kB
39. 20.582 3,508.948 ↓ 86.6 23,811 1

Hash Left Join (cost=255,297.06..265,184.06 rows=275 width=135) (actual time=2,552.605..3,508.948 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
40. 19.895 3,079.957 ↓ 155.3 58,709 1

Hash Left Join (cost=171,286.56..181,172.57 rows=378 width=127) (actual time=2,144.181..3,079.957 rows=58,709 loops=1)

  • Hash Cond: (public_7.id = advance_stats.pay_period_id)
41. 31.645 1,148.651 ↓ 155.3 58,709 1

Nested Loop (cost=82,821.80..92,706.81 rows=378 width=111) (actual time=232.760..1,148.651 rows=58,709 loops=1)

42. 42.641 940.879 ↓ 155.3 58,709 1

Nested Loop (cost=82,821.37..91,757.78 rows=378 width=104) (actual time=232.745..940.879 rows=58,709 loops=1)

43. 17.873 663.402 ↓ 155.3 58,709 1

Hash Join (cost=82,819.42..89,504.60 rows=378 width=90) (actual time=232.723..663.402 rows=58,709 loops=1)

  • Hash Cond: (public_3.provider_id = public_4.id)
44. 24.917 645.057 ↓ 155.3 58,709 1

Nested Loop (cost=82,734.56..89,418.73 rows=378 width=77) (actual time=232.233..645.057 rows=58,709 loops=1)

45. 23.545 502.722 ↓ 148.3 58,709 1

Nested Loop (cost=82,734.28..88,524.97 rows=396 width=66) (actual time=232.215..502.722 rows=58,709 loops=1)

  • Join Filter: (provider_accounts_list.provider_account_id = public_3.id)
46. 52.948 303.050 ↓ 58.5 58,709 1

Hash Join (cost=82,733.85..86,035.33 rows=1,003 width=16) (actual time=232.194..303.050 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list.provider_account_id = public_7.provider_account_id)
47. 17.949 17.949 ↓ 4.9 145,814 1

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

48. 34.736 232.153 ↓ 6.9 192,118 1

Hash (cost=82,383.40..82,383.40 rows=28,036 width=12) (actual time=232.153..232.153 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 10,304kB
49. 38.367 197.417 ↓ 6.9 192,118 1

Nested Loop (cost=0.56..82,383.40 rows=28,036 width=12) (actual time=0.062..197.417 rows=192,118 loops=1)

50. 0.002 0.002 ↑ 1.0 1 1

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

51. 159.048 159.048 ↓ 6.9 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_7 (cost=0.56..82,103.02 rows=28,036 width=12) (actual time=0.058..159.048 rows=192,118 loops=1)

  • Index Cond: (payment_expected_on = payday.payday)
52. 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)
53. 117.418 117.418 ↑ 1.0 1 58,709

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

  • Index Cond: (id = public_3.company_id)
54. 0.074 0.472 ↓ 1.0 398 1

Hash (cost=79.94..79.94 rows=394 width=21) (actual time=0.472..0.472 rows=398 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
55. 0.398 0.398 ↓ 1.0 398 1

Seq Scan on providers public_4 (cost=0.00..79.94 rows=394 width=21) (actual time=0.009..0.398 rows=398 loops=1)

56. 58.709 234.836 ↑ 1.0 1 58,709

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

  • Recheck Cond: (id = public_3.user_id)
  • Heap Blocks: exact=60,313
57. 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)
58. 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)
59. 0.955 1,911.411 ↓ 8.4 7,049 1

Hash (cost=88,454.32..88,454.32 rows=835 width=20) (actual time=1,911.411..1,911.411 rows=7,049 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 367kB
60. 0.692 1,910.456 ↓ 8.4 7,049 1

Subquery Scan on advance_stats (cost=88,423.01..88,454.32 rows=835 width=20) (actual time=1,903.042..1,910.456 rows=7,049 loops=1)

61. 5.250 1,909.764 ↓ 8.4 7,049 1

GroupAggregate (cost=88,423.01..88,445.97 rows=835 width=20) (actual time=1,903.041..1,909.764 rows=7,049 loops=1)

  • Group Key: public_11.pay_period_id
62. 8.807 1,904.514 ↓ 28.0 23,376 1

Sort (cost=88,423.01..88,425.10 rows=835 width=15) (actual time=1,903.033..1,904.514 rows=23,376 loops=1)

  • Sort Key: public_11.pay_period_id
  • Sort Method: quicksort Memory: 1,865kB
63. 19.007 1,895.707 ↓ 28.0 23,376 1

Nested Loop (cost=82,734.43..88,382.49 rows=835 width=15) (actual time=223.686..1,895.707 rows=23,376 loops=1)

64. 50.259 291.557 ↓ 58.5 58,709 1

Hash Join (cost=82,733.85..83,520.52 rows=1,003 width=4) (actual time=223.631..291.557 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_2.provider_account_id = public_12.provider_account_id)
65. 17.689 17.689 ↓ 4.9 145,814 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.689 rows=145,814 loops=1)

66. 32.433 223.609 ↓ 6.9 192,118 1

Hash (cost=82,383.40..82,383.40 rows=28,036 width=8) (actual time=223.609..223.609 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
67. 23.463 191.176 ↓ 6.9 192,118 1

Nested Loop (cost=0.56..82,383.40 rows=28,036 width=8) (actual time=0.046..191.176 rows=192,118 loops=1)

68. 0.001 0.001 ↑ 1.0 1 1

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

69. 167.712 167.712 ↓ 6.9 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_12 (cost=0.56..82,103.02 rows=28,036 width=12) (actual time=0.043..167.712 rows=192,118 loops=1)

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

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

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

Hash (cost=84,007.91..84,007.91 rows=207 width=12) (actual time=408.409..408.409 rows=11,625 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 628kB
72. 1.032 406.702 ↓ 56.2 11,625 1

Subquery Scan on provider_payments_list (cost=84,002.22..84,007.91 rows=207 width=12) (actual time=402.193..406.702 rows=11,625 loops=1)

73. 2.843 405.670 ↓ 56.2 11,625 1

GroupAggregate (cost=84,002.22..84,005.84 rows=207 width=12) (actual time=402.192..405.670 rows=11,625 loops=1)

  • Group Key: public_14.id
74. 4.077 402.827 ↓ 58.5 12,106 1

Sort (cost=84,002.22..84,002.74 rows=207 width=8) (actual time=402.187..402.827 rows=12,106 loops=1)

  • Sort Key: public_14.id
  • Sort Method: quicksort Memory: 952kB
75. 9.799 398.750 ↓ 58.5 12,106 1

Nested Loop (cost=82,734.29..83,994.26 rows=207 width=8) (actual time=227.083..398.750 rows=12,106 loops=1)

76. 27.032 271.533 ↓ 58.5 58,709 1

Hash Join (cost=82,733.85..83,520.52 rows=1,003 width=4) (actual time=227.062..271.533 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_3.provider_account_id = public_14.provider_account_id)
77. 17.481 17.481 ↓ 4.9 145,814 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..17.481 rows=145,814 loops=1)

78. 33.044 227.020 ↓ 6.9 192,118 1

Hash (cost=82,383.40..82,383.40 rows=28,036 width=8) (actual time=227.020..227.020 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
79. 24.327 193.976 ↓ 6.9 192,118 1

Nested Loop (cost=0.56..82,383.40 rows=28,036 width=8) (actual time=0.048..193.976 rows=192,118 loops=1)

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

81. 169.647 169.647 ↓ 6.9 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_14 (cost=0.56..82,103.02 rows=28,036 width=12) (actual time=0.044..169.647 rows=192,118 loops=1)

  • Index Cond: (payment_expected_on = payday_3.payday)
82. 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)
83. 804.598 27,757.973 ↑ 12.3 51,329 1

Unique (cost=379,000.26..384,614.67 rows=630,836 width=20) (actual time=25,274.066..27,757.973 rows=51,329 loops=1)

84. 8,411.633 26,953.375 ↓ 11.7 13,189,860 1

Sort (cost=379,000.26..381,807.47 rows=1,122,882 width=20) (actual time=25,274.063..26,953.375 rows=13,189,860 loops=1)

  • Sort Key: public_15.user_id, public_15.created_at DESC
  • Sort Method: external merge Disk: 439,880kB
85. 2,604.547 18,541.742 ↓ 11.8 13,220,885 1

Nested Loop (cost=100,353.05..266,157.50 rows=1,122,882 width=20) (actual time=799.191..18,541.742 rows=13,220,885 loops=1)

86. 97.812 918.353 ↓ 4.9 145,814 1

Hash Join (cost=100,352.49..101,021.87 rows=29,586 width=4) (actual time=799.138..918.353 rows=145,814 loops=1)

  • Hash Cond: (provider_accounts_list_4.provider_account_id = public_16.id)
87. 29.058 29.058 ↓ 4.9 145,814 1

CTE Scan on provider_accounts_list provider_accounts_list_4 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.000..29.058 rows=145,814 loops=1)

88. 413.302 791.483 ↑ 1.0 2,071,500 1

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 97,302kB
89. 378.181 378.181 ↑ 1.0 2,071,500 1

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

90. 15,018.842 15,018.842 ↑ 1.3 91 145,814

Index Scan using index_available_balance_changes_on_user_id on available_balance_changes public_15 (cost=0.57..4.42 rows=116 width=20) (actual time=0.010..0.103 rows=91 loops=145,814)

  • Index Cond: (user_id = public_16.user_id)
91. 1.470 12,387.353 ↑ 3.9 4,751 1

Sort (cost=1,338,762.06..1,338,808.40 rows=18,534 width=20) (actual time=12,387.072..12,387.353 rows=4,751 loops=1)

  • Sort Key: balances.id
  • Sort Method: quicksort Memory: 586kB
92. 0.458 12,385.883 ↑ 3.7 5,035 1

Subquery Scan on balances (cost=1,336,938.52..1,337,448.20 rows=18,534 width=20) (actual time=12,382.213..12,385.883 rows=5,035 loops=1)

93. 2.945 12,385.425 ↑ 3.7 5,035 1

WindowAgg (cost=1,336,938.52..1,337,262.86 rows=18,534 width=28) (actual time=12,382.212..12,385.425 rows=5,035 loops=1)

94. 1.489 12,382.480 ↑ 3.7 5,035 1

Sort (cost=1,336,938.52..1,336,984.85 rows=18,534 width=20) (actual time=12,382.190..12,382.480 rows=5,035 loops=1)

  • Sort Key: public_17.provider_account_id
  • Sort Method: quicksort Memory: 586kB
95. 1.413 12,380.991 ↑ 3.7 5,035 1

GroupAggregate (cost=1,335,253.97..1,335,624.65 rows=18,534 width=20) (actual time=12,379.201..12,380.991 rows=5,035 loops=1)

  • Group Key: public_17.id, public_18.total_amount_cents
96. 3.319 12,379.578 ↑ 3.7 5,035 1

Sort (cost=1,335,253.97..1,335,300.31 rows=18,534 width=12) (actual time=12,379.195..12,379.578 rows=5,035 loops=1)

  • Sort Key: public_17.id, public_18.total_amount_cents
  • Sort Method: quicksort Memory: 429kB
97. 1,602.719 12,376.259 ↑ 3.7 5,035 1

Nested Loop (cost=1.00..1,333,940.11 rows=18,534 width=12) (actual time=1.630..12,376.259 rows=5,035 loops=1)

98. 421.344 3,652.581 ↓ 3.2 2,373,653 1

Nested Loop (cost=0.56..936,216.37 rows=741,702 width=8) (actual time=0.035..3,652.581 rows=2,373,653 loops=1)

99. 23.329 23.329 ↓ 4.9 145,814 1

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

100. 3,207.908 3,207.908 ↑ 1.6 16 145,814

Index Scan using index_pay_periods_on_status_and_provider_account_id on pay_periods public_17 (cost=0.56..31.37 rows=25 width=8) (actual time=0.007..0.022 rows=16 loops=145,814)

  • Index Cond: (((status)::text = 'settled'::text) AND (provider_account_id = provider_accounts_list_5.provider_account_id))
101. 7,120.959 7,120.959 ↓ 0.0 0 2,373,653

Index Scan using index_settlements_on_pay_period_id on settlements public_18 (cost=0.44..0.53 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=2,373,653)

  • Index Cond: (pay_period_id = public_17.id)
  • Filter: ((total_amount_cents <> 0) AND ((status)::text = 'unprocessed'::text))
  • Rows Removed by Filter: 1
102. 0.619 482.653 ↓ 39.2 235 1

GroupAggregate (cost=84,027.62..84,027.76 rows=6 width=36) (actual time=482.051..482.653 rows=235 loops=1)

  • Group Key: public_19.noteable_id
103. 0.177 482.034 ↓ 39.2 235 1

Sort (cost=84,027.62..84,027.64 rows=6 width=88) (actual time=481.990..482.034 rows=235 loops=1)

  • Sort Key: public_19.noteable_id
  • Sort Method: quicksort Memory: 84kB
104. 31.908 481.857 ↓ 39.2 235 1

Nested Loop (cost=82,734.29..84,027.54 rows=6 width=88) (actual time=229.522..481.857 rows=235 loops=1)

105. 27.756 273.822 ↓ 58.5 58,709 1

Hash Join (cost=82,733.85..83,520.52 rows=1,003 width=4) (actual time=229.171..273.822 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_6.provider_account_id = public_20.provider_account_id)
106. 16.940 16.940 ↓ 4.9 145,814 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.940 rows=145,814 loops=1)

107. 31.474 229.126 ↓ 6.9 192,118 1

Hash (cost=82,383.40..82,383.40 rows=28,036 width=8) (actual time=229.126..229.126 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
108. 27.463 197.652 ↓ 6.9 192,118 1

Nested Loop (cost=0.56..82,383.40 rows=28,036 width=8) (actual time=0.042..197.652 rows=192,118 loops=1)

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

110. 170.187 170.187 ↓ 6.9 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_20 (cost=0.56..82,103.02 rows=28,036 width=12) (actual time=0.039..170.187 rows=192,118 loops=1)

  • Index Cond: (payment_expected_on = payday_4.payday)
111. 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.50 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))
112. 0.001 3,944.426 ↓ 0.0 0 1

GroupAggregate (cost=85,907.31..85,907.33 rows=1 width=8) (actual time=3,944.426..3,944.426 rows=0 loops=1)

  • Group Key: public_21.id
113. 0.003 3,944.425 ↓ 0.0 0 1

Sort (cost=85,907.31..85,907.32 rows=1 width=8) (actual time=3,944.425..3,944.425 rows=0 loops=1)

  • Sort Key: public_21.id
  • Sort Method: quicksort Memory: 25kB
114. 0.001 3,944.422 ↓ 0.0 0 1

Nested Loop (cost=82,735.01..85,907.30 rows=1 width=8) (actual time=3,944.422..3,944.422 rows=0 loops=1)

115. 60.230 3,944.421 ↓ 0.0 0 1

Nested Loop (cost=82,734.72..85,905.73 rows=5 width=12) (actual time=3,944.421..3,944.421 rows=0 loops=1)

  • Join Filter: (public_21.ends_at = public_23.period_ends_at)
  • Rows Removed by Join Filter: 534,714
116. 43.091 537.778 ↓ 58.5 58,709 1

Nested Loop (cost=82,734.28..83,979.20 rows=1,003 width=28) (actual time=237.273..537.778 rows=58,709 loops=1)

117. 61.784 318.560 ↓ 58.5 58,709 1

Hash Join (cost=82,733.85..83,520.52 rows=1,003 width=20) (actual time=237.250..318.560 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_7.provider_account_id = public_21.provider_account_id)
118. 19.653 19.653 ↓ 4.9 145,814 1

CTE Scan on provider_accounts_list provider_accounts_list_7 (cost=0.00..591.72 rows=29,586 width=4) (actual time=0.000..19.653 rows=145,814 loops=1)

119. 35.937 237.123 ↓ 6.9 192,118 1

Hash (cost=82,383.40..82,383.40 rows=28,036 width=16) (actual time=237.123..237.123 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
120. 28.261 201.186 ↓ 6.9 192,118 1

Nested Loop (cost=0.56..82,383.40 rows=28,036 width=16) (actual time=0.044..201.186 rows=192,118 loops=1)

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

122. 172.923 172.923 ↓ 6.9 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_21 (cost=0.56..82,103.02 rows=28,036 width=20) (actual time=0.042..172.923 rows=192,118 loops=1)

  • Index Cond: (payment_expected_on = payday_5.payday)
123. 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)
124. 3,346.413 3,346.413 ↓ 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.016..0.057 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
125. 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[]))
126. 19.336 509.424 ↓ 530.0 11,661 1

GroupAggregate (cost=84,159.30..84,159.80 rows=22 width=44) (actual time=488.653..509.424 rows=11,661 loops=1)

  • Group Key: public_25.pay_period_id
127. 5.903 490.088 ↓ 544.0 11,969 1

Sort (cost=84,159.30..84,159.36 rows=22 width=23) (actual time=488.628..490.088 rows=11,969 loops=1)

  • Sort Key: public_25.pay_period_id
  • Sort Method: quicksort Memory: 1,320kB
128. 6.054 484.185 ↓ 544.0 11,969 1

Nested Loop (cost=82,734.85..84,158.81 rows=22 width=23) (actual time=225.385..484.185 rows=11,969 loops=1)

129. 0.000 442.224 ↓ 544.0 11,969 1

Nested Loop (cost=82,734.29..84,055.39 rows=22 width=20) (actual time=225.364..442.224 rows=11,969 loops=1)

130. 30.466 272.919 ↓ 58.5 58,709 1

Hash Join (cost=82,733.85..83,520.52 rows=1,003 width=4) (actual time=225.338..272.919 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_8.provider_account_id = public_27.provider_account_id)
131. 17.166 17.166 ↓ 4.9 145,814 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..17.166 rows=145,814 loops=1)

132. 33.283 225.287 ↓ 6.9 192,118 1

Hash (cost=82,383.40..82,383.40 rows=28,036 width=8) (actual time=225.287..225.287 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
133. 22.974 192.004 ↓ 6.9 192,118 1

Nested Loop (cost=0.56..82,383.40 rows=28,036 width=8) (actual time=0.045..192.004 rows=192,118 loops=1)

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

135. 169.028 169.028 ↓ 6.9 192,118 1

Index Scan using index_pay_periods_on_payment_expected_on on pay_periods public_27 (cost=0.56..82,103.02 rows=28,036 width=12) (actual time=0.042..169.028 rows=192,118 loops=1)

  • Index Cond: (payment_expected_on = payday_6.payday)
136. 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.002..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
137. 35.907 35.907 ↑ 1.0 1 11,969

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,969)

  • Index Cond: (id = public_25.payment_id)
138. 0.005 52,233.357 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..29.63 rows=1 width=792) (actual time=52,233.356..52,233.357 rows=1 loops=1)

139. 37.577 52,233.352 ↑ 1.0 1 1

GroupAggregate (cost=0.00..29.59 rows=1 width=528) (actual time=52,233.352..52,233.352 rows=1 loops=1)

  • Group Key: 'Count'::character varying, NULL::text, NULL::text, NULL::text, NULL::text
140. 52,195.775 52,195.775 ↓ 86.6 23,811 1

CTE Scan on data (cost=0.00..5.50 rows=275 width=372) (actual time=51,991.511..52,195.775 rows=23,811 loops=1)

141. 0.002 14.855 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..18.66 rows=1 width=792) (actual time=14.855..14.855 rows=1 loops=1)

142. 7.700 14.853 ↑ 1.0 1 1

GroupAggregate (cost=0.00..18.64 rows=1 width=780) (actual time=14.853..14.853 rows=1 loops=1)

  • Group Key: 'Total $$'::character varying, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::text, NULL::text
143. 7.153 7.153 ↓ 86.6 23,811 1

CTE Scan on data data_1 (cost=0.00..5.50 rows=275 width=300) (actual time=0.001..7.153 rows=23,811 loops=1)

144. 4.206 128.783 ↓ 86.6 23,811 1

Subquery Scan on *SELECT* 3 (cost=37.27..45.52 rows=275 width=792) (actual time=115.931..128.783 rows=23,811 loops=1)

145. 7.076 124.577 ↓ 86.6 23,811 1

Subquery Scan on t1 (cost=37.27..40.70 rows=275 width=780) (actual time=115.929..124.577 rows=23,811 loops=1)

146. 53.082 117.501 ↓ 86.6 23,811 1

Sort (cost=37.27..37.95 rows=275 width=788) (actual time=115.928..117.501 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_periods
  • Sort Method: quicksort Memory: 7,178kB
147. 64.419 64.419 ↓ 86.6 23,811 1

CTE Scan on data data_2 (cost=0.00..26.12 rows=275 width=788) (actual time=0.019..64.419 rows=23,811 loops=1)

Planning time : 21.354 ms
Execution time : 52,494.856 ms