explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JHqX : LG4605J

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.476 304,369.317 ↑ 7.6 23,980 1

Append (cost=2,342,390.88..2,475,312.65 rows=182,680 width=784) (actual time=304,218.298..304,369.317 rows=23,980 loops=1)

2.          

CTE provider_accounts_list

3. 24.031 322.061 ↓ 5.0 145,152 1

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
6. 78.720 505.955 ↓ 4.0 48,384 3 / 3

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

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

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

8. 0.030 25.501 ↑ 1.0 22 3 / 3

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

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

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

  • Recheck Cond: (provider_id = 202)
  • Heap Blocks: exact=18
10. 1.324 1.324 ↑ 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=1.324..1.324 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. 15.167 304,159.425 ↑ 7.6 23,978 1

Merge Right Join (cost=2,270,100.12..2,274,190.39 rows=182,678 width=243) (actual time=304,034.752..304,159.425 rows=23,978 loops=1)

  • Merge Cond: (public_9.provider_account_id = provider_accounts_list.provider_account_id)
15. 56.675 250,351.451 ↓ 28.6 42,645 1

Unique (cost=91,584.06..91,598.95 rows=1,489 width=24) (actual time=250,265.365..250,351.451 rows=42,645 loops=1)

16. 160.839 250,294.776 ↓ 415.5 618,740 1

Sort (cost=91,584.06..91,587.78 rows=1,489 width=24) (actual time=250,265.363..250,294.776 rows=618,740 loops=1)

  • Sort Key: public_9.provider_account_id, (max(public_10.total_amount_cents) OVER (?)), (first_value(public_10.total_amount_cents) OVER (?))
  • Sort Method: quicksort Memory: 72,916kB
17. 235.168 250,133.937 ↓ 415.5 618,740 1

WindowAgg (cost=91,475.81..91,505.59 rows=1,489 width=24) (actual time=249,864.193..250,133.937 rows=618,740 loops=1)

18. 290.839 249,898.769 ↓ 415.5 618,740 1

Sort (cost=91,475.81..91,479.53 rows=1,489 width=20) (actual time=249,864.186..249,898.769 rows=618,740 loops=1)

  • Sort Key: public_9.provider_account_id, public_10.created_at DESC
  • Sort Method: quicksort Memory: 72,916kB
19. 255.774 249,607.930 ↓ 415.5 618,740 1

WindowAgg (cost=91,360.11..91,397.34 rows=1,489 width=20) (actual time=249,200.619..249,607.930 rows=618,740 loops=1)

20. 105.375 249,352.156 ↓ 415.5 618,740 1

Group (cost=91,360.11..91,375.00 rows=1,489 width=16) (actual time=249,200.592..249,352.156 rows=618,740 loops=1)

  • Group Key: public_9.provider_account_id, public_10.total_amount_cents, public_10.created_at
21. 485.241 249,246.781 ↓ 415.5 618,740 1

Sort (cost=91,360.11..91,363.83 rows=1,489 width=16) (actual time=249,200.590..249,246.781 rows=618,740 loops=1)

  • Sort Key: public_9.provider_account_id, public_10.total_amount_cents, public_10.created_at
  • Sort Method: quicksort Memory: 53,580kB
22. 245.258 248,761.540 ↓ 415.5 618,740 1

Nested Loop (cost=86,785.30..91,281.64 rows=1,489 width=16) (actual time=24,292.979..248,761.540 rows=618,740 loops=1)

23. 122.220 24,776.283 ↓ 56.7 58,709 1

Hash Join (cost=86,784.72..87,587.36 rows=1,036 width=16) (actual time=24,287.174..24,776.283 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_1.provider_account_id = public_9.provider_account_id)
24. 400.932 400.932 ↓ 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=33.900..400.932 rows=145,152 loops=1)

25. 64.692 24,253.131 ↓ 6.7 192,118 1

Hash (cost=86,427.27..86,427.27 rows=28,596 width=12) (actual time=24,253.131..24,253.131 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
26. 41.997 24,188.439 ↓ 6.7 192,118 1

Nested Loop (cost=862.18..86,427.27 rows=28,596 width=12) (actual time=77.004..24,188.439 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. 24,078.126 24,146.440 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_9 (cost=862.18..86,141.29 rows=28,596 width=16) (actual time=76.999..24,146.440 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.03 rows=28,596 width=0) (actual time=68.314..68.314 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_2.payday)
30. 223,739.999 223,739.999 ↓ 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.56 rows=1 width=24) (actual time=1.031..3.811 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.684 53,792.807 ↑ 1.0 23,978 1

Materialize (cost=2,178,516.07..2,179,437.31 rows=24,537 width=231) (actual time=53,769.372..53,792.807 rows=23,978 loops=1)

32. 9.130 53,784.123 ↑ 1.0 23,978 1

Merge Left Join (cost=2,178,516.07..2,179,375.97 rows=24,537 width=231) (actual time=53,769.367..53,784.123 rows=23,978 loops=1)

  • Merge Cond: (provider_accounts_list.provider_account_id = public_26.provider_account_id)
33. 18.886 52,766.305 ↓ 85.6 23,978 1

Sort (cost=906,410.44..906,411.14 rows=280 width=215) (actual time=52,763.455..52,766.305 rows=23,978 loops=1)

  • Sort Key: public_3.id
  • Sort Method: quicksort Memory: 5,885kB
34. 11.093 52,747.419 ↓ 85.6 23,978 1

Hash Left Join (cost=893,644.51..906,399.06 rows=280 width=215) (actual time=50,242.964..52,747.419 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = settlements_agg.pay_period_id)
35. 5.074 52,280.720 ↓ 85.6 23,978 1

Hash Left Join (cost=805,401.57..818,155.39 rows=280 width=179) (actual time=49,787.343..52,280.720 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = net_earnings_reported.pay_period_id)
36. 5.968 48,174.621 ↓ 85.6 23,978 1

Hash Left Join (cost=715,372.30..728,125.38 rows=280 width=175) (actual time=45,686.310..48,174.621 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = pre_settlement_exclusions.pay_period_id)
37. 17.228 47,738.634 ↓ 85.6 23,978 1

Merge Left Join (cost=627,266.68..640,019.03 rows=280 width=143) (actual time=45,256.277..47,738.634 rows=23,978 loops=1)

  • Merge Cond: (public_6.id = public_15.user_id)
38. 39.924 19,905.435 ↓ 85.6 23,978 1

Sort (cost=274,175.02..274,175.72 rows=280 width=139) (actual time=19,901.570..19,905.435 rows=23,978 loops=1)

  • Sort Key: public_6.id
  • Sort Method: quicksort Memory: 5,809kB
39. 36.888 19,865.511 ↓ 85.6 23,978 1

Hash Left Join (cost=267,561.08..274,163.64 rows=280 width=139) (actual time=2,364.586..19,865.511 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
40. 36.481 19,497.154 ↓ 152.5 58,709 1

Hash Left Join (cost=179,467.88..186,069.43 rows=385 width=131) (actual time=2,023.167..19,497.154 rows=58,709 loops=1)

  • Hash Cond: (public_7.id = advance_stats.pay_period_id)
41. 38.446 17,607.775 ↓ 152.5 58,709 1

Nested Loop (cost=86,872.15..93,472.69 rows=385 width=115) (actual time=170.253..17,607.775 rows=58,709 loops=1)

42. 40.159 1,130.809 ↓ 152.5 58,709 1

Nested Loop (cost=86,871.72..93,261.06 rows=385 width=108) (actual time=167.015..1,130.809 rows=58,709 loops=1)

43. 24.342 797.105 ↓ 152.5 58,709 1

Hash Join (cost=86,871.17..91,450.12 rows=385 width=94) (actual time=166.989..797.105 rows=58,709 loops=1)

  • Hash Cond: (public_3.provider_id = public_4.id)
44. 25.512 772.293 ↓ 152.5 58,709 1

Nested Loop (cost=86,785.43..91,363.36 rows=385 width=81) (actual time=166.502..772.293 rows=58,709 loops=1)

45. 70.356 570.654 ↓ 145.3 58,709 1

Nested Loop (cost=86,785.15..91,243.02 rows=404 width=70) (actual time=164.979..570.654 rows=58,709 loops=1)

  • Join Filter: (provider_accounts_list.provider_account_id = public_3.id)
46. 70.470 265.462 ↓ 56.7 58,709 1

Hash Join (cost=86,784.72..90,756.46 rows=1,036 width=16) (actual time=164.945..265.462 rows=58,709 loops=1)

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

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

48. 33.430 164.818 ↓ 6.7 192,118 1

Hash (cost=86,427.27..86,427.27 rows=28,596 width=12) (actual time=164.818..164.818 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 10,304kB
49. 38.433 131.388 ↓ 6.7 192,118 1

Nested Loop (cost=862.18..86,427.27 rows=28,596 width=12) (actual time=21.361..131.388 rows=192,118 loops=1)

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

51. 78.703 92.953 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_7 (cost=862.18..86,141.29 rows=28,596 width=12) (actual time=21.355..92.953 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.03 rows=28,596 width=0) (actual time=14.250..14.250 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_1.payday)
53. 234.836 234.836 ↑ 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.004..0.004 rows=1 loops=58,709)

  • Index Cond: (id = public_7.provider_account_id)
54. 176.127 176.127 ↑ 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.003..0.003 rows=1 loops=58,709)

  • Index Cond: (id = public_3.company_id)
55. 0.067 0.470 ↑ 1.1 393 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
56. 0.403 0.403 ↑ 1.1 393 1

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

57. 293.545 293.545 ↑ 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.005..0.005 rows=1 loops=58,709)

  • Index Cond: (id = public_3.user_id)
58. 16,438.520 16,438.520 ↑ 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.280..0.280 rows=1 loops=58,709)

  • Index Cond: (id = public_3.profile_id)
59. 1.024 1,852.898 ↓ 7.8 7,049 1

Hash (cost=92,584.49..92,584.49 rows=899 width=20) (actual time=1,852.898..1,852.898 rows=7,049 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 367kB
60. 0.762 1,851.874 ↓ 7.8 7,049 1

Subquery Scan on advance_stats (cost=92,550.78..92,584.49 rows=899 width=20) (actual time=1,844.293..1,851.874 rows=7,049 loops=1)

61. 5.373 1,851.112 ↓ 7.8 7,049 1

GroupAggregate (cost=92,550.78..92,575.50 rows=899 width=20) (actual time=1,844.292..1,851.112 rows=7,049 loops=1)

  • Group Key: public_11.pay_period_id
62. 9.162 1,845.739 ↓ 26.0 23,376 1

Sort (cost=92,550.78..92,553.03 rows=899 width=15) (actual time=1,844.284..1,845.739 rows=23,376 loops=1)

  • Sort Key: public_11.pay_period_id
  • Sort Method: quicksort Memory: 1,865kB
63. 34.362 1,836.577 ↓ 26.0 23,376 1

Nested Loop (cost=86,785.30..92,506.67 rows=899 width=15) (actual time=148.586..1,836.577 rows=23,376 loops=1)

64. 51.073 217.072 ↓ 56.7 58,709 1

Hash Join (cost=86,784.72..87,587.36 rows=1,036 width=4) (actual time=148.217..217.072 rows=58,709 loops=1)

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

66. 31.476 148.194 ↓ 6.7 192,118 1

Hash (cost=86,427.27..86,427.27 rows=28,596 width=8) (actual time=148.194..148.194 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
67. 22.844 116.718 ↓ 6.7 192,118 1

Nested Loop (cost=862.18..86,427.27 rows=28,596 width=8) (actual time=22.755..116.718 rows=192,118 loops=1)

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

69. 78.158 93.872 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_12 (cost=862.18..86,141.29 rows=28,596 width=12) (actual time=22.750..93.872 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.03 rows=28,596 width=0) (actual time=15.714..15.714 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_3.payday)
71. 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.70 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
72. 1.658 331.469 ↓ 54.8 11,625 1

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

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 628kB
73. 1.031 329.811 ↓ 54.8 11,625 1

Subquery Scan on provider_payments_list (cost=88,084.72..88,090.55 rows=212 width=12) (actual time=325.224..329.811 rows=11,625 loops=1)

74. 2.920 328.780 ↓ 54.8 11,625 1

GroupAggregate (cost=88,084.72..88,088.43 rows=212 width=12) (actual time=325.224..328.780 rows=11,625 loops=1)

  • Group Key: public_14.id
75. 4.245 325.860 ↓ 57.1 12,106 1

Sort (cost=88,084.72..88,085.25 rows=212 width=8) (actual time=325.219..325.860 rows=12,106 loops=1)

  • Sort Key: public_14.id
  • Sort Method: quicksort Memory: 952kB
76. 10.916 321.615 ↓ 57.1 12,106 1

Nested Loop (cost=86,785.16..88,076.52 rows=212 width=8) (actual time=148.495..321.615 rows=12,106 loops=1)

77. 28.358 193.281 ↓ 56.7 58,709 1

Hash Join (cost=86,784.72..87,587.36 rows=1,036 width=4) (actual time=148.433..193.281 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_3.provider_account_id = public_14.provider_account_id)
78. 16.550 16.550 ↓ 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..16.550 rows=145,152 loops=1)

79. 31.450 148.373 ↓ 6.7 192,118 1

Hash (cost=86,427.27..86,427.27 rows=28,596 width=8) (actual time=148.373..148.373 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
80. 22.967 116.923 ↓ 6.7 192,118 1

Nested Loop (cost=862.18..86,427.27 rows=28,596 width=8) (actual time=23.143..116.923 rows=192,118 loops=1)

81. 0.001 0.001 ↑ 1.0 1 1

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

82. 77.977 93.955 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_14 (cost=862.18..86,141.29 rows=28,596 width=12) (actual time=23.138..93.955 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.03 rows=28,596 width=0) (actual time=15.978..15.978 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_4.payday)
84. 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)
85. 785.696 27,815.971 ↑ 11.8 51,604 1

Unique (cost=353,091.66..358,212.49 rows=610,341 width=20) (actual time=25,354.678..27,815.971 rows=51,604 loops=1)

86. 8,137.568 27,030.275 ↓ 12.3 12,645,336 1

Sort (cost=353,091.66..355,652.08 rows=1,024,165 width=20) (actual time=25,354.674..27,030.275 rows=12,645,336 loops=1)

  • Sort Key: public_15.user_id, public_15.created_at DESC
  • Sort Method: external merge Disk: 421,208kB
87. 2,686.887 18,892.707 ↓ 12.4 12,659,637 1

Nested Loop (cost=99,104.53..250,849.19 rows=1,024,165 width=20) (actual time=1,130.056..18,892.707 rows=12,659,637 loops=1)

88. 101.785 1,255.164 ↓ 5.0 145,152 1

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

  • Hash Cond: (provider_accounts_list_4.provider_account_id = public_16.id)
89. 31.094 31.094 ↓ 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..31.094 rows=145,152 loops=1)

90. 411.682 1,122.285 ↓ 1.0 2,051,509 1

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 96,522kB
91. 710.603 710.603 ↓ 1.0 2,051,509 1

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

92. 14,950.656 14,950.656 ↑ 1.2 87 145,152

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

  • Index Cond: (user_id = public_16.user_id)
93. 0.051 430.019 ↓ 39.2 235 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
94. 0.026 429.968 ↓ 39.2 235 1

Subquery Scan on pre_settlement_exclusions (cost=88,105.34..88,105.54 rows=6 width=36) (actual time=429.582..429.968 rows=235 loops=1)

95. 0.372 429.942 ↓ 39.2 235 1

GroupAggregate (cost=88,105.34..88,105.48 rows=6 width=36) (actual time=429.581..429.942 rows=235 loops=1)

  • Group Key: public_17.noteable_id
96. 0.230 429.570 ↓ 39.2 235 1

Sort (cost=88,105.34..88,105.36 rows=6 width=85) (actual time=429.552..429.570 rows=235 loops=1)

  • Sort Key: public_17.noteable_id
  • Sort Method: quicksort Memory: 84kB
97. 46.178 429.340 ↓ 39.2 235 1

Nested Loop (cost=86,785.16..88,105.27 rows=6 width=85) (actual time=155.496..429.340 rows=235 loops=1)

98. 38.305 207.035 ↓ 56.7 58,709 1

Hash Join (cost=86,784.72..87,587.36 rows=1,036 width=4) (actual time=151.837..207.035 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_5.provider_account_id = public_18.provider_account_id)
99. 16.935 16.935 ↓ 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.001..16.935 rows=145,152 loops=1)

100. 30.790 151.795 ↓ 6.7 192,118 1

Hash (cost=86,427.27..86,427.27 rows=28,596 width=8) (actual time=151.795..151.795 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
101. 26.558 121.005 ↓ 6.7 192,118 1

Nested Loop (cost=862.18..86,427.27 rows=28,596 width=8) (actual time=21.339..121.005 rows=192,118 loops=1)

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

103. 80.076 94.445 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_18 (cost=862.18..86,141.29 rows=28,596 width=12) (actual time=21.333..94.445 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.03 rows=28,596 width=0) (actual time=14.369..14.369 rows=192,140 loops=1)

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

Index Scan using index_notes_on_noteable_type_and_noteable_id on notes public_17 (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_18.id))
106. 0.000 4,101.025 ↓ 0.0 0 1

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

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

Subquery Scan on net_earnings_reported (cost=90,029.23..90,029.26 rows=1 width=8) (actual time=4,101.025..4,101.025 rows=0 loops=1)

108. 0.001 4,101.024 ↓ 0.0 0 1

GroupAggregate (cost=90,029.23..90,029.25 rows=1 width=8) (actual time=4,101.024..4,101.024 rows=0 loops=1)

  • Group Key: public_19.id
109. 0.003 4,101.023 ↓ 0.0 0 1

Sort (cost=90,029.23..90,029.24 rows=1 width=8) (actual time=4,101.023..4,101.023 rows=0 loops=1)

  • Sort Key: public_19.id
  • Sort Method: quicksort Memory: 25kB
110. 0.000 4,101.020 ↓ 0.0 0 1

Nested Loop (cost=86,785.88..90,029.22 rows=1 width=8) (actual time=4,101.020..4,101.020 rows=0 loops=1)

111. 95.477 4,101.020 ↓ 0.0 0 1

Nested Loop (cost=86,785.59..90,027.65 rows=5 width=12) (actual time=4,101.019..4,101.020 rows=0 loops=1)

  • Join Filter: (public_19.ends_at = public_21.period_ends_at)
  • Rows Removed by Join Filter: 495,778
112. 62.236 483.003 ↓ 56.7 58,709 1

Nested Loop (cost=86,785.15..88,060.97 rows=1,036 width=28) (actual time=158.157..483.003 rows=58,709 loops=1)

113. 66.147 244.640 ↓ 56.7 58,709 1

Hash Join (cost=86,784.72..87,587.36 rows=1,036 width=20) (actual time=158.133..244.640 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_6.provider_account_id = public_19.provider_account_id)
114. 20.468 20.468 ↓ 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..20.468 rows=145,152 loops=1)

115. 34.413 158.025 ↓ 6.7 192,118 1

Hash (cost=86,427.27..86,427.27 rows=28,596 width=16) (actual time=158.025..158.025 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 11,054kB
116. 27.659 123.612 ↓ 6.7 192,118 1

Nested Loop (cost=862.18..86,427.27 rows=28,596 width=16) (actual time=22.997..123.612 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. 80.307 95.951 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_19 (cost=862.18..86,141.29 rows=28,596 width=20) (actual time=22.992..95.951 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.03 rows=28,596 width=0) (actual time=15.644..15.644 rows=192,140 loops=1)

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

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

  • Index Cond: (id = public_19.provider_account_id)
121. 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_21 (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_20.external_id)::text)
  • Filter: (public_20.company_id = company_id)
  • Rows Removed by Filter: 35
122. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = public_21.payroll_journal_id)
  • Filter: ((status)::text = ANY ('{presettlement,initial_presettlement,net_pay_only}'::text[]))
123. 2.143 455.606 ↓ 529.3 11,645 1

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

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 740kB
124. 1.213 453.463 ↓ 529.3 11,645 1

Subquery Scan on settlements_agg (cost=88,241.95..88,242.67 rows=22 width=44) (actual time=435.947..453.463 rows=11,645 loops=1)

125. 15.357 452.250 ↓ 529.3 11,645 1

GroupAggregate (cost=88,241.95..88,242.45 rows=22 width=44) (actual time=435.946..452.250 rows=11,645 loops=1)

  • Group Key: public_23.pay_period_id
126. 5.584 436.893 ↓ 542.7 11,940 1

Sort (cost=88,241.95..88,242.01 rows=22 width=22) (actual time=435.918..436.893 rows=11,940 loops=1)

  • Sort Key: public_23.pay_period_id
  • Sort Method: quicksort Memory: 1,317kB
127. 8.974 431.309 ↓ 542.7 11,940 1

Nested Loop (cost=86,785.72..88,241.46 rows=22 width=22) (actual time=152.224..431.309 rows=11,940 loops=1)

128. 7.938 386.515 ↓ 542.7 11,940 1

Nested Loop (cost=86,785.16..88,138.46 rows=22 width=20) (actual time=152.202..386.515 rows=11,940 loops=1)

129. 33.656 202.450 ↓ 56.7 58,709 1

Hash Join (cost=86,784.72..87,587.36 rows=1,036 width=4) (actual time=152.120..202.450 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_7.provider_account_id = public_25.provider_account_id)
130. 16.717 16.717 ↓ 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.000..16.717 rows=145,152 loops=1)

131. 32.454 152.077 ↓ 6.7 192,118 1

Hash (cost=86,427.27..86,427.27 rows=28,596 width=8) (actual time=152.077..152.077 rows=192,118 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 9,553kB
132. 22.998 119.623 ↓ 6.7 192,118 1

Nested Loop (cost=862.18..86,427.27 rows=28,596 width=8) (actual time=23.249..119.623 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. 80.595 96.623 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_25 (cost=862.18..86,141.29 rows=28,596 width=12) (actual time=23.243..96.623 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.03 rows=28,596 width=0) (actual time=16.028..16.028 rows=192,140 loops=1)

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

Index Scan using index_settlements_on_pay_period_id on settlements public_23 (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_25.id)
  • Filter: ((total_amount_cents <> 0) AND ((status)::text = 'paid'::text))
  • Rows Removed by Filter: 1
137. 35.820 35.820 ↑ 1.0 1 11,940

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

  • Index Cond: (id = public_23.payment_id)
138. 2.392 1,008.688 ↑ 4.1 4,251 1

GroupAggregate (cost=1,272,105.62..1,272,499.84 rows=17,520 width=20) (actual time=1,005.906..1,008.688 rows=4,251 loops=1)

  • Group Key: public_26.provider_account_id
139. 1.847 1,006.295 ↑ 3.3 5,260 1

Sort (cost=1,272,105.62..1,272,149.42 rows=17,520 width=12) (actual time=1,005.892..1,006.295 rows=5,260 loops=1)

  • Sort Key: public_26.provider_account_id
  • Sort Method: quicksort Memory: 440kB
140. 23.296 1,004.448 ↑ 3.3 5,271 1

Hash Join (cost=1,267,634.49..1,270,870.75 rows=17,520 width=12) (actual time=968.787..1,004.448 rows=5,271 loops=1)

  • Hash Cond: (provider_accounts_list_8.provider_account_id = public_26.provider_account_id)
141. 14.238 14.238 ↓ 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.001..14.238 rows=145,152 loops=1)

142. 23.509 966.914 ↑ 5.5 87,453 1

Hash (cost=1,261,591.49..1,261,591.49 rows=483,440 width=12) (actual time=966.914..966.914 rows=87,453 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 7,854kB
143. 6.339 943.405 ↑ 5.5 87,453 1

Gather (cost=1,000.56..1,261,591.49 rows=483,440 width=12) (actual time=3.052..943.405 rows=87,453 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
144. 5.247 937.066 ↑ 6.9 29,151 3 / 3

Nested Loop (cost=0.56..1,212,247.49 rows=201,433 width=12) (actual time=0.680..937.066 rows=29,151 loops=3)

145. 815.152 815.152 ↑ 7.2 29,167 3 / 3

Parallel Seq Scan on settlements public_27 (cost=0.00..382,764.66 rows=209,351 width=8) (actual time=0.662..815.152 rows=29,167 loops=3)

  • Filter: ((total_amount_cents <> 0) AND ((status)::text = 'unprocessed'::text))
  • Rows Removed by Filter: 7,106,235
146. 116.667 116.667 ↑ 1.0 1 87,500 / 3

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

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

SubPlan (for GroupAggregate)

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

149. 0.006 304,218.298 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..19,181.26 rows=1 width=784) (actual time=304,218.297..304,218.298 rows=1 loops=1)

150. 33.446 304,218.292 ↑ 1.0 1 1

GroupAggregate (cost=0.00..19,181.22 rows=1 width=520) (actual time=304,218.292..304,218.292 rows=1 loops=1)

  • Group Key: 'Count'::character varying, NULL::text, NULL::text, NULL::text, NULL::text
151. 304,184.846 304,184.846 ↑ 7.6 23,978 1

CTE Scan on data (cost=0.00..3,653.56 rows=182,678 width=368) (actual time=304,034.756..304,184.846 rows=23,978 loops=1)

152. 0.001 15.199 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..12,330.86 rows=1 width=784) (actual time=15.199..15.199 rows=1 loops=1)

153. 7.874 15.198 ↑ 1.0 1 1

GroupAggregate (cost=0.00..12,330.85 rows=1 width=776) (actual time=15.197..15.198 rows=1 loops=1)

  • Group Key: 'Total $$'::character varying, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::text, NULL::text
154. 7.324 7.324 ↑ 7.6 23,978 1

CTE Scan on data data_1 (cost=0.00..3,653.56 rows=182,678 width=300) (actual time=0.001..7.324 rows=23,978 loops=1)

155. 4.049 134.344 ↑ 7.6 23,978 1

Subquery Scan on *SELECT* 3 (cost=96,386.00..101,409.65 rows=182,678 width=784) (actual time=121.878..134.344 rows=23,978 loops=1)

156. 6.905 130.295 ↑ 7.6 23,978 1

Subquery Scan on t1 (cost=96,386.00..98,669.48 rows=182,678 width=776) (actual time=121.876..130.295 rows=23,978 loops=1)

157. 56.218 123.390 ↑ 7.6 23,978 1

Sort (cost=96,386.00..96,842.70 rows=182,678 width=784) (actual time=121.874..123.390 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
158. 67.172 67.172 ↑ 7.6 23,978 1

CTE Scan on data data_2 (cost=0.00..17,354.41 rows=182,678 width=784) (actual time=0.137..67.172 rows=23,978 loops=1)

Planning time : 485.977 ms
Execution time : 304,478.187 ms