explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vleM : Optimization for: LG4605J; SG4605J

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.448 39,062.874 ↑ 7.6 23,980 1

Append (cost=2,342,514.18..2,475,435.94 rows=182,680 width=784) (actual time=38,919.475..39,062.874 rows=23,980 loops=1)

2.          

CTE provider_accounts_list

3. 14.083 141.924 ↓ 5.0 145,152 1

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

4. 0.015 0.015 ↑ 1.0 1 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
6. 52.464 399.778 ↓ 4.0 48,384 3 / 3

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

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

8. 0.007 0.045 ↑ 1.0 22 3 / 3

Hash (cost=84.57..84.57 rows=22 width=8) (actual time=0.045..0.045 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.019..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. 15.265 38,862.168 ↑ 7.6 23,978 1

Merge Right Join (cost=2,270,223.42..2,274,313.69 rows=182,678 width=243) (actual time=38,734.747..38,862.168 rows=23,978 loops=1)

  • Merge Cond: (public_9.provider_account_id = provider_accounts_list.provider_account_id)
15. 59.770 2,963.937 ↓ 28.6 42,645 1

Unique (cost=91,593.39..91,608.28 rows=1,489 width=24) (actual time=2,874.534..2,963.937 rows=42,645 loops=1)

16. 160.124 2,904.167 ↓ 415.5 618,740 1

Sort (cost=91,593.39..91,597.11 rows=1,489 width=24) (actual time=2,874.532..2,904.167 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. 231.325 2,744.043 ↓ 415.5 618,740 1

WindowAgg (cost=91,485.14..91,514.92 rows=1,489 width=24) (actual time=2,478.956..2,744.043 rows=618,740 loops=1)

18. 292.037 2,512.718 ↓ 415.5 618,740 1

Sort (cost=91,485.14..91,488.86 rows=1,489 width=20) (actual time=2,478.948..2,512.718 rows=618,740 loops=1)

  • Sort Key: public_9.provider_account_id, public_10.created_at DESC
  • Sort Method: quicksort Memory: 72,916kB
19. 253.550 2,220.681 ↓ 415.5 618,740 1

WindowAgg (cost=91,369.44..91,406.67 rows=1,489 width=20) (actual time=1,817.936..2,220.681 rows=618,740 loops=1)

20. 104.667 1,967.131 ↓ 415.5 618,740 1

Group (cost=91,369.44..91,384.33 rows=1,489 width=16) (actual time=1,817.899..1,967.131 rows=618,740 loops=1)

  • Group Key: public_9.provider_account_id, public_10.total_amount_cents, public_10.created_at
21. 319.893 1,862.464 ↓ 415.5 618,740 1

Sort (cost=91,369.44..91,373.16 rows=1,489 width=16) (actual time=1,817.896..1,862.464 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. 97.511 1,542.571 ↓ 415.5 618,740 1

Nested Loop (cost=86,794.63..91,290.97 rows=1,489 width=16) (actual time=156.803..1,542.571 rows=618,740 loops=1)

23. 58.750 388.298 ↓ 56.7 58,709 1

Hash Join (cost=86,794.06..87,596.70 rows=1,036 width=16) (actual time=156.750..388.298 rows=58,709 loops=1)

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

25. 33.196 154.667 ↓ 6.7 192,118 1

Hash (cost=86,436.57..86,436.57 rows=28,599 width=12) (actual time=154.666..154.667 rows=192,118 loops=1)

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

Nested Loop (cost=862.20..86,436.57 rows=28,599 width=12) (actual time=23.151..121.471 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. 77.938 93.983 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_9 (cost=862.20..86,150.56 rows=28,599 width=16) (actual time=23.148..93.983 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.05 rows=28,599 width=0) (actual time=16.045..16.045 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_2.payday)
30. 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_10 (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_9.provider_account_id) AND (period_ends_at = public_9.ends_at))
  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 0
31. 8.473 35,882.966 ↑ 1.0 23,978 1

Materialize (cost=2,178,630.03..2,179,551.27 rows=24,537 width=231) (actual time=35,860.197..35,882.966 rows=23,978 loops=1)

32. 8.878 35,874.493 ↑ 1.0 23,978 1

Merge Left Join (cost=2,178,630.03..2,179,489.93 rows=24,537 width=231) (actual time=35,860.192..35,874.493 rows=23,978 loops=1)

  • Merge Cond: (provider_accounts_list.provider_account_id = public_26.provider_account_id)
33. 17.246 34,864.242 ↓ 85.6 23,978 1

Sort (cost=906,502.74..906,503.44 rows=280 width=215) (actual time=34,861.427..34,864.242 rows=23,978 loops=1)

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

Hash Left Join (cost=893,736.81..906,491.36 rows=280 width=215) (actual time=32,405.381..34,846.996 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = settlements_agg.pay_period_id)
35. 4.576 34,404.623 ↓ 85.6 23,978 1

Hash Left Join (cost=805,484.54..818,238.35 rows=280 width=179) (actual time=31,972.599..34,404.623 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = net_earnings_reported.pay_period_id)
36. 5.330 30,549.763 ↓ 85.6 23,978 1

Hash Left Join (cost=715,445.93..728,199.01 rows=280 width=175) (actual time=28,122.300..30,549.763 rows=23,978 loops=1)

  • Hash Cond: (public_7.id = pre_settlement_exclusions.pay_period_id)
37. 15.709 30,142.136 ↓ 85.6 23,978 1

Merge Left Join (cost=627,330.98..640,083.33 rows=280 width=143) (actual time=27,719.988..30,142.136 rows=23,978 loops=1)

  • Merge Cond: (public_6.id = public_15.user_id)
38. 22.144 3,269.238 ↓ 85.6 23,978 1

Sort (cost=274,238.87..274,239.57 rows=280 width=139) (actual time=3,265.616..3,269.238 rows=23,978 loops=1)

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

Hash Left Join (cost=267,588.91..274,227.48 rows=280 width=139) (actual time=2,284.229..3,247.094 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. 19.073 2,899.429 ↓ 152.5 58,709 1

Hash Left Join (cost=179,486.38..186,123.94 rows=385 width=131) (actual time=1,956.323..2,899.429 rows=58,709 loops=1)

  • Hash Cond: (public_7.id = advance_stats.pay_period_id)
41. 36.486 1,086.631 ↓ 152.5 58,709 1

Nested Loop (cost=86,881.49..93,518.04 rows=385 width=115) (actual time=162.583..1,086.631 rows=58,709 loops=1)

42. 25.845 874.018 ↓ 152.5 58,709 1

Nested Loop (cost=86,881.06..93,306.41 rows=385 width=108) (actual time=162.562..874.018 rows=58,709 loops=1)

43. 18.758 613.337 ↓ 152.5 58,709 1

Hash Join (cost=86,880.51..91,495.47 rows=385 width=94) (actual time=162.547..613.337 rows=58,709 loops=1)

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

Nested Loop (cost=86,794.77..91,408.71 rows=385 width=81) (actual time=162.054..594.104 rows=58,709 loops=1)

45. 36.131 446.068 ↓ 145.3 58,709 1

Nested Loop (cost=86,794.49..91,288.37 rows=404 width=70) (actual time=162.033..446.068 rows=58,709 loops=1)

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

Hash Join (cost=86,794.06..90,801.81 rows=1,036 width=16) (actual time=162.003..233.810 rows=58,709 loops=1)

  • Hash Cond: (provider_accounts_list.provider_account_id = public_7.provider_account_id)
47. 18.078 18.078 ↓ 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..18.078 rows=145,152 loops=1)

48. 33.396 161.865 ↓ 6.7 192,118 1

Hash (cost=86,436.57..86,436.57 rows=28,599 width=12) (actual time=161.865..161.865 rows=192,118 loops=1)

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

Nested Loop (cost=862.20..86,436.57 rows=28,599 width=12) (actual time=21.284..128.469 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. 76.921 91.229 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_7 (cost=862.20..86,150.56 rows=28,599 width=12) (actual time=21.279..91.229 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.05 rows=28,599 width=0) (actual time=14.308..14.308 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_1.payday)
53. 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)
54. 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)
55. 0.075 0.475 ↑ 1.1 393 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
56. 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.010..0.400 rows=393 loops=1)

57. 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)
58. 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)
59. 0.949 1,793.725 ↓ 7.8 7,049 1

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

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

Subquery Scan on advance_stats (cost=92,559.94..92,593.65 rows=899 width=20) (actual time=1,785.216..1,792.776 rows=7,049 loops=1)

61. 5.417 1,792.070 ↓ 7.8 7,049 1

GroupAggregate (cost=92,559.94..92,584.66 rows=899 width=20) (actual time=1,785.215..1,792.070 rows=7,049 loops=1)

  • Group Key: public_11.pay_period_id
62. 8.649 1,786.653 ↓ 26.0 23,376 1

Sort (cost=92,559.94..92,562.19 rows=899 width=15) (actual time=1,785.207..1,786.653 rows=23,376 loops=1)

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

Nested Loop (cost=86,794.63..92,515.84 rows=899 width=15) (actual time=147.425..1,778.004 rows=23,376 loops=1)

64. 50.855 215.267 ↓ 56.7 58,709 1

Hash Join (cost=86,794.06..87,596.70 rows=1,036 width=4) (actual time=146.839..215.267 rows=58,709 loops=1)

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

66. 31.319 146.817 ↓ 6.7 192,118 1

Hash (cost=86,436.57..86,436.57 rows=28,599 width=8) (actual time=146.817..146.817 rows=192,118 loops=1)

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

Nested Loop (cost=862.20..86,436.57 rows=28,599 width=8) (actual time=23.216..115.498 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. 77.616 93.671 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_12 (cost=862.20..86,150.56 rows=28,599 width=12) (actual time=23.212..93.671 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.05 rows=28,599 width=0) (actual time=16.055..16.055 rows=192,140 loops=1)

  • Index Cond: (payment_expected_on = payday_3.payday)
71. 1,526.434 1,526.434 ↓ 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.023..0.026 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.649 327.739 ↓ 54.8 11,625 1

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

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

Subquery Scan on provider_payments_list (cost=88,094.05..88,099.88 rows=212 width=12) (actual time=321.456..326.090 rows=11,625 loops=1)

74. 2.937 325.041 ↓ 54.8 11,625 1

GroupAggregate (cost=88,094.05..88,097.76 rows=212 width=12) (actual time=321.456..325.041 rows=11,625 loops=1)

  • Group Key: public_14.id
75. 4.175 322.104 ↓ 57.1 12,106 1

Sort (cost=88,094.05..88,094.58 rows=212 width=8) (actual time=321.450..322.104 rows=12,106 loops=1)

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

Nested Loop (cost=86,794.49..88,085.86 rows=212 width=8) (actual time=148.626..317.929 rows=12,106 loops=1)

77. 28.330 193.661 ↓ 56.7 58,709 1

Hash Join (cost=86,794.06..87,596.70 rows=1,036 width=4) (actual time=148.542..193.661 rows=58,709 loops=1)

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

79. 31.444 148.498 ↓ 6.7 192,118 1

Hash (cost=86,436.57..86,436.57 rows=28,599 width=8) (actual time=148.498..148.498 rows=192,118 loops=1)

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

Nested Loop (cost=862.20..86,436.57 rows=28,599 width=8) (actual time=22.952..117.054 rows=192,118 loops=1)

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

82. 77.858 93.900 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_14 (cost=862.20..86,150.56 rows=28,599 width=12) (actual time=22.947..93.900 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.05 rows=28,599 width=0) (actual time=16.042..16.042 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. 770.346 26,857.189 ↑ 11.8 51,604 1

Unique (cost=353,092.12..358,212.94 rows=610,341 width=20) (actual time=24,454.340..26,857.189 rows=51,604 loops=1)

86. 8,064.901 26,086.843 ↓ 12.3 12,645,408 1

Sort (cost=353,092.12..355,652.53 rows=1,024,165 width=20) (actual time=24,454.336..26,086.843 rows=12,645,408 loops=1)

  • Sort Key: public_15.user_id, public_15.created_at DESC
  • Sort Method: external merge Disk: 421,216kB
87. 2,827.101 18,021.942 ↓ 12.4 12,659,712 1

Nested Loop (cost=99,104.53..250,849.64 rows=1,024,165 width=20) (actual time=1,145.850..18,021.942 rows=12,659,712 loops=1)

88. 93.521 1,260.249 ↓ 5.0 145,152 1

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

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

90. 405.854 1,138.455 ↓ 1.0 2,051,509 1

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 96,522kB
91. 732.601 732.601 ↓ 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.008..732.601 rows=2,051,509 loops=1)

92. 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_15 (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_16.user_id)
93. 0.049 402.297 ↓ 39.2 235 1

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

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

Subquery Scan on pre_settlement_exclusions (cost=88,114.68..88,114.87 rows=6 width=36) (actual time=401.886..402.248 rows=235 loops=1)

95. 0.348 402.222 ↓ 39.2 235 1

GroupAggregate (cost=88,114.68..88,114.81 rows=6 width=36) (actual time=401.884..402.222 rows=235 loops=1)

  • Group Key: public_17.noteable_id
96. 0.172 401.874 ↓ 39.2 235 1

Sort (cost=88,114.68..88,114.69 rows=6 width=85) (actual time=401.859..401.874 rows=235 loops=1)

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

Nested Loop (cost=86,794.49..88,114.60 rows=6 width=85) (actual time=145.176..401.702 rows=235 loops=1)

98. 29.687 190.304 ↓ 56.7 58,709 1

Hash Join (cost=86,794.06..87,596.70 rows=1,036 width=4) (actual time=144.950..190.304 rows=58,709 loops=1)

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

100. 29.098 144.909 ↓ 6.7 192,118 1

Hash (cost=86,436.57..86,436.57 rows=28,599 width=8) (actual time=144.909..144.909 rows=192,118 loops=1)

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

Nested Loop (cost=862.20..86,436.57 rows=28,599 width=8) (actual time=20.628..115.811 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. 76.717 90.208 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_18 (cost=862.20..86,150.56 rows=28,599 width=12) (actual time=20.622..90.208 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.05 rows=28,599 width=0) (actual time=13.491..13.491 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 3,850.284 ↓ 0.0 0 1

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

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

Subquery Scan on net_earnings_reported (cost=90,038.57..90,038.60 rows=1 width=8) (actual time=3,850.284..3,850.284 rows=0 loops=1)

108. 0.001 3,850.283 ↓ 0.0 0 1

GroupAggregate (cost=90,038.57..90,038.59 rows=1 width=8) (actual time=3,850.283..3,850.283 rows=0 loops=1)

  • Group Key: public_19.id
109. 0.004 3,850.282 ↓ 0.0 0 1

Sort (cost=90,038.57..90,038.57 rows=1 width=8) (actual time=3,850.282..3,850.282 rows=0 loops=1)

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

Nested Loop (cost=86,795.22..90,038.56 rows=1 width=8) (actual time=3,850.278..3,850.278 rows=0 loops=1)

111. 52.732 3,850.278 ↓ 0.0 0 1

Nested Loop (cost=86,794.93..90,036.98 rows=5 width=12) (actual time=3,850.278..3,850.278 rows=0 loops=1)

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

Nested Loop (cost=86,794.49..88,070.31 rows=1,036 width=28) (actual time=151.812..451.133 rows=58,709 loops=1)

113. 60.627 232.290 ↓ 56.7 58,709 1

Hash Join (cost=86,794.06..87,596.70 rows=1,036 width=20) (actual time=151.769..232.290 rows=58,709 loops=1)

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

115. 33.624 151.636 ↓ 6.7 192,118 1

Hash (cost=86,436.57..86,436.57 rows=28,599 width=16) (actual time=151.636..151.636 rows=192,118 loops=1)

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

Nested Loop (cost=862.20..86,436.57 rows=28,599 width=16) (actual time=22.980..118.012 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. 76.724 92.345 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_19 (cost=862.20..86,150.56 rows=28,599 width=20) (actual time=22.974..92.345 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.05 rows=28,599 width=0) (actual time=15.621..15.621 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,346.413 3,346.413 ↓ 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.016..0.057 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.025 432.772 ↓ 529.3 11,645 1

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

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

Subquery Scan on settlements_agg (cost=88,251.28..88,252.00 rows=22 width=44) (actual time=413.970..430.747 rows=11,645 loops=1)

125. 14.795 429.576 ↓ 529.3 11,645 1

GroupAggregate (cost=88,251.28..88,251.78 rows=22 width=44) (actual time=413.969..429.576 rows=11,645 loops=1)

  • Group Key: public_23.pay_period_id
126. 5.271 414.781 ↓ 542.7 11,940 1

Sort (cost=88,251.28..88,251.34 rows=22 width=22) (actual time=413.941..414.781 rows=11,940 loops=1)

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

Nested Loop (cost=86,795.06..88,250.79 rows=22 width=22) (actual time=145.976..409.510 rows=11,940 loops=1)

128. 0.000 367.812 ↓ 542.7 11,940 1

Nested Loop (cost=86,794.50..88,147.79 rows=22 width=20) (actual time=145.952..367.812 rows=11,940 loops=1)

129. 31.135 193.835 ↓ 56.7 58,709 1

Hash Join (cost=86,794.06..87,596.70 rows=1,036 width=4) (actual time=145.838..193.835 rows=58,709 loops=1)

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

131. 30.832 145.792 ↓ 6.7 192,118 1

Hash (cost=86,436.57..86,436.57 rows=28,599 width=8) (actual time=145.792..145.792 rows=192,118 loops=1)

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

Nested Loop (cost=862.20..86,436.57 rows=28,599 width=8) (actual time=22.425..114.960 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. 76.855 92.269 ↓ 6.7 192,118 1

Bitmap Heap Scan on pay_periods public_25 (cost=862.20..86,150.56 rows=28,599 width=12) (actual time=22.419..92.269 rows=192,118 loops=1)

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

Bitmap Index Scan on index_pay_periods_on_payment_expected_on (cost=0.00..855.05 rows=28,599 width=0) (actual time=15.414..15.414 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.002..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.209 1,001.373 ↑ 4.1 4,251 1

GroupAggregate (cost=1,272,127.29..1,272,521.51 rows=17,520 width=20) (actual time=998.760..1,001.373 rows=4,251 loops=1)

  • Group Key: public_26.provider_account_id
139. 1.825 999.163 ↑ 3.3 5,260 1

Sort (cost=1,272,127.29..1,272,171.09 rows=17,520 width=12) (actual time=998.747..999.163 rows=5,260 loops=1)

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

Hash Join (cost=1,267,656.15..1,270,892.42 rows=17,520 width=12) (actual time=962.600..997.338 rows=5,271 loops=1)

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

142. 21.780 960.745 ↑ 5.5 87,453 1

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

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

Gather (cost=1,000.56..1,261,613.15 rows=483,440 width=12) (actual time=3.078..938.965 rows=87,453 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
144. 4.648 929.960 ↑ 6.9 29,151 3 / 3

Nested Loop (cost=0.56..1,212,269.15 rows=201,433 width=12) (actual time=0.575..929.960 rows=29,151 loops=3)

145. 808.645 808.645 ↑ 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.556..808.645 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.001..0.001 rows=1 loops=1)

149. 0.005 38,919.475 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.00..19,181.26 rows=1 width=784) (actual time=38,919.474..38,919.475 rows=1 loops=1)

150. 32.422 38,919.470 ↑ 1.0 1 1

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

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

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

152. 0.001 14.675 ↑ 1.0 1 1

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

153. 7.506 14.674 ↑ 1.0 1 1

GroupAggregate (cost=0.00..12,330.85 rows=1 width=776) (actual time=14.674..14.674 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.168 7.168 ↑ 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.002..7.168 rows=23,978 loops=1)

155. 3.948 127.276 ↑ 7.6 23,978 1

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

156. 6.833 123.328 ↑ 7.6 23,978 1

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

157. 52.089 116.495 ↑ 7.6 23,978 1

Sort (cost=96,386.00..96,842.70 rows=182,678 width=784) (actual time=114.965..116.495 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. 64.406 64.406 ↑ 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.032..64.406 rows=23,978 loops=1)

Planning time : 22.341 ms
Execution time : 39,174.244 ms