explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SZjw : Optimization for: Optimization for: LG4605J; SG4606DI; IJPP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.497 40,137.915 ↑ 210.8 23,980 1

Append (cost=2,446,479.45..6,244,991.49 rows=5,054,171 width=784) (actual time=39,992.054..40,137.915 rows=23,980 loops=1)

2.          

CTE provider_accounts_list

3. 13.516 132.426 ↓ 5.0 145,152 1

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
6. 52.651 380.317 ↓ 4.0 48,384 3 / 3

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

  • Hash Cond: (public.company_id = public_1.id)
7. 327.622 327.622 ↑ 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..327.622 rows=683,837 loops=3)

8. 0.006 0.044 ↑ 1.0 22 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.024 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.020..0.038 rows=22 loops=3)

  • Recheck Cond: (provider_id = 202)
  • Heap Blocks: exact=18
10. 0.014 0.014 ↑ 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.014..0.014 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. 8.924 39,934.239 ↑ 210.8 23,978 1

Hash Left Join (cost=2,362,365.43..2,378,278.96 rows=5,054,169 width=243) (actual time=39,727.466..39,934.239 rows=23,978 loops=1)

  • Hash Cond: (provider_accounts_list.provider_account_id = greatest_gross_earnings.provider_account_id)
15. 9.173 37,796.678 ↑ 7.6 23,978 1

Merge Left Join (cost=2,270,160.71..2,272,948.46 rows=182,923 width=235) (actual time=37,598.772..37,796.678 rows=23,978 loops=1)

  • Merge Cond: (provider_accounts_list.provider_account_id = public_26.provider_account_id)
16. 9.079 35,971.068 ↑ 1.0 23,978 1

Merge Left Join (cost=2,178,771.62..2,179,631.52 rows=24,537 width=231) (actual time=35,956.253..35,971.068 rows=23,978 loops=1)

  • Merge Cond: (provider_accounts_list.provider_account_id = public_24.provider_account_id)
17. 17.924 34,963.942 ↓ 85.6 23,978 1

Sort (cost=906,607.67..906,608.37 rows=280 width=215) (actual time=34,960.909..34,963.942 rows=23,978 loops=1)

  • Sort Key: public_3.id
  • Sort Method: quicksort Memory: 5,885kB
18. 10.121 34,946.018 ↓ 85.6 23,978 1

Hash Left Join (cost=893,841.73..906,596.28 rows=280 width=215) (actual time=32,461.043..34,946.018 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = settlements_agg.pay_period_id)
19. 4.754 34,506.327 ↓ 85.6 23,978 1

Hash Left Join (cost=805,573.51..818,327.33 rows=280 width=179) (actual time=32,031.458..34,506.327 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = net_earnings_reported.pay_period_id)
20. 5.515 30,663.783 ↓ 85.6 23,978 1

Hash Left Join (cost=715,517.12..728,270.20 rows=280 width=175) (actual time=28,193.658..30,663.783 rows=23,978 loops=1)

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

Merge Left Join (cost=627,386.25..640,138.60 rows=280 width=143) (actual time=27,783.922..30,248.545 rows=23,978 loops=1)

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

Sort (cost=274,290.58..274,291.28 rows=280 width=139) (actual time=3,532.106..3,535.852 rows=23,978 loops=1)

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

Hash Left Join (cost=267,640.15..274,279.20 rows=280 width=139) (actual time=2,512.344..3,514.043 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. 21.165 3,159.693 ↓ 152.5 58,709 1

Hash Left Join (cost=179,521.73..186,159.76 rows=385 width=131) (actual time=2,178.695..3,159.693 rows=58,709 loops=1)

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

Nested Loop (cost=86,896.91..93,533.93 rows=385 width=115) (actual time=171.672..1,131.517 rows=58,709 loops=1)

26. 54.795 917.087 ↓ 152.5 58,709 1

Nested Loop (cost=86,896.48..93,322.30 rows=385 width=108) (actual time=171.656..917.087 rows=58,709 loops=1)

27. 18.654 627.456 ↓ 152.5 58,709 1

Hash Join (cost=86,895.92..91,511.36 rows=385 width=94) (actual time=171.635..627.456 rows=58,709 loops=1)

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

Nested Loop (cost=86,810.18..91,424.60 rows=385 width=81) (actual time=171.152..608.328 rows=58,709 loops=1)

29. 37.634 459.307 ↓ 145.3 58,709 1

Nested Loop (cost=86,809.90..91,304.26 rows=404 width=70) (actual time=171.137..459.307 rows=58,709 loops=1)

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

Hash Join (cost=86,809.48..90,817.23 rows=1,037 width=16) (actual time=171.096..245.546 rows=58,709 loops=1)

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

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

32. 36.202 168.934 ↓ 6.7 192,118 1

Hash (cost=86,451.93..86,451.93 rows=28,604 width=12) (actual time=168.934..168.934 rows=192,118 loops=1)

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

Nested Loop (cost=862.24..86,451.93 rows=28,604 width=12) (actual time=22.570..132.732 rows=192,118 loops=1)

34. 0.003 0.003 ↑ 1.0 1 1

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

35. 80.741 96.364 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_7 (cost=862.24..86,165.87 rows=28,604 width=12) (actual time=22.566..96.364 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.09 rows=28,604 width=0) (actual time=15.623..15.623 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.074 0.474 ↑ 1.1 393 1

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

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

Seq Scan on providers public_4 (cost=0.00..80.33 rows=433 width=21) (actual time=0.006..0.400 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.065 2,007.011 ↓ 7.8 7,049 1

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

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

Subquery Scan on advance_stats (cost=92,579.87..92,613.58 rows=899 width=20) (actual time=1,998.132..2,005.946 rows=7,049 loops=1)

45. 5.557 2,005.250 ↓ 7.8 7,049 1

GroupAggregate (cost=92,579.87..92,604.59 rows=899 width=20) (actual time=1,998.131..2,005.250 rows=7,049 loops=1)

  • Group Key: public_9.pay_period_id
46. 8.345 1,999.693 ↓ 26.0 23,376 1

Sort (cost=92,579.87..92,582.12 rows=899 width=15) (actual time=1,998.123..1,999.693 rows=23,376 loops=1)

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

Nested Loop (cost=86,810.05..92,535.76 rows=899 width=15) (actual time=152.122..1,991.348 rows=23,376 loops=1)

48. 56.060 373.340 ↓ 56.6 58,709 1

Hash Join (cost=86,809.48..87,612.12 rows=1,037 width=4) (actual time=151.790..373.340 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_1.provider_account_id = public_10.provider_account_id)
49. 165.513 165.513 ↓ 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..165.513 rows=145,152 loops=1)

50. 32.998 151.767 ↓ 6.7 192,118 1

Hash (cost=86,451.93..86,451.93 rows=28,604 width=8) (actual time=151.767..151.767 rows=192,118 loops=1)

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

Nested Loop (cost=862.24..86,451.93 rows=28,604 width=8) (actual time=23.371..118.769 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.000..0.002 rows=1 loops=1)

53. 80.408 96.332 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_10 (cost=862.24..86,165.87 rows=28,604 width=12) (actual time=23.366..96.332 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.09 rows=28,604 width=0) (actual time=15.924..15.924 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.666 333.483 ↓ 54.8 11,625 1

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

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

Subquery Scan on provider_payments_list (cost=88,109.95..88,115.78 rows=212 width=12) (actual time=326.995..331.817 rows=11,625 loops=1)

58. 3.058 330.722 ↓ 54.8 11,625 1

GroupAggregate (cost=88,109.95..88,113.66 rows=212 width=12) (actual time=326.994..330.722 rows=11,625 loops=1)

  • Group Key: public_12.id
59. 4.305 327.664 ↓ 57.1 12,106 1

Sort (cost=88,109.95..88,110.48 rows=212 width=8) (actual time=326.986..327.664 rows=12,106 loops=1)

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

Nested Loop (cost=86,809.91..88,101.76 rows=212 width=8) (actual time=147.431..323.359 rows=12,106 loops=1)

61. 28.262 192.350 ↓ 56.6 58,709 1

Hash Join (cost=86,809.48..87,612.12 rows=1,037 width=4) (actual time=147.383..192.350 rows=58,709 loops=1)

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

63. 31.459 147.358 ↓ 6.7 192,118 1

Hash (cost=86,451.93..86,451.93 rows=28,604 width=8) (actual time=147.358..147.358 rows=192,118 loops=1)

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

Nested Loop (cost=862.24..86,451.93 rows=28,604 width=8) (actual time=22.804..115.899 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. 78.072 93.515 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_12 (cost=862.24..86,165.87 rows=28,604 width=12) (actual time=22.799..93.515 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.09 rows=28,604 width=0) (actual time=15.443..15.443 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. 767.476 26,696.929 ↑ 11.8 51,604 1

Unique (cost=353,095.67..358,216.50 rows=610,341 width=20) (actual time=24,251.786..26,696.929 rows=51,604 loops=1)

70. 8,122.034 25,929.453 ↓ 12.3 12,646,184 1

Sort (cost=353,095.67..355,656.09 rows=1,024,165 width=20) (actual time=24,251.782..25,929.453 rows=12,646,184 loops=1)

  • Sort Key: public_13.user_id, public_13.created_at DESC
  • Sort Method: external merge Disk: 421,240kB
71. 2,636.937 17,807.419 ↓ 12.4 12,660,509 1

Nested Loop (cost=99,104.53..250,853.20 rows=1,024,165 width=20) (actual time=1,121.134..17,807.419 rows=12,660,509 loops=1)

72. 95.242 1,235.890 ↓ 5.0 145,152 1

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

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

74. 416.091 1,113.718 ↓ 1.0 2,051,511 1

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

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

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

76. 13,934.592 13,934.592 ↑ 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.096 rows=87 loops=145,152)

  • Index Cond: (user_id = public_14.user_id)
77. 0.047 409.723 ↓ 39.2 235 1

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

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

Subquery Scan on pre_settlement_exclusions (cost=88,130.60..88,130.79 rows=6 width=36) (actual time=409.338..409.676 rows=235 loops=1)

79. 0.332 409.652 ↓ 39.2 235 1

GroupAggregate (cost=88,130.60..88,130.73 rows=6 width=36) (actual time=409.336..409.652 rows=235 loops=1)

  • Group Key: public_15.noteable_id
80. 0.168 409.320 ↓ 39.2 235 1

Sort (cost=88,130.60..88,130.61 rows=6 width=85) (actual time=409.306..409.320 rows=235 loops=1)

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

Nested Loop (cost=86,809.91..88,130.52 rows=6 width=85) (actual time=148.493..409.152 rows=235 loops=1)

82. 28.247 191.060 ↓ 56.6 58,709 1

Hash Join (cost=86,809.48..87,612.12 rows=1,037 width=4) (actual time=146.705..191.060 rows=58,709 loops=1)

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

84. 30.174 146.662 ↓ 6.7 192,118 1

Hash (cost=86,451.93..86,451.93 rows=28,604 width=8) (actual time=146.662..146.662 rows=192,118 loops=1)

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

Nested Loop (cost=862.24..86,451.93 rows=28,604 width=8) (actual time=20.617..116.488 rows=192,118 loops=1)

86. 0.003 0.003 ↑ 1.0 1 1

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

87. 76.872 90.570 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_16 (cost=862.24..86,165.87 rows=28,604 width=12) (actual time=20.610..90.570 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.09 rows=28,604 width=0) (actual time=13.698..13.698 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 3,837.790 ↓ 0.0 0 1

Hash (cost=90,056.37..90,056.37 rows=1 width=8) (actual time=3,837.790..3,837.790 rows=0 loops=1)

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

Subquery Scan on net_earnings_reported (cost=90,056.34..90,056.37 rows=1 width=8) (actual time=3,837.789..3,837.789 rows=0 loops=1)

92. 0.001 3,837.788 ↓ 0.0 0 1

GroupAggregate (cost=90,056.34..90,056.36 rows=1 width=8) (actual time=3,837.788..3,837.788 rows=0 loops=1)

  • Group Key: public_17.id
93. 0.003 3,837.787 ↓ 0.0 0 1

Sort (cost=90,056.34..90,056.35 rows=1 width=8) (actual time=3,837.787..3,837.787 rows=0 loops=1)

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

Nested Loop (cost=86,810.63..90,056.33 rows=1 width=8) (actual time=3,837.784..3,837.784 rows=0 loops=1)

95. 86.785 3,837.783 ↓ 0.0 0 1

Nested Loop (cost=86,810.34..90,054.76 rows=5 width=12) (actual time=3,837.783..3,837.783 rows=0 loops=1)

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

Nested Loop (cost=86,809.90..88,086.19 rows=1,037 width=28) (actual time=156.792..463.294 rows=58,709 loops=1)

97. 63.199 239.338 ↓ 56.6 58,709 1

Hash Join (cost=86,809.48..87,612.12 rows=1,037 width=20) (actual time=156.762..239.338 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_5.provider_account_id = public_17.provider_account_id)
98. 19.512 19.512 ↓ 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.512 rows=145,152 loops=1)

99. 34.515 156.627 ↓ 6.7 192,118 1

Hash (cost=86,451.93..86,451.93 rows=28,604 width=16) (actual time=156.627..156.627 rows=192,118 loops=1)

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

Nested Loop (cost=862.24..86,451.93 rows=28,604 width=16) (actual time=23.610..122.112 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. 79.308 95.532 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_17 (cost=862.24..86,165.87 rows=28,604 width=20) (actual time=23.604..95.532 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.09 rows=28,604 width=0) (actual time=16.224..16.224 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,287.704 3,287.704 ↓ 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.016..0.056 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.019 429.570 ↓ 529.3 11,645 1

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

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

Subquery Scan on settlements_agg (cost=88,267.23..88,267.95 rows=22 width=44) (actual time=410.762..427.551 rows=11,645 loops=1)

109. 14.719 426.346 ↓ 529.3 11,645 1

GroupAggregate (cost=88,267.23..88,267.73 rows=22 width=44) (actual time=410.761..426.346 rows=11,645 loops=1)

  • Group Key: public_21.pay_period_id
110. 5.303 411.627 ↓ 542.7 11,940 1

Sort (cost=88,267.23..88,267.29 rows=22 width=22) (actual time=410.733..411.627 rows=11,940 loops=1)

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

Nested Loop (cost=86,810.48..88,266.74 rows=22 width=22) (actual time=147.246..406.324 rows=11,940 loops=1)

112. 50.997 365.048 ↓ 542.7 11,940 1

Nested Loop (cost=86,809.91..88,163.74 rows=22 width=20) (actual time=147.226..365.048 rows=11,940 loops=1)

113. 32.707 196.633 ↓ 56.6 58,709 1

Hash Join (cost=86,809.48..87,612.12 rows=1,037 width=4) (actual time=147.142..196.633 rows=58,709 loops=1)

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

115. 31.121 147.101 ↓ 6.7 192,118 1

Hash (cost=86,451.93..86,451.93 rows=28,604 width=8) (actual time=147.101..147.101 rows=192,118 loops=1)

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

Nested Loop (cost=862.24..86,451.93 rows=28,604 width=8) (actual time=22.451..115.980 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.061 92.468 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_23 (cost=862.24..86,165.87 rows=28,604 width=12) (actual time=22.446..92.468 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.09 rows=28,604 width=0) (actual time=15.407..15.407 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_6.payday)
120. 117.418 117.418 ↓ 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.002 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.237 998.047 ↑ 4.1 4,246 1

GroupAggregate (cost=1,272,163.95..1,272,558.18 rows=17,520 width=20) (actual time=995.338..998.047 rows=4,246 loops=1)

  • Group Key: public_24.provider_account_id
123. 1.935 995.809 ↑ 3.3 5,255 1

Sort (cost=1,272,163.95..1,272,207.75 rows=17,520 width=12) (actual time=995.323..995.809 rows=5,255 loops=1)

  • Sort Key: public_24.provider_account_id
  • Sort Method: quicksort Memory: 439kB
124. 23.104 993.874 ↑ 3.3 5,266 1

Hash Join (cost=1,267,692.82..1,270,929.08 rows=17,520 width=12) (actual time=958.976..993.874 rows=5,266 loops=1)

  • Hash Cond: (provider_accounts_list_7.provider_account_id = public_24.provider_account_id)
125. 13.675 13.675 ↓ 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.675 rows=145,152 loops=1)

126. 21.634 957.095 ↑ 5.5 87,448 1

Hash (cost=1,261,649.82..1,261,649.82 rows=483,440 width=12) (actual time=957.095..957.095 rows=87,448 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 7,854kB
127. 8.576 935.461 ↑ 5.5 87,448 1

Gather (cost=1,000.56..1,261,649.82 rows=483,440 width=12) (actual time=3.208..935.461 rows=87,448 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
128. 2.953 926.885 ↑ 6.9 29,149 3 / 3

Nested Loop (cost=0.56..1,212,305.82 rows=201,433 width=12) (actual time=0.568..926.885 rows=29,149 loops=3)

129. 807.272 807.272 ↑ 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.549..807.272 rows=29,165 loops=3)

  • Filter: ((total_amount_cents <> 0) AND ((status)::text = 'unprocessed'::text))
  • Rows Removed by Filter: 7,106,237
130. 116.660 116.660 ↑ 1.0 1 87,495 / 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,495)

  • 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.971 1,816.437 ↓ 28.6 42,645 1

Unique (cost=91,389.09..91,407.73 rows=1,491 width=16) (actual time=1,642.503..1,816.437 rows=42,645 loops=1)

134. 104.674 1,781.466 ↓ 415.0 618,740 1

Group (cost=91,389.09..91,404.00 rows=1,491 width=16) (actual time=1,642.501..1,781.466 rows=618,740 loops=1)

  • Group Key: public_26.provider_account_id, public_27.created_at, public_27.total_amount_cents
135. 307.226 1,676.792 ↓ 415.0 618,740 1

Sort (cost=91,389.09..91,392.82 rows=1,491 width=16) (actual time=1,642.495..1,676.792 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. 90.727 1,369.566 ↓ 415.0 618,740 1

Nested Loop (cost=86,810.05..91,310.50 rows=1,491 width=16) (actual time=152.595..1,369.566 rows=618,740 loops=1)

137. 52.165 222.077 ↓ 56.6 58,709 1

Hash Join (cost=86,809.48..87,612.12 rows=1,037 width=16) (actual time=152.549..222.077 rows=58,709 loops=1)

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

139. 32.560 152.439 ↓ 6.7 192,118 1

Hash (cost=86,451.93..86,451.93 rows=28,604 width=12) (actual time=152.439..152.439 rows=192,118 loops=1)

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

Nested Loop (cost=862.24..86,451.93 rows=28,604 width=12) (actual time=23.263..119.879 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. 77.733 93.678 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_26 (cost=862.24..86,165.87 rows=28,604 width=16) (actual time=23.258..93.678 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.09 rows=28,604 width=0) (actual time=15.945..15.945 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.523 2,128.637 ↓ 7.7 42,645 1

Hash (cost=92,135.64..92,135.64 rows=5,526 width=8) (actual time=2,128.637..2,128.637 rows=42,645 loops=1)

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,178kB
146. 4.362 2,122.114 ↓ 7.7 42,645 1

Subquery Scan on greatest_gross_earnings (cost=92,052.75..92,135.64 rows=5,526 width=8) (actual time=2,043.595..2,122.114 rows=42,645 loops=1)

147. 39.443 2,117.752 ↓ 7.7 42,645 1

Unique (cost=92,052.75..92,080.38 rows=5,526 width=20) (actual time=2,043.593..2,117.752 rows=42,645 loops=1)

148. 277.307 2,078.309 ↓ 112.0 618,740 1

Sort (cost=92,052.75..92,066.57 rows=5,526 width=20) (actual time=2,043.591..2,078.309 rows=618,740 loops=1)

  • Sort Key: public_29.provider_account_id, public_28.total_amount_cents DESC
  • Sort Method: quicksort Memory: 72,916kB
149. 106.617 1,801.002 ↓ 112.0 618,740 1

Group (cost=91,654.00..91,709.26 rows=5,526 width=20) (actual time=1,652.572..1,801.002 rows=618,740 loops=1)

  • Group Key: public_29.provider_account_id, public_28.total_amount_cents, public_28.created_at
150. 322.601 1,694.385 ↓ 112.0 618,740 1

Sort (cost=91,654.00..91,667.81 rows=5,526 width=16) (actual time=1,652.565..1,694.385 rows=618,740 loops=1)

  • Sort Key: public_29.provider_account_id, public_28.total_amount_cents, public_28.created_at
  • Sort Method: quicksort Memory: 53,580kB
151. 91.319 1,371.784 ↓ 112.0 618,740 1

Nested Loop (cost=86,810.05..91,310.50 rows=5,526 width=16) (actual time=151.705..1,371.784 rows=618,740 loops=1)

152. 54.489 223.703 ↓ 56.6 58,709 1

Hash Join (cost=86,809.48..87,612.12 rows=1,037 width=16) (actual time=151.658..223.703 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list_9.provider_account_id = public_29.provider_account_id)
153. 17.607 17.607 ↓ 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.607 rows=145,152 loops=1)

154. 32.411 151.607 ↓ 6.7 192,118 1

Hash (cost=86,451.93..86,451.93 rows=28,604 width=12) (actual time=151.607..151.607 rows=192,118 loops=1)

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

Nested Loop (cost=862.24..86,451.93 rows=28,604 width=12) (actual time=22.047..119.196 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. 77.227 92.215 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_29 (cost=862.24..86,165.87 rows=28,604 width=16) (actual time=22.040..92.215 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.09 rows=28,604 width=0) (actual time=14.988..14.988 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_28 (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_29.provider_account_id) AND (period_ends_at = public_29.ends_at))
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 0
160. 0.004 39,992.054 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..530,687.81 rows=1 width=784) (actual time=39,992.054..39,992.054 rows=1 loops=1)

161. 32.485 39,992.050 ↑ 1.0 1 1

GroupAggregate (cost=0.00..530,687.78 rows=1 width=520) (actual time=39,992.050..39,992.050 rows=1 loops=1)

  • Group Key: 'Count'::character varying, NULL::text, NULL::text, NULL::text, NULL::text
162. 39,959.565 39,959.565 ↑ 210.8 23,978 1

CTE Scan on data (cost=0.00..101,083.38 rows=5,054,169 width=368) (actual time=39,727.472..39,959.565 rows=23,978 loops=1)

163. 0.002 14.742 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..341,156.51 rows=1 width=784) (actual time=14.741..14.742 rows=1 loops=1)

164. 7.833 14.740 ↑ 1.0 1 1

GroupAggregate (cost=0.00..341,156.49 rows=1 width=776) (actual time=14.740..14.740 rows=1 loops=1)

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

CTE Scan on data data_1 (cost=0.00..101,083.38 rows=5,054,169 width=300) (actual time=0.002..6.907 rows=23,978 loops=1)

166. 4.117 129.622 ↑ 210.8 23,978 1

Subquery Scan on *SELECT* 3 (cost=2,787,678.08..2,926,667.72 rows=5,054,169 width=784) (actual time=116.974..129.622 rows=23,978 loops=1)

167. 7.020 125.505 ↑ 210.8 23,978 1

Subquery Scan on t1 (cost=2,787,678.08..2,850,855.19 rows=5,054,169 width=776) (actual time=116.971..125.505 rows=23,978 loops=1)

168. 53.992 118.485 ↑ 210.8 23,978 1

Sort (cost=2,787,678.08..2,800,313.50 rows=5,054,169 width=784) (actual time=116.970..118.485 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. 64.493 64.493 ↑ 210.8 23,978 1

CTE Scan on data data_2 (cost=0.00..480,146.05 rows=5,054,169 width=784) (actual time=0.019..64.493 rows=23,978 loops=1)

Planning time : 27.199 ms
Execution time : 40,250.547 ms