explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xSbm : Optimization for: Optimization for: Optimization for: plan #TeKD; plan #aMrH; plan #0vt9

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7,366.771 58,007.129 ↓ 2.4 846,555 1

Merge Left Join (cost=3,312,860.45..3,320,679.19 rows=360,070 width=140) (actual time=47,611.442..58,007.129 rows=846,555 loops=1)

  • Merge Cond: ((projects.account_id = exchange_rates_by_months.account_id) AND (("*SELECT* 1".currency)::text = (exchange_rates_by_months.currency)::text))
  • Join Filter: ((exchange_rates_by_months.start_date <= "*SELECT* 1_1".date) AND (exchange_rates_by_months.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 40566262
  • Buffers: shared hit=4191143 read=6672
2. 575.913 47,680.926 ↓ 2.4 846,555 1

Sort (cost=3,312,615.59..3,313,515.77 rows=360,070 width=136) (actual time=47,609.973..47,680.926 rows=846,555 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 170476kB
  • Buffers: shared hit=4191105 read=6672
3. 273.768 47,105.013 ↓ 2.4 846,555 1

Merge Left Join (cost=3,204,030.76..3,279,384.88 rows=360,070 width=136) (actual time=46,154.612..47,105.013 rows=846,555 loops=1)

  • Merge Cond: (projects.id = discounts.project_id)
  • Join Filter: (("*SELECT* 1_1".date >= discounts.start_date) AND ("*SELECT* 1_1".date <= discounts.end_date) AND (((discounts.discountable_id = "*SELECT* 1_1".user_id) AND ((discounts.discountable_type)::text = 'User'::text)) OR ((discounts.discountable_id = "*SELECT* 1_1".epic_id) AND ((discounts.discountable_type)::text = 'Epic'::text))))
  • Rows Removed by Join Filter: 653458
  • Buffers: shared hit=4191105 read=6672
4. 474.296 46,790.789 ↓ 2.4 846,467 1

Merge Left Join (cost=3,204,008.00..3,261,934.73 rows=360,070 width=132) (actual time=46,154.437..46,790.789 rows=846,467 loops=1)

  • Merge Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)) = ""*SELECT* 1"".activity_id))
  • Join Filter: (("*SELECT* 1_1".date >= "*SELECT* 1".start_date) AND (("*SELECT* 1_1".date <= "*SELECT* 1".end_date) OR ("*SELECT* 1".end_date IS NULL)))
  • Rows Removed by Join Filter: 244719
  • Buffers: shared hit=4191101 read=6672
5. 545.464 45,708.534 ↓ 2.4 846,467 1

Sort (cost=3,157,177.08..3,158,077.26 rows=360,070 width=104) (actual time=45,630.833..45,708.534 rows=846,467 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 143611kB
  • Buffers: shared hit=4182421 read=6672
6. 226.537 45,163.070 ↓ 2.4 846,467 1

Hash Join (cost=2,268,999.86..3,123,946.37 rows=360,070 width=104) (actual time=34,767.116..45,163.070 rows=846,467 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=4182421 read=6672
7. 136.346 44,936.413 ↓ 1.0 1,112,574 1

Append (cost=2,268,981.19..3,110,256.09 rows=1,080,211 width=104) (actual time=34,765.683..44,936.413 rows=1,112,574 loops=1)

  • Buffers: shared hit=4182410 read=6672
8. 6,733.259 43,934.432 ↓ 1.9 1,095,353 1

Merge Left Join (cost=2,268,981.19..2,772,726.18 rows=590,704 width=104) (actual time=34,765.682..43,934.432 rows=1,095,353 loops=1)

  • Merge Cond: ("*SELECT* 1_1".project_plan_id = pa.project_plan_id)
  • Join Filter: (("*SELECT* 1_1".date >= pa.start_date) AND ("*SELECT* 1_1".date <= pa.end_date) AND ((pa.plan_row_uuid = "*SELECT* 1_1".plan_row_uuid) OR ("*SELECT* 1_1".plan_row_uuid IS NULL)) AND ((pa.user_id = "*SELECT* 1_1".user_id) OR ("*SELECT* 1_1".user_id IS NULL)) AND ((pa.epic_id = "*SELECT* 1_1".epic_id) OR ((('PlanItem'::text) = 'PlanItem'::text) AND ("*SELECT* 1_1".epic_id IS NULL)) OR ((('PlanItem'::text) = 'TimeLog'::text) AND (pa.epic_id IS NULL))))
  • Rows Removed by Join Filter: 35962723
  • Buffers: shared hit=3063692 read=6672
9. 696.620 34,729.167 ↓ 1.9 1,095,353 1

Sort (cost=2,259,415.21..2,260,891.97 rows=590,704 width=153) (actual time=34,585.344..34,729.167 rows=1,095,353 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: quicksort Memory: 229016kB
  • Buffers: shared hit=3062703 read=6672
10. 7,354.137 34,032.547 ↓ 1.9 1,095,353 1

Merge Left Join (cost=2,197,347.05..2,202,790.10 rows=590,704 width=153) (actual time=23,791.334..34,032.547 rows=1,095,353 loops=1)

  • Merge Cond: ((staff_memberships_1.account_id = cost_exchange_rates.account_id) AND ((staff_memberships_1.currency)::text = (cost_exchange_rates.currency)::text))
  • Join Filter: ((cost_exchange_rates.start_date <= "*SELECT* 1_1".date) AND (cost_exchange_rates.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 42756958
  • Buffers: shared hit=3062703 read=6672
11. 882.986 23,870.906 ↓ 1.9 1,095,353 1

Sort (cost=2,197,102.19..2,198,578.95 rows=590,704 width=153) (actual time=23,789.789..23,870.906 rows=1,095,353 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 229789kB
  • Buffers: shared hit=3062665 read=6672
12. 285.219 22,987.920 ↓ 1.9 1,095,353 1

Hash Left Join (cost=2,119,038.00..2,140,477.08 rows=590,704 width=153) (actual time=21,950.570..22,987.920 rows=1,095,353 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: ((((""*SELECT* 1_1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 5453
  • Buffers: shared hit=3062665 read=6672
13. 525.993 22,702.543 ↑ 1.6 1,100,806 1

Merge Left Join (cost=2,119,019.03..2,135,772.46 rows=1,754,565 width=148) (actual time=21,950.400..22,702.543 rows=1,100,806 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships_1.account_id) AND ("*SELECT* 1_1".user_id = staff_memberships_1.user_id))
  • Join Filter: ((""*SELECT* 1_1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at))) AND (""*SELECT* 1_1"".date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 258899
  • Buffers: shared hit=3062654 read=6672
14. 1,169.540 22,079.197 ↑ 1.6 1,100,806 1

Sort (cost=2,118,171.89..2,122,558.31 rows=1,754,565 width=120) (actual time=21,938.100..22,079.197 rows=1,100,806 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 198687kB
  • Buffers: shared hit=3062569 read=6672
15. 326.176 20,909.657 ↑ 1.6 1,100,806 1

Hash Join (cost=1,673,625.69..1,936,199.97 rows=1,754,565 width=120) (actual time=1,769.608..20,909.657 rows=1,100,806 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=3062569 read=6672
16. 188.918 20,581.611 ↑ 1.1 1,597,377 1

Append (cost=1,673,396.92..1,913,816.46 rows=1,754,565 width=116) (actual time=1,625.161..20,581.611 rows=1,597,377 loops=1)

  • Buffers: shared hit=3062471 read=6672
17. 271.987 20,006.559 ↑ 1.2 893,416 1

Result (cost=1,673,396.92..1,866,783.57 rows=1,050,604 width=116) (actual time=1,625.160..20,006.559 rows=893,416 loops=1)

  • Buffers: shared hit=3059527
18. 101.447 19,734.572 ↑ 1.2 893,416 1

Append (cost=1,673,396.92..1,853,651.02 rows=1,050,604 width=140) (actual time=1,625.157..19,734.572 rows=893,416 loops=1)

  • Buffers: shared hit=3059527
19. 112.620 2,315.301 ↑ 1.5 661,732 1

Subquery Scan on *SELECT* 1_1 (cost=1,673,396.92..1,716,730.33 rows=968,516 width=114) (actual time=1,625.156..2,315.301 rows=661,732 loops=1)

  • Buffers: shared hit=2465
20. 165.326 2,202.681 ↑ 1.5 661,732 1

Merge Left Join (cost=1,673,396.92..1,702,202.59 rows=968,516 width=134) (actual time=1,625.154..2,202.681 rows=661,732 loops=1)

  • Merge Cond: ((generate_series.generate_series = summary_vacations_by_dates.date) AND (projects_1.account_id = summary_vacations_by_dates.account_id) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
  • Buffers: shared hit=2465
21. 191.163 1,924.777 ↑ 1.5 661,732 1

Merge Anti Join (cost=1,066,799.64..1,080,109.02 rows=968,516 width=66) (actual time=1,625.151..1,924.777 rows=661,732 loops=1)

  • Merge Cond: ((generate_series.generate_series = non_working_intervals_by_dates.date) AND (projects_1.account_id = non_working_intervals_by_dates.account_id) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
  • Buffers: shared hit=2134
22. 1,110.349 1,694.257 ↑ 1.8 669,378 1

Sort (cost=1,060,781.65..1,063,848.65 rows=1,226,800 width=66) (actual time=1,625.147..1,694.257 rows=669,378 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: quicksort Memory: 118708kB
  • Buffers: shared hit=1450
23. 115.704 583.908 ↑ 1.8 669,378 1

Nested Loop (cost=1,991.21..936,712.49 rows=1,226,800 width=66) (actual time=23.546..583.908 rows=669,378 loops=1)

  • Buffers: shared hit=1450
24. 13.864 85.900 ↑ 1.0 47,788 1

Hash Left Join (cost=1,991.21..4,344.48 rows=49,072 width=66) (actual time=23.517..85.900 rows=47,788 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
  • Buffers: shared hit=1450
25. 12.485 70.053 ↑ 1.0 47,788 1

Hash Left Join (cost=1,762.44..3,986.81 rows=49,072 width=62) (actual time=21.491..70.053 rows=47,788 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=1352
26. 24.217 55.889 ↑ 1.0 47,788 1

Hash Join (cost=1,560.41..3,655.88 rows=49,072 width=58) (actual time=19.778..55.889 rows=47,788 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
  • Buffers: shared hit=1246
27. 12.155 12.155 ↓ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.007..12.155 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
28. 10.242 19.517 ↑ 1.0 42,344 1

Hash (cost=1,030.06..1,030.06 rows=42,428 width=28) (actual time=19.517..19.517 rows=42,344 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2816kB
  • Buffers: shared hit=549
29. 9.275 9.275 ↑ 1.0 42,344 1

Seq Scan on plan_rows (cost=0.00..1,030.06 rows=42,428 width=28) (actual time=0.007..9.275 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
  • Buffers: shared hit=549
30. 0.503 1.679 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.679..1.679 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
31. 1.176 1.176 ↑ 1.0 2,851 1

Seq Scan on project_plans (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.011..1.176 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
32. 1.046 1.983 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=1.982..1.983 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
  • Buffers: shared hit=98
33. 0.937 0.937 ↑ 1.0 5,812 1

Seq Scan on projects projects_1 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.006..0.937 rows=5,812 loops=1)

  • Buffers: shared hit=98
34. 382.304 382.304 ↑ 1.8 14 47,788

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.003..0.008 rows=14 loops=47,788)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
35. 29.745 39.357 ↑ 1.0 59,712 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=34.943..39.357 rows=59,712 loops=1)

  • Sort Key: non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4336kB
  • Buffers: shared hit=684
36. 9.612 9.612 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.013..9.612 rows=59,712 loops=1)

  • Buffers: shared hit=684
37. 29.304 112.578 ↑ 15.5 37,179 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=107.222..112.578 rows=37,179 loops=1)

  • Sort Key: summary_vacations_by_dates.date, summary_vacations_by_dates.account_id, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2844kB
  • Buffers: shared hit=331
38. 4.740 83.274 ↑ 17.8 32,266 1

Subquery Scan on summary_vacations_by_dates (cost=537,214.32..551,589.32 rows=575,000 width=44) (actual time=62.012..83.274 rows=32,266 loops=1)

  • Buffers: shared hit=331
39. 31.545 78.534 ↑ 17.8 32,266 1

HashAggregate (cost=537,214.32..545,839.32 rows=575,000 width=52) (actual time=62.010..78.534 rows=32,266 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=331
40. 10.991 46.989 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.543..46.989 rows=32,422 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
  • Join Filter: ((generate_series_1.generate_series >= staff_memberships.joined_at) AND ((staff_memberships.archived_at IS NULL) OR (generate_series_1.generate_series <= staff_memberships.archived_at)))
  • Rows Removed by Join Filter: 1908
  • Buffers: shared hit=331
41. 10.485 34.488 ↑ 634.8 34,330 1

Nested Loop (cost=0.01..436,340.93 rows=21,792,000 width=17) (actual time=0.024..34.488 rows=34,330 loops=1)

  • Buffers: shared hit=283
42. 2.211 2.211 ↑ 1.0 21,792 1

Seq Scan on vacations (cost=0.00..500.92 rows=21,792 width=17) (actual time=0.013..2.211 rows=21,792 loops=1)

  • Buffers: shared hit=283
43. 21.792 21.792 ↑ 500.0 2 21,792

Function Scan on generate_series generate_series_1 (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.001..0.001 rows=2 loops=21,792)

44. 0.786 1.510 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=20) (actual time=1.510..1.510 rows=4,002 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=48
45. 0.724 0.724 ↑ 1.0 4,002 1

Seq Scan on staff_memberships (cost=0.00..88.02 rows=4,002 width=20) (actual time=0.005..0.724 rows=4,002 loops=1)

  • Buffers: shared hit=48
46. 110.388 17,317.824 ↓ 2.8 231,684 1

Hash Left Join (cost=1,215.85..130,846.80 rows=82,088 width=114) (actual time=6.774..17,317.824 rows=231,684 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 770
  • Buffers: shared hit=3057062
47. 51.458 236.105 ↓ 1.4 232,454 1

Nested Loop (cost=987.08..127,447.75 rows=164,175 width=62) (actual time=4.542..236.105 rows=232,454 loops=1)

  • Buffers: shared hit=1251
48. 3.081 35.003 ↓ 1.2 7,876 1

Hash Left Join (cost=987.08..2,674.74 rows=6,567 width=62) (actual time=4.519..35.003 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
  • Buffers: shared hit=1251
49. 15.948 30.383 ↓ 1.2 7,876 1

Hash Join (cost=785.05..2,455.47 rows=6,567 width=58) (actual time=2.957..30.383 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_1.uuid)
  • Buffers: shared hit=1145
50. 11.545 11.545 ↓ 1.0 55,664 1

Seq Scan on plan_items plan_items_1 (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.015..11.545 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
51. 1.184 2.890 ↓ 1.0 5,762 1

Hash (cost=714.07..714.07 rows=5,678 width=28) (actual time=2.890..2.890 rows=5,762 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
  • Buffers: shared hit=448
52. 1.510 1.706 ↓ 1.0 5,762 1

Bitmap Heap Scan on plan_rows plan_rows_1 (cost=108.29..714.07 rows=5,678 width=28) (actual time=0.234..1.706 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
  • Buffers: shared hit=448
53. 0.196 0.196 ↓ 1.0 5,762 1

Bitmap Index Scan on index_plan_rows_on_user_id (cost=0.00..106.88 rows=5,678 width=0) (actual time=0.196..0.196 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
  • Buffers: shared hit=18
54. 0.483 1.539 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.539..1.539 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
55. 1.056 1.056 ↑ 1.0 2,851 1

Seq Scan on project_plans project_plans_1 (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.008..1.056 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
56. 149.644 149.644 ↓ 1.2 30 7,876

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.007..0.019 rows=30 loops=7,876)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 12
57. 1.017 2.189 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=12) (actual time=2.189..2.189 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
  • Buffers: shared hit=98
58. 1.172 1.172 ↑ 1.0 5,812 1

Seq Scan on projects projects_1_1 (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.006..1.172 rows=5,812 loops=1)

  • Buffers: shared hit=98
59.          

SubPlan (for Hash Left Join)

60. 217.118 16,969.142 ↓ 0.0 0 232,454

Nested Loop (cost=0.69..90.49 rows=1 width=0) (actual time=0.073..0.073 rows=0 loops=232,454)

  • Join Filter: (non_working_intervals_by_dates_2.office_id = clients_3.office_id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=3055713
61. 0.000 0.000 ↓ 0.0 0 232,454

Index Scan using clients_pkey on clients clients_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=232,454)

  • Index Cond: (projects_1_1.client_id = id)
  • Buffers: shared hit=177008
62. 16,752.024 16,752.024 ↑ 3.7 3 58,986

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..82.06 rows=11 width=4) (actual time=0.277..0.284 rows=3 loops=58,986)

  • Index Cond: ((projects_1_1.account_id = account_id) AND (generate_series_2.generate_series = date))
  • Buffers: shared hit=2878705
63. 275.872 386.134 ↑ 1.0 703,961 1

Hash Join (cost=1,826.58..31,220.45 rows=703,961 width=116) (actual time=16.322..386.134 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=2944 read=6672
64. 93.990 93.990 ↑ 1.0 703,961 1

Seq Scan on time_logs (cost=0.00..16,194.61 rows=703,961 width=28) (actual time=0.010..93.990 rows=703,961 loops=1)

  • Buffers: shared hit=2483 read=6672
65. 5.100 16.272 ↑ 1.0 26,435 1

Hash (cost=1,496.14..1,496.14 rows=26,435 width=16) (actual time=16.272..16.272 rows=26,435 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
  • Buffers: shared hit=461
66. 7.187 11.172 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.483..11.172 rows=26,435 loops=1)

  • Hash Cond: (epics.project_id = general_epic_plans.project_id)
  • Join Filter: (epics.project_plan_id IS NULL)
  • Rows Removed by Join Filter: 11144
  • Buffers: shared hit=461
67. 2.534 2.534 ↑ 1.0 26,435 1

Seq Scan on epics (cost=0.00..619.35 rows=26,435 width=12) (actual time=0.005..2.534 rows=26,435 loops=1)

  • Buffers: shared hit=355
68. 0.447 1.451 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.451..1.451 rows=2,851 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
69. 1.004 1.004 ↑ 1.0 2,851 1

Seq Scan on project_plans general_epic_plans (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.006..1.004 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
70. 0.927 1.870 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=1.870..1.870 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
  • Buffers: shared hit=98
71. 0.943 0.943 ↑ 1.0 5,812 1

Seq Scan on projects (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.006..0.943 rows=5,812 loops=1)

  • Buffers: shared hit=98
72. 83.124 97.353 ↓ 295.4 1,255,026 1

Materialize (cost=847.14..942.74 rows=4,249 width=40) (actual time=12.293..97.353 rows=1,255,026 loops=1)

  • Buffers: shared hit=85
73. 1.361 14.229 ↑ 1.0 4,218 1

Unique (cost=847.14..889.63 rows=4,249 width=65) (actual time=12.289..14.229 rows=4,218 loops=1)

  • Buffers: shared hit=85
74. 3.368 12.868 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=12.288..12.868 rows=4,246 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.user_id, (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at)), (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 790kB
  • Buffers: shared hit=85
75. 3.139 9.500 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..591.08 rows=4,249 width=65) (actual time=6.017..9.500 rows=4,249 loops=1)

  • Buffers: shared hit=85
76. 2.440 6.361 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=6.006..6.361 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 790kB
  • Buffers: shared hit=85
77. 1.547 3.921 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=56) (actual time=1.965..3.921 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
  • Buffers: shared hit=85
78. 0.461 0.461 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links (cost=0.00..79.56 rows=4,256 width=28) (actual time=0.015..0.461 rows=4,256 loops=1)

  • Buffers: shared hit=37
79. 1.036 1.913 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=32) (actual time=1.913..1.913 rows=4,002 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
  • Buffers: shared hit=48
80. 0.877 0.877 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..88.02 rows=4,002 width=32) (actual time=0.007..0.877 rows=4,002 loops=1)

  • Buffers: shared hit=48
81. 0.058 0.158 ↑ 1.0 354 1

Hash (cost=14.54..14.54 rows=354 width=9) (actual time=0.158..0.158 rows=354 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=11
82. 0.100 0.100 ↑ 1.0 354 1

Seq Scan on accounts accounts_1 (cost=0.00..14.54 rows=354 width=9) (actual time=0.015..0.100 rows=354 loops=1)

  • Buffers: shared hit=11
83. 2,806.986 2,807.504 ↓ 14,254.8 43,448,695 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.408..2,807.504 rows=43,448,695 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
84. 0.518 0.518 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.014..0.518 rows=3,048 loops=1)

  • Buffers: shared hit=38
85. 2,306.473 2,472.006 ↓ 7,425.3 36,911,275 1

Sort (cost=9,565.98..9,578.40 rows=4,971 width=48) (actual time=180.324..2,472.006 rows=36,911,275 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5645kB
  • Buffers: shared hit=989
86. 6.173 165.533 ↓ 8.9 44,428 1

Subquery Scan on pa (cost=9,136.50..9,260.77 rows=4,971 width=48) (actual time=120.867..165.533 rows=44,428 loops=1)

  • Buffers: shared hit=989
87. 66.954 159.360 ↓ 8.9 44,428 1

HashAggregate (cost=9,136.50..9,211.06 rows=4,971 width=48) (actual time=120.865..159.360 rows=44,428 loops=1)

  • Group Key: plan_rows_2.project_plan_id, plan_rows_2.epic_id, plan_rows_2.user_id, plan_roles.activity_id, COALESCE(plan_roles.start_date, '1980-01-01'::date), COALESCE(lag((plan_roles.start_date - 1), 1) OVER (?), '3000-01-01'::date)
  • Buffers: shared hit=989
88. 29.122 92.406 ↑ 1.0 44,581 1

WindowAgg (cost=6,572.98..7,576.09 rows=44,583 width=68) (actual time=57.323..92.406 rows=44,581 loops=1)

  • Buffers: shared hit=989
89. 24.486 63.284 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=60) (actual time=57.313..63.284 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7154kB
  • Buffers: shared hit=989
90. 16.629 38.798 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..3,130.23 rows=44,583 width=60) (actual time=18.181..38.798 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_2.uuid)
  • Buffers: shared hit=989
91. 4.248 4.248 ↑ 1.0 44,583 1

Seq Scan on plan_roles (cost=0.00..885.83 rows=44,583 width=32) (actual time=0.015..4.248 rows=44,583 loops=1)

  • Buffers: shared hit=440
92. 9.848 17.921 ↑ 1.0 48,106 1

Hash (cost=1,030.06..1,030.06 rows=48,106 width=28) (actual time=17.921..17.921 rows=48,106 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
  • Buffers: shared hit=549
93. 8.073 8.073 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_2 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.007..8.073 rows=48,106 loops=1)

  • Buffers: shared hit=549
94. 7.064 865.635 ↑ 28.4 17,221 1

Subquery Scan on *SELECT* 2_1 (cost=1,111.30..326,221.82 rows=489,507 width=104) (actual time=17.970..865.635 rows=17,221 loops=1)

  • Buffers: shared hit=1118718
95. 14.716 858.571 ↑ 28.4 17,221 1

Nested Loop (cost=1,111.30..320,102.98 rows=489,507 width=111) (actual time=17.968..858.571 rows=17,221 loops=1)

  • Buffers: shared hit=1118718
96. 7.400 28.227 ↑ 1.3 11,022 1

Hash Join (cost=1,110.31..1,860.29 rows=14,010 width=60) (actual time=17.916..28.227 rows=11,022 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_membership_activity_links_1.staff_membership_id)
  • Buffers: shared hit=405
97. 2.939 2.939 ↑ 1.0 21,792 1

Seq Scan on vacations vacations_1 (cost=0.00..500.92 rows=21,792 width=44) (actual time=0.014..2.939 rows=21,792 loops=1)

  • Buffers: shared hit=283
98. 0.295 17.888 ↑ 1.3 1,592 1

Hash (cost=1,083.71..1,083.71 rows=2,128 width=16) (actual time=17.888..17.888 rows=1,592 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 109kB
  • Buffers: shared hit=122
99. 0.640 17.593 ↑ 1.3 1,592 1

Hash Join (cost=969.31..1,083.71 rows=2,128 width=16) (actual time=16.587..17.593 rows=1,592 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
  • Buffers: shared hit=122
100. 0.379 0.379 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..79.56 rows=4,256 width=20) (actual time=0.005..0.379 rows=4,256 loops=1)

  • Buffers: shared hit=37
101. 0.264 16.574 ↓ 8.7 1,592 1

Hash (cost=967.01..967.01 rows=184 width=4) (actual time=16.574..16.574 rows=1,592 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
  • Buffers: shared hit=85
102. 1.219 16.310 ↓ 8.7 1,592 1

HashAggregate (cost=965.17..967.01 rows=184 width=4) (actual time=16.118..16.310 rows=1,592 loops=1)

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=85
103. 1.328 15.091 ↓ 3.4 1,592 1

Subquery Scan on staff_activities_with_dates (cost=836.52..963.99 rows=472 width=4) (actual time=12.604..15.091 rows=1,592 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2629
  • Buffers: shared hit=85
104. 0.843 13.763 ↑ 1.0 4,221 1

Unique (cost=836.52..879.01 rows=4,249 width=613) (actual time=12.598..13.763 rows=4,221 loops=1)

  • Buffers: shared hit=85
105. 2.650 12.920 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=613) (actual time=12.597..12.920 rows=4,249 loops=1)

  • Sort Key: staff_memberships_3.account_id, staff_memberships_3.user_id, (COALESCE(staff_membership_activity_links_2.start_date, staff_memberships_3.joined_at)), (COALESCE(lag((staff_membership_activity_links_2.start_date - 1), 1) OVER (?), staff_memberships_3.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 524kB
  • Buffers: shared hit=85
106. 2.728 10.270 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..580.45 rows=4,249 width=613) (actual time=7.230..10.270 rows=4,249 loops=1)

  • Buffers: shared hit=85
107. 3.499 7.542 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=28) (actual time=7.221..7.542 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links_2.staff_membership_id, staff_membership_activity_links_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 524kB
  • Buffers: shared hit=85
108. 1.980 4.043 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=28) (actual time=2.504..4.043 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
  • Buffers: shared hit=85
109. 0.402 0.402 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..79.56 rows=4,256 width=12) (actual time=0.015..0.402 rows=4,256 loops=1)

  • Buffers: shared hit=37
110. 0.903 1.661 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=20) (actual time=1.661..1.661 rows=4,002 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=48
111. 0.758 0.758 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..88.02 rows=4,002 width=20) (actual time=0.016..0.758 rows=4,002 loops=1)

  • Buffers: shared hit=48
112. 194.963 815.628 ↑ 1.0 2 11,022

Nested Loop Left Join (cost=0.98..22.43 rows=2 width=28) (actual time=0.042..0.074 rows=2 loops=11,022)

  • Join Filter: ((cost_exchange_rates_1.start_date <= (generate_series_3.generate_series)::date) AND (cost_exchange_rates_1.end_date >= (generate_series_3.generate_series)::date))
  • Rows Removed by Join Filter: 68
  • Buffers: shared hit=1118313
113. 77.532 396.792 ↓ 2.0 2 11,022

Nested Loop Anti Join (cost=0.70..20.82 rows=1 width=24) (actual time=0.028..0.036 rows=2 loops=11,022)

  • Join Filter: (generate_series_3.generate_series = non_working_intervals_by_dates_1.date)
  • Rows Removed by Join Filter: 59
  • Buffers: shared hit=726780
114. 22.044 55.110 ↓ 2.0 2 11,022

Nested Loop (cost=0.29..19.42 rows=1 width=24) (actual time=0.004..0.005 rows=2 loops=11,022)

  • Join Filter: (vacations_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=33066
115. 11.022 11.022 ↑ 1.0 1 11,022

Index Scan using memberships_pkey on staff_memberships staff_memberships_2 (cost=0.28..0.35 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=11,022)

  • Index Cond: (id = staff_membership_activity_links_1.staff_membership_id)
  • Buffers: shared hit=33066
116. 22.044 22.044 ↑ 12.5 2 11,022

Function Scan on generate_series generate_series_3 (cost=0.01..18.76 rows=25 width=8) (actual time=0.002..0.002 rows=2 loops=11,022)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
117. 264.150 264.150 ↓ 18.5 37 17,610

Index Only Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..1.37 rows=2 width=12) (actual time=0.003..0.015 rows=37 loops=17,610)

  • Index Cond: ((account_id = staff_memberships_2.account_id) AND (user_id = staff_memberships_2.user_id))
  • Heap Fetches: 646022
  • Buffers: shared hit=693714
118. 223.873 223.873 ↓ 1.4 44 17,221

Index Scan using exchange_rates_by_months_idx on exchange_rates_by_months cost_exchange_rates_1 (cost=0.28..0.97 rows=32 width=24) (actual time=0.003..0.013 rows=44 loops=17,221)

  • Index Cond: ((account_id = staff_memberships_2.account_id) AND ((currency)::text = (staff_memberships_2.currency)::text))
  • Buffers: shared hit=391533
119. 0.004 0.120 ↑ 9.8 12 1

Hash (cost=17.20..17.20 rows=118 width=4) (actual time=0.120..0.120 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=11
120. 0.116 0.116 ↑ 9.8 12 1

Seq Scan on accounts (cost=0.00..17.20 rows=118 width=4) (actual time=0.015..0.116 rows=12 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
  • Buffers: shared hit=11
121. 338.001 607.959 ↓ 4.3 1,166,321 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=523.582..607.959 rows=1,166,321 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32838kB
  • Buffers: shared hit=8680
122. 28.616 269.958 ↑ 1.0 263,033 1

Append (cost=300.88..22,213.28 rows=272,669 width=44) (actual time=1.935..269.958 rows=263,033 loops=1)

  • Buffers: shared hit=8680
123. 0.104 15.199 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=300.88..470.62 rows=49 width=44) (actual time=1.934..15.199 rows=474 loops=1)

  • Buffers: shared hit=2602
124. 0.144 15.095 ↓ 9.7 474 1

Merge Join (cost=300.88..470.13 rows=49 width=612) (actual time=1.933..15.095 rows=474 loops=1)

  • Merge Cond: (clients.brand_id = brands.id)
  • Buffers: shared hit=2602
125. 0.163 14.904 ↓ 9.7 474 1

Nested Loop (cost=294.80..1,671.71 rows=49 width=52) (actual time=1.882..14.904 rows=474 loops=1)

  • Buffers: shared hit=2601
126. 0.008 1.276 ↓ 5.0 5 1

Merge Join (cost=294.38..294.67 rows=1 width=32) (actual time=1.255..1.276 rows=5 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
  • Buffers: shared hit=144
127. 0.027 0.630 ↑ 28.8 4 1

WindowAgg (cost=108.61..111.49 rows=115 width=45) (actual time=0.613..0.630 rows=4 loops=1)

  • Buffers: shared hit=44
128. 0.034 0.603 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.602..0.603 rows=5 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=44
129. 0.569 0.569 ↑ 1.0 115 1

Seq Scan on rate_cards (cost=0.00..104.68 rows=115 width=29) (actual time=0.118..0.569 rows=115 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
  • Buffers: shared hit=44
130. 0.006 0.638 ↑ 1.0 5 1

Sort (cost=185.76..185.78 rows=5 width=8) (actual time=0.636..0.638 rows=5 loops=1)

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=100
131. 0.006 0.632 ↑ 1.0 5 1

Nested Loop (cost=0.56..185.71 rows=5 width=8) (actual time=0.347..0.632 rows=5 loops=1)

  • Buffers: shared hit=100
132. 0.004 0.616 ↑ 1.0 5 1

Nested Loop (cost=0.28..184.13 rows=5 width=8) (actual time=0.341..0.616 rows=5 loops=1)

  • Buffers: shared hit=85
133. 0.597 0.597 ↑ 1.0 5 1

Seq Scan on pricing_models (cost=0.00..142.62 rows=5 width=4) (actual time=0.331..0.597 rows=5 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
  • Buffers: shared hit=70
134. 0.015 0.015 ↑ 1.0 1 5

Index Scan using projects_pkey on projects projects_2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=5)

  • Index Cond: (id = pricing_models.project_id)
  • Buffers: shared hit=15
135. 0.010 0.010 ↑ 1.0 1 5

Index Scan using clients_pkey on clients (cost=0.28..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5)

  • Index Cond: (id = projects_2.client_id)
  • Buffers: shared hit=15
136. 13.465 13.465 ↓ 1.9 95 5

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,376.55 rows=49 width=28) (actual time=0.754..2.693 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
137. 0.029 0.047 ↑ 38.3 3 1

Sort (cost=6.09..6.37 rows=115 width=4) (actual time=0.047..0.047 rows=3 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=1
138. 0.018 0.018 ↑ 1.0 115 1

Seq Scan on brands (cost=0.00..2.15 rows=115 width=4) (actual time=0.007..0.018 rows=115 loops=1)

  • Buffers: shared hit=1
139. 13.173 73.433 ↓ 2.6 91,600 1

Subquery Scan on *SELECT* 2 (cost=583.00..5,484.31 rows=35,240 width=44) (actual time=13.043..73.433 rows=91,600 loops=1)

  • Buffers: shared hit=2036
140. 36.808 60.260 ↓ 2.6 91,600 1

Hash Join (cost=583.00..5,131.91 rows=35,240 width=612) (actual time=13.042..60.260 rows=91,600 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
  • Buffers: shared hit=2036
141. 17.374 17.374 ↑ 1.0 174,437 1

Seq Scan on rates rates_1 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.010..17.374 rows=174,437 loops=1)

  • Buffers: shared hit=1798
142. 0.190 6.078 ↑ 1.1 657 1

Hash (cost=574.00..574.00 rows=720 width=24) (actual time=6.078..6.078 rows=657 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=238
143. 0.188 5.888 ↑ 1.1 657 1

Hash Join (cost=378.98..574.00 rows=720 width=24) (actual time=4.090..5.888 rows=657 loops=1)

  • Hash Cond: (clients_1.office_id = offices.id)
  • Buffers: shared hit=238
144. 0.185 4.296 ↑ 1.0 623 1

Hash Join (cost=214.02..399.80 rows=623 width=8) (actual time=2.682..4.296 rows=623 loops=1)

  • Hash Cond: (projects_3.client_id = clients_1.id)
  • Buffers: shared hit=190
145. 1.926 3.440 ↑ 1.0 623 1

Hash Join (cost=150.41..334.56 rows=623 width=8) (actual time=1.986..3.440 rows=623 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
  • Buffers: shared hit=168
146. 0.607 0.607 ↑ 1.0 5,812 1

Seq Scan on projects projects_3 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.006..0.607 rows=5,812 loops=1)

  • Buffers: shared hit=98
147. 0.109 0.907 ↑ 1.0 623 1

Hash (cost=142.62..142.62 rows=623 width=4) (actual time=0.907..0.907 rows=623 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
  • Buffers: shared hit=70
148. 0.798 0.798 ↑ 1.0 623 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..142.62 rows=623 width=4) (actual time=0.020..0.798 rows=623 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5187
  • Buffers: shared hit=70
149. 0.306 0.671 ↑ 1.0 1,849 1

Hash (cost=40.49..40.49 rows=1,849 width=8) (actual time=0.671..0.671 rows=1,849 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
  • Buffers: shared hit=22
150. 0.365 0.365 ↑ 1.0 1,849 1

Seq Scan on clients clients_1 (cost=0.00..40.49 rows=1,849 width=8) (actual time=0.008..0.365 rows=1,849 loops=1)

  • Buffers: shared hit=22
151. 0.103 1.404 ↑ 1.1 450 1

Hash (cost=158.85..158.85 rows=489 width=28) (actual time=1.404..1.404 rows=450 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=48
152. 0.118 1.301 ↑ 1.1 450 1

Hash Join (cost=140.44..158.85 rows=489 width=28) (actual time=0.848..1.301 rows=450 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=48
153. 0.316 1.043 ↑ 1.0 489 1

WindowAgg (cost=126.52..138.74 rows=489 width=45) (actual time=0.704..1.043 rows=489 loops=1)

  • Buffers: shared hit=44
154. 0.156 0.727 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.698..0.727 rows=489 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 63kB
  • Buffers: shared hit=44
155. 0.571 0.571 ↑ 1.0 489 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..104.68 rows=489 width=29) (actual time=0.106..0.571 rows=489 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
  • Buffers: shared hit=44
156. 0.083 0.140 ↑ 1.0 441 1

Hash (cost=8.41..8.41 rows=441 width=4) (actual time=0.140..0.140 rows=441 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
  • Buffers: shared hit=4
157. 0.057 0.057 ↑ 1.0 441 1

Seq Scan on offices (cost=0.00..8.41 rows=441 width=4) (actual time=0.006..0.057 rows=441 loops=1)

  • Buffers: shared hit=4
158. 14.107 82.032 ↑ 2.2 94,593 1

Subquery Scan on *SELECT* 3 (cost=840.78..9,363.71 rows=205,419 width=44) (actual time=9.982..82.032 rows=94,593 loops=1)

  • Buffers: shared hit=2032
159. 39.956 67.925 ↑ 2.2 94,593 1

Hash Join (cost=840.78..7,309.52 rows=205,419 width=612) (actual time=9.981..67.925 rows=94,593 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
  • Buffers: shared hit=2032
160. 18.024 18.024 ↑ 1.0 174,437 1

Seq Scan on rates rates_2 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.010..18.024 rows=174,437 loops=1)

  • Buffers: shared hit=1798
161. 0.899 9.945 ↑ 1.0 4,004 1

Hash (cost=788.31..788.31 rows=4,197 width=24) (actual time=9.945..9.945 rows=4,004 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=234
162. 0.993 9.046 ↑ 1.0 4,004 1

Hash Join (cost=586.95..788.31 rows=4,197 width=24) (actual time=6.159..9.046 rows=4,004 loops=1)

  • Hash Cond: (projects_4.client_id = clients_2.id)
  • Buffers: shared hit=234
163. 1.005 4.091 ↑ 1.0 3,620 1

Hash Join (cost=228.77..380.91 rows=3,620 width=8) (actual time=2.174..4.091 rows=3,620 loops=1)

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
  • Buffers: shared hit=168
164. 0.964 0.964 ↑ 1.0 3,621 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..142.62 rows=3,621 width=4) (actual time=0.007..0.964 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2189
  • Buffers: shared hit=70
165. 0.922 2.122 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=2.122..2.122 rows=5,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
  • Buffers: shared hit=98
166. 1.200 1.200 ↑ 1.0 5,812 1

Seq Scan on projects projects_4 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.006..1.200 rows=5,812 loops=1)

  • Buffers: shared hit=98
167. 0.441 3.962 ↑ 1.0 1,820 1

Hash (cost=335.42..335.42 rows=1,821 width=28) (actual time=3.962..3.962 rows=1,820 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=66
168. 0.495 3.521 ↑ 1.0 1,820 1

Hash Join (cost=266.89..335.42 rows=1,821 width=28) (actual time=1.820..3.521 rows=1,820 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
  • Buffers: shared hit=66
169. 1.157 2.512 ↑ 1.0 1,821 1

WindowAgg (cost=203.29..248.81 rows=1,821 width=45) (actual time=1.240..2.512 rows=1,821 loops=1)

  • Buffers: shared hit=44
170. 0.719 1.355 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.227..1.355 rows=1,821 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 191kB
  • Buffers: shared hit=44
171. 0.636 0.636 ↑ 1.0 1,821 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..104.68 rows=1,821 width=29) (actual time=0.007..0.636 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3033
  • Buffers: shared hit=44
172. 0.282 0.514 ↑ 1.0 1,849 1

Hash (cost=40.49..40.49 rows=1,849 width=4) (actual time=0.514..0.514 rows=1,849 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=22
173. 0.232 0.232 ↑ 1.0 1,849 1

Seq Scan on clients clients_2 (cost=0.00..40.49 rows=1,849 width=4) (actual time=0.008..0.232 rows=1,849 loops=1)

  • Buffers: shared hit=22
174. 11.007 70.678 ↓ 2.4 76,366 1

Subquery Scan on *SELECT* 4 (cost=695.56..5,531.29 rows=31,961 width=44) (actual time=7.869..70.678 rows=76,366 loops=1)

  • Buffers: shared hit=2010
175. 34.817 59.671 ↓ 2.4 76,366 1

Hash Join (cost=695.56..5,211.68 rows=31,961 width=612) (actual time=7.868..59.671 rows=76,366 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
  • Buffers: shared hit=2010
176. 17.013 17.013 ↑ 1.0 174,437 1

Seq Scan on rates rates_3 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.007..17.013 rows=174,437 loops=1)

  • Buffers: shared hit=1798
177. 0.310 7.841 ↓ 2.4 1,591 1

Hash (cost=687.40..687.40 rows=653 width=24) (actual time=7.841..7.841 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
  • Buffers: shared hit=212
178. 0.805 7.531 ↓ 2.4 1,591 1

Hash Join (cost=502.95..687.40 rows=653 width=24) (actual time=6.151..7.531 rows=1,591 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
  • Buffers: shared hit=212
179. 0.595 0.595 ↑ 1.0 5,812 1

Seq Scan on projects projects_5 (cost=0.00..156.12 rows=5,812 width=4) (actual time=0.005..0.595 rows=5,812 loops=1)

  • Buffers: shared hit=98
180. 0.421 6.131 ↓ 2.4 1,591 1

Hash (cost=494.79..494.79 rows=653 width=24) (actual time=6.131..6.131 rows=1,591 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
  • Buffers: shared hit=114
181. 0.473 5.710 ↓ 2.4 1,591 1

Hash Join (cost=403.40..494.79 rows=653 width=24) (actual time=3.330..5.710 rows=1,591 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
  • Buffers: shared hit=114
182. 1.687 3.889 ↑ 1.0 2,429 1

WindowAgg (cost=241.26..301.98 rows=2,429 width=45) (actual time=1.957..3.889 rows=2,429 loops=1)

  • Buffers: shared hit=44
183. 1.259 2.202 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.946..2.202 rows=2,429 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 286kB
  • Buffers: shared hit=44
184. 0.943 0.943 ↑ 1.0 2,429 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..104.68 rows=2,429 width=29) (actual time=0.021..0.943 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2425
  • Buffers: shared hit=44
185. 0.325 1.348 ↑ 1.0 1,561 1

Hash (cost=142.62..142.62 rows=1,561 width=8) (actual time=1.348..1.348 rows=1,561 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
  • Buffers: shared hit=70
186. 1.023 1.023 ↑ 1.0 1,561 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..142.62 rows=1,561 width=8) (actual time=0.008..1.023 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
  • Buffers: shared hit=70
187. 40.364 40.456 ↓ 2,172.3 664,709 1

Sort (cost=22.77..23.53 rows=306 width=26) (actual time=0.173..40.456 rows=664,709 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 53kB
  • Buffers: shared hit=4
188. 0.092 0.092 ↓ 1.2 359 1

Seq Scan on discounts (cost=0.00..10.13 rows=306 width=26) (actual time=0.016..0.092 rows=359 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
  • Buffers: shared hit=4
189. 2,958.897 2,959.432 ↓ 13,515.3 41,194,638 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.388..2,959.432 rows=41,194,638 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
190. 0.535 0.535 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.016..0.535 rows=3,048 loops=1)

  • Buffers: shared hit=38
Planning time : 10.438 ms
Execution time : 58,228.801 ms