explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y7MC : Optimization for: Optimization for: Optimization for: Optimization for: plan #TeKD; plan #aMrH; plan #0vt9; plan #K3Qt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,589.889 52,474.316 ↓ 3.3 520,465 1

Merge Left Join (cost=2,335,156.01..2,338,532.52 rows=155,495 width=140) (actual time=46,016.921..52,474.316 rows=520,465 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: 23919485
  • Buffers: shared hit=26845172 read=7280
2. 365.748 46,060.950 ↓ 3.3 520,465 1

Sort (cost=2,334,911.15..2,335,299.89 rows=155,495 width=136) (actual time=46,015.306..46,060.950 rows=520,465 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 85482kB
  • Buffers: shared hit=26845134 read=7280
3. 174.247 45,695.202 ↓ 3.3 520,465 1

Merge Left Join (cost=2,288,961.00..2,321,502.42 rows=155,495 width=136) (actual time=45,055.053..45,695.202 rows=520,465 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: 350171
  • Buffers: shared hit=26845134 read=7280
4. 325.951 45,496.888 ↓ 3.3 520,377 1

Merge Left Join (cost=2,288,938.23..2,313,953.70 rows=155,495 width=132) (actual time=45,054.738..45,496.888 rows=520,377 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: 138500
  • Buffers: shared hit=26845130 read=7280
5. 386.722 44,495.648 ↓ 3.3 520,377 1

Sort (cost=2,242,107.32..2,242,496.06 rows=155,495 width=104) (actual time=44,444.482..44,495.648 rows=520,377 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: 85467kB
  • Buffers: shared hit=26836450 read=7280
6. 164.210 44,108.926 ↓ 3.3 520,377 1

Hash Join (cost=1,563,160.94..2,228,698.59 rows=155,495 width=104) (actual time=35,487.297..44,108.926 rows=520,377 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
  • Buffers: shared hit=26836450 read=7280
7. 102.888 43,944.480 ↓ 1.6 740,209 1

Append (cost=1,563,142.27..2,222,775.88 rows=466,485 width=104) (actual time=35,485.361..43,944.480 rows=740,209 loops=1)

  • Buffers: shared hit=26836439 read=7280
8. 5,661.047 43,210.355 ↓ 1.5 722,988 1

Merge Left Join (cost=1,563,142.27..1,960,953.14 rows=466,483 width=104) (actual time=35,485.360..43,210.355 rows=722,988 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: 27584634
  • Buffers: shared hit=26388001 read=7280
9. 499.224 35,368.188 ↓ 1.5 722,988 1

Sort (cost=1,553,576.29..1,554,742.50 rows=466,483 width=153) (actual time=35,268.532..35,368.188 rows=722,988 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: quicksort Memory: 127034kB
  • Buffers: shared hit=26387012 read=7280
10. 5,216.507 34,868.964 ↓ 1.5 722,988 1

Merge Left Join (cost=1,505,355.10..1,509,653.50 rows=466,483 width=153) (actual time=27,424.956..34,868.964 rows=722,988 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: 27566322
  • Buffers: shared hit=26387012 read=7280
11. 676.233 27,483.537 ↓ 1.5 722,988 1

Sort (cost=1,505,110.23..1,506,276.44 rows=466,483 width=153) (actual time=27,423.324..27,483.537 rows=722,988 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 127034kB
  • Buffers: shared hit=26386974 read=7280
12. 350.599 26,807.304 ↓ 1.5 722,988 1

Hash Left Join (cost=1,444,070.32..1,461,187.44 rows=466,483 width=153) (actual time=25,806.109..26,807.304 rows=722,988 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision)
  • Rows Removed by Filter: 71970
  • Buffers: shared hit=26386974 read=7280
13. 458.056 26,456.312 ↑ 1.8 794,958 1

Merge Left Join (cost=1,444,051.36..1,457,431.18 rows=1,399,449 width=148) (actual time=25,805.653..26,456.312 rows=794,958 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: 174049
  • Buffers: shared hit=26386963 read=7280
14. 903.650 25,906.788 ↑ 1.8 794,958 1

Sort (cost=1,443,204.22..1,446,702.84 rows=1,399,449 width=120) (actual time=25,792.622..25,906.788 rows=794,958 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 136367kB
  • Buffers: shared hit=26386878 read=7280
15. 272.270 25,003.138 ↑ 1.8 794,958 1

Hash Join (cost=1,136,224.56..1,300,345.47 rows=1,399,449 width=120) (actual time=21,696.114..25,003.138 rows=794,958 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
  • Buffers: shared hit=26386878 read=7280
16. 170.821 24,728.685 ↑ 1.1 1,291,529 1

Append (cost=1,135,995.79..1,282,445.98 rows=1,399,449 width=116) (actual time=21,572.756..24,728.685 rows=1,291,529 loops=1)

  • Buffers: shared hit=26386780 read=7280
17. 189.248 24,111.663 ↑ 1.2 587,568 1

Result (cost=1,135,995.79..1,237,188.67 rows=695,488 width=116) (actual time=21,572.755..24,111.663 rows=587,568 loops=1)

  • Buffers: shared hit=26384444
18. 80.666 23,922.415 ↑ 1.2 587,568 1

Append (cost=1,135,995.79..1,228,495.07 rows=695,488 width=140) (actual time=21,572.751..23,922.415 rows=587,568 loops=1)

  • Buffers: shared hit=26384444
19. 76.792 21,962.998 ↑ 1.6 395,111 1

Subquery Scan on *SELECT* 1_1 (cost=1,135,995.79..1,158,653.00 rows=613,400 width=114) (actual time=21,572.750..21,962.998 rows=395,111 loops=1)

  • Buffers: shared hit=24524601
20. 148.241 21,886.206 ↑ 1.6 395,111 1

Merge Left Join (cost=1,135,995.79..1,149,452.00 rows=613,400 width=134) (actual time=21,572.748..21,886.206 rows=395,111 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=24524601
21. 624.291 21,611.722 ↑ 1.6 395,111 1

Sort (cost=529,398.51..530,932.01 rows=613,400 width=66) (actual time=21,572.742..21,611.722 rows=395,111 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: quicksort Memory: 67851kB
  • Buffers: shared hit=24524270
22. 74.401 20,987.431 ↑ 1.6 395,111 1

Nested Loop (cost=1,991.21..470,430.93 rows=613,400 width=66) (actual time=25.685..20,987.431 rows=395,111 loops=1)

  • Buffers: shared hit=24524270
23. 20.958 20,627.371 ↓ 1.1 25,969 1

Hash Join (cost=1,991.21..4,246.92 rows=24,536 width=66) (actual time=25.645..20,627.371 rows=25,969 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
  • Buffers: shared hit=24524270
24. 43.180 20,585.807 ↓ 1.2 32,054 1

Hash Left Join (cost=430.80..2,336.83 rows=27,820 width=54) (actual time=4.765..20,585.807 rows=32,054 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 23610
  • Buffers: shared hit=24523721
25. 29.684 55.594 ↓ 1.0 55,664 1

Hash Left Join (cost=202.03..1,744.28 rows=55,639 width=50) (actual time=2.022..55.594 rows=55,664 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=803
26. 23.927 23.927 ↓ 1.0 55,664 1

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
  • Buffers: shared hit=697
27. 0.584 1.983 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
29. 1.202 2.681 ↑ 1.0 5,812 1

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

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

Seq Scan on projects projects_1 (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.010..1.479 rows=5,812 loops=1)

  • Buffers: shared hit=98
31.          

SubPlan (for Hash Left Join)

32. 11,077.136 20,484.352 ↓ 0.0 0 55,664

Nested Loop (cost=0.69..1,412.30 rows=67 width=0) (actual time=0.368..0.368 rows=0 loops=55,664)

  • Join Filter: (non_working_intervals_by_dates_1.office_id = clients_3.office_id)
  • Rows Removed by Join Filter: 446
  • Buffers: shared hit=24522820
33. 9,407.216 9,407.216 ↑ 5.1 447 55,664

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..1,369.58 rows=2,295 width=4) (actual time=0.002..0.169 rows=447 loops=55,664)

  • Index Cond: ((projects_1.account_id = account_id) AND (date IS NOT NULL))
  • Buffers: shared hit=24449961
34. 0.000 0.000 ↑ 1.0 1 24,869,582

Materialize (cost=0.28..8.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=24,869,582)

  • Buffers: shared hit=72859
35. 48.476 48.476 ↑ 1.0 1 24,238

Index Scan using clients_pkey on clients clients_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=24,238)

  • Index Cond: (projects_1.client_id = id)
  • Buffers: shared hit=72859
36. 11.063 20.606 ↑ 1.0 42,344 1

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

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

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

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
  • Buffers: shared hit=549
38. 285.659 285.659 ↑ 1.7 15 25,969

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.005..0.011 rows=15 loops=25,969)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
39. 33.348 126.243 ↑ 17.7 32,563 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=120.775..126.243 rows=32,563 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
40. 5.315 92.895 ↑ 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=67.893..92.895 rows=32,266 loops=1)

  • Buffers: shared hit=331
41. 35.435 87.580 ↑ 17.8 32,266 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=331
42. 12.127 52.145 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.615..52.145 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
43. 14.118 38.435 ↑ 634.8 34,330 1

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

  • Buffers: shared hit=283
44. 2.525 2.525 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
45. 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)

46. 0.852 1.583 ↑ 1.0 4,002 1

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

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

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

  • Buffers: shared hit=48
48. 50.362 1,878.751 ↓ 2.3 192,457 1

Nested Loop (cost=1,215.85..65,543.75 rows=82,088 width=114) (actual time=8.609..1,878.751 rows=192,457 loops=1)

  • Buffers: shared hit=1859843
49. 4.661 1,700.604 ↓ 1.9 6,085 1

Hash Left Join (cost=1,215.85..2,942.52 rows=3,284 width=62) (actual time=8.573..1,700.604 rows=6,085 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Filter: (NOT (SubPlan 2))
  • Rows Removed by Filter: 1791
  • Buffers: shared hit=1859843
50. 3.380 38.981 ↓ 1.2 7,876 1

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

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
  • Buffers: shared hit=1251
51. 16.933 34.078 ↓ 1.2 7,876 1

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

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_1.uuid)
  • Buffers: shared hit=1145
52. 13.210 13.210 ↓ 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.018..13.210 rows=55,664 loops=1)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 366kB
  • Buffers: shared hit=448
54. 1.974 2.263 ↓ 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.342..2.263 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
  • Buffers: shared hit=448
55. 0.289 0.289 ↓ 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.288..0.289 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
  • Buffers: shared hit=18
56. 0.454 1.523 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
57. 1.069 1.069 ↑ 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.069 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
58. 1.436 3.002 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
  • Buffers: shared hit=98
59. 1.566 1.566 ↑ 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.007..1.566 rows=5,812 loops=1)

  • Buffers: shared hit=98
60.          

SubPlan (for Hash Left Join)

61. 874.236 1,653.960 ↓ 0.0 0 7,876

Nested Loop (cost=0.69..1,412.30 rows=67 width=0) (actual time=0.210..0.210 rows=0 loops=7,876)

  • Join Filter: (non_working_intervals_by_dates_2.office_id = clients_4.office_id)
  • Rows Removed by Join Filter: 240
  • Buffers: shared hit=1858494
62. 779.724 779.724 ↑ 9.6 240 7,876

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..1,369.58 rows=2,295 width=4) (actual time=0.001..0.099 rows=240 loops=7,876)

  • Index Cond: ((projects_1_1.account_id = account_id) AND (date IS NOT NULL))
  • Buffers: shared hit=1853070
63. 0.000 0.000 ↑ 1.0 1 1,890,203

Materialize (cost=0.28..8.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1,890,203)

  • Buffers: shared hit=5424
64. 5.424 5.424 ↑ 1.0 1 1,808

Index Scan using clients_pkey on clients clients_4 (cost=0.28..8.29 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1,808)

  • Index Cond: (projects_1_1.client_id = id)
  • Buffers: shared hit=5424
65. 127.785 127.785 ↓ 1.3 32 6,085

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.008..0.021 rows=32 loops=6,085)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 13
66. 317.959 446.201 ↑ 1.0 703,961 1

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

  • Hash Cond: (time_logs.epic_id = epics.id)
  • Buffers: shared hit=2336 read=7280
67. 107.399 107.399 ↑ 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..107.399 rows=703,961 loops=1)

  • Buffers: shared hit=1875 read=7280
68. 6.521 20.843 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
  • Buffers: shared hit=461
69. 9.380 14.322 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.761..14.322 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
70. 3.216 3.216 ↑ 1.0 26,435 1

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

  • Buffers: shared hit=355
71. 0.505 1.726 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
  • Buffers: shared hit=106
72. 1.221 1.221 ↑ 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.007..1.221 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
  • Buffers: shared hit=106
73. 1.106 2.183 ↑ 1.0 5,812 1

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

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

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

  • Buffers: shared hit=98
75. 76.182 91.468 ↓ 216.0 917,790 1

Materialize (cost=847.14..942.74 rows=4,249 width=40) (actual time=13.023..91.468 rows=917,790 loops=1)

  • Buffers: shared hit=85
76. 1.622 15.286 ↑ 1.0 4,218 1

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

  • Buffers: shared hit=85
77. 3.622 13.664 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=13.018..13.664 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
78. 3.400 10.042 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
79. 2.445 6.642 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=6.243..6.642 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
80. 1.585 4.197 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
  • Buffers: shared hit=85
81. 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.031..0.461 rows=4,256 loops=1)

  • Buffers: shared hit=37
82. 1.120 2.151 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
  • Buffers: shared hit=48
83. 1.031 1.031 ↑ 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.012..1.031 rows=4,002 loops=1)

  • Buffers: shared hit=48
84. 0.175 0.393 ↑ 1.0 354 1

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

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

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

  • Buffers: shared hit=11
86. 2,168.348 2,168.920 ↓ 9,180.7 27,982,920 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.503..2,168.920 rows=27,982,920 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
87. 0.572 0.572 ↑ 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.016..0.572 rows=3,048 loops=1)

  • Buffers: shared hit=38
88. 1,983.290 2,181.120 ↓ 5,671.2 28,191,725 1

Sort (cost=9,565.98..9,578.40 rows=4,971 width=48) (actual time=216.809..2,181.120 rows=28,191,725 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5645kB
  • Buffers: shared hit=989
89. 8.127 197.830 ↓ 8.9 44,428 1

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

  • Buffers: shared hit=989
90. 82.523 189.703 ↓ 8.9 44,428 1

HashAggregate (cost=9,136.50..9,211.06 rows=4,971 width=48) (actual time=140.564..189.703 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
91. 33.294 107.180 ↑ 1.0 44,581 1

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

  • Buffers: shared hit=989
92. 29.581 73.886 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=60) (actual time=67.271..73.886 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
93. 19.717 44.305 ↑ 1.0 44,581 1

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

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

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

  • Buffers: shared hit=440
95. 10.883 19.782 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
  • Buffers: shared hit=549
96. 8.899 8.899 ↑ 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.006..8.899 rows=48,106 loops=1)

  • Buffers: shared hit=549
97. 6.522 631.237 ↓ 8,610.5 17,221 1

Subquery Scan on *SELECT* 2_1 (cost=247,976.15..254,825.48 rows=2 width=104) (actual time=89.568..631.237 rows=17,221 loops=1)

  • Buffers: shared hit=448438
98. 229.198 624.715 ↓ 8,610.5 17,221 1

Nested Loop Left Join (cost=247,976.15..254,825.46 rows=2 width=111) (actual time=89.566..624.715 rows=17,221 loops=1)

  • 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: 750977
  • Buffers: shared hit=448438
99. 19.422 137.202 ↓ 17,221.0 17,221 1

Merge Right Join (cost=247,975.87..254,823.83 rows=1 width=64) (actual time=89.513..137.202 rows=17,221 loops=1)

  • Merge Cond: ((non_working_intervals_by_dates.account_id = staff_memberships_2.account_id) AND (non_working_intervals_by_dates.user_id = staff_memberships_2.user_id) AND (non_working_intervals_by_dates.date = generate_series_3.generate_series))
  • Filter: (non_working_intervals_by_dates.id IS NULL)
  • Rows Removed by Filter: 389
  • Buffers: shared hit=56905
100. 25.801 25.801 ↑ 1.0 59,669 1

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates (cost=0.41..3,467.10 rows=59,712 width=16) (actual time=0.024..25.801 rows=59,669 loops=1)

  • Buffers: shared hit=56452
101. 37.428 91.979 ↑ 16.4 17,610 1

Sort (cost=247,975.46..248,698.42 rows=289,183 width=64) (actual time=89.472..91.979 rows=17,610 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, generate_series_3.generate_series
  • Sort Method: quicksort Memory: 3245kB
  • Buffers: shared hit=453
102. 3.513 54.551 ↑ 16.4 17,610 1

Nested Loop (cost=1,253.91..221,744.21 rows=289,183 width=64) (actual time=20.676..54.551 rows=17,610 loops=1)

  • Buffers: shared hit=453
103. 5.881 28.994 ↑ 1.0 11,022 1

Hash Join (cost=1,253.91..1,952.21 rows=11,568 width=64) (actual time=20.662..28.994 rows=11,022 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=453
104. 2.469 2.469 ↑ 1.0 21,792 1

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

  • Buffers: shared hit=283
105. 0.495 20.644 ↑ 1.3 1,592 1

Hash (cost=1,227.36..1,227.36 rows=2,124 width=32) (actual time=20.644..20.644 rows=1,592 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 134kB
  • Buffers: shared hit=170
106. 0.494 20.149 ↑ 1.3 1,592 1

Hash Join (cost=1,107.35..1,227.36 rows=2,124 width=32) (actual time=18.456..20.149 rows=1,592 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
  • Buffers: shared hit=170
107. 0.809 17.983 ↑ 1.3 1,592 1

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

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
  • Buffers: shared hit=122
108. 0.431 0.431 ↑ 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.007..0.431 rows=4,256 loops=1)

  • Buffers: shared hit=37
109. 0.281 16.743 ↓ 8.7 1,592 1

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

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

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

  • Group Key: staff_activities_with_dates.link_id
  • Buffers: shared hit=85
111. 1.407 15.885 ↓ 3.4 1,592 1

Subquery Scan on staff_activities_with_dates (cost=836.52..963.99 rows=472 width=4) (actual time=13.287..15.885 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
112. 0.872 14.478 ↑ 1.0 4,221 1

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

  • Buffers: shared hit=85
113. 3.008 13.606 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=613) (actual time=13.278..13.606 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
114. 3.751 10.598 ↑ 1.0 4,249 1

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

  • Buffers: shared hit=85
115. 2.396 6.847 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=28) (actual time=6.439..6.847 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
116. 1.939 4.451 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
  • Buffers: shared hit=85
117. 0.656 0.656 ↑ 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.003..0.656 rows=4,256 loops=1)

  • Buffers: shared hit=37
118. 0.972 1.856 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
  • Buffers: shared hit=48
119. 0.884 0.884 ↑ 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.008..0.884 rows=4,002 loops=1)

  • Buffers: shared hit=48
120. 0.860 1.672 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 220kB
  • Buffers: shared hit=48
121. 0.812 0.812 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..88.02 rows=4,002 width=16) (actual time=0.007..0.812 rows=4,002 loops=1)

  • Buffers: shared hit=48
122. 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.001..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
123. 258.315 258.315 ↓ 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.015 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
124. 0.007 0.236 ↑ 9.8 12 1

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

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

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
  • Buffers: shared hit=11
126. 356.364 675.289 ↓ 2.9 795,076 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=610.222..675.289 rows=795,076 loops=1)

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

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

  • Buffers: shared hit=8680
128. 0.114 16.773 ↓ 9.7 474 1

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

  • Buffers: shared hit=2602
129. 0.159 16.659 ↓ 9.7 474 1

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

  • Merge Cond: (clients.brand_id = brands.id)
  • Buffers: shared hit=2602
130. 0.171 16.452 ↓ 9.7 474 1

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

  • Buffers: shared hit=2601
131. 0.010 1.551 ↓ 5.0 5 1

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

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

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

  • Buffers: shared hit=44
133. 0.036 0.598 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.597..0.598 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
134. 0.562 0.562 ↑ 1.0 115 1

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

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

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

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=100
136. 0.004 0.910 ↑ 1.0 5 1

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

  • Buffers: shared hit=100
137. 0.006 0.891 ↑ 1.0 5 1

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

  • Buffers: shared hit=85
138. 0.865 0.865 ↑ 1.0 5 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
  • Buffers: shared hit=70
139. 0.020 0.020 ↑ 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.004..0.004 rows=1 loops=5)

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

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

  • Index Cond: (id = projects_2.client_id)
  • Buffers: shared hit=15
141. 14.730 14.730 ↓ 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.691..2.946 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
  • Buffers: shared hit=2457
142. 0.026 0.048 ↑ 38.3 3 1

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

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

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

  • Buffers: shared hit=1
144. 15.698 87.853 ↓ 2.6 91,600 1

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

  • Buffers: shared hit=2036
145. 44.235 72.155 ↓ 2.6 91,600 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
  • Buffers: shared hit=2036
146. 20.705 20.705 ↑ 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..20.705 rows=174,437 loops=1)

  • Buffers: shared hit=1798
147. 0.259 7.215 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=238
148. 0.262 6.956 ↑ 1.1 657 1

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

  • Hash Cond: (clients_1.office_id = offices.id)
  • Buffers: shared hit=238
149. 0.297 4.370 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
  • Buffers: shared hit=190
150. 1.347 3.211 ↑ 1.0 623 1

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

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

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

  • Buffers: shared hit=98
152. 0.117 0.960 ↑ 1.0 623 1

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

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

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

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

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

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

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

  • Buffers: shared hit=22
156. 0.227 2.324 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=48
157. 0.201 2.097 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
  • Buffers: shared hit=48
158. 0.584 1.775 ↑ 1.0 489 1

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

  • Buffers: shared hit=44
159. 0.299 1.191 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=1.105..1.191 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
160. 0.892 0.892 ↑ 1.0 489 1

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
  • Buffers: shared hit=44
161. 0.062 0.121 ↑ 1.0 441 1

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

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

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

  • Buffers: shared hit=4
163. 16.213 91.620 ↑ 2.2 94,593 1

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

  • Buffers: shared hit=2032
164. 43.856 75.407 ↑ 2.2 94,593 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
  • Buffers: shared hit=2032
165. 19.216 19.216 ↑ 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.009..19.216 rows=174,437 loops=1)

  • Buffers: shared hit=1798
166. 1.142 12.335 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
  • Buffers: shared hit=234
167. 1.371 11.193 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
  • Buffers: shared hit=234
168. 1.332 4.741 ↑ 1.0 3,620 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
  • Buffers: shared hit=168
169. 1.213 1.213 ↑ 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.006..1.213 rows=3,621 loops=1)

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

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

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

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

  • Buffers: shared hit=98
172. 0.531 5.081 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
  • Buffers: shared hit=66
173. 0.521 4.550 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
  • Buffers: shared hit=66
174. 1.305 3.413 ↑ 1.0 1,821 1

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

  • Buffers: shared hit=44
175. 0.999 2.108 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.977..2.108 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
176. 1.109 1.109 ↑ 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..1.109 rows=1,821 loops=1)

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

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

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

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

  • Buffers: shared hit=22
179. 13.737 88.412 ↓ 2.4 76,366 1

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

  • Buffers: shared hit=2010
180. 43.842 74.675 ↓ 2.4 76,366 1

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

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
  • Buffers: shared hit=2010
181. 20.679 20.679 ↑ 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.008..20.679 rows=174,437 loops=1)

  • Buffers: shared hit=1798
182. 0.505 10.154 ↓ 2.4 1,591 1

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

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

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

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

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

  • Buffers: shared hit=98
185. 0.489 7.555 ↓ 2.4 1,591 1

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

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

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
  • Buffers: shared hit=114
187. 2.208 4.835 ↑ 1.0 2,429 1

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

  • Buffers: shared hit=44
188. 1.467 2.627 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=2.396..2.627 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
189. 1.160 1.160 ↑ 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.009..1.160 rows=2,429 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
  • Buffers: shared hit=70
191. 1.208 1.208 ↑ 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.208 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
  • Buffers: shared hit=70
192. 23.916 24.067 ↓ 1,169.2 357,766 1

Sort (cost=22.77..23.53 rows=306 width=26) (actual time=0.311..24.067 rows=357,766 loops=1)

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

Seq Scan on discounts (cost=0.00..10.13 rows=306 width=26) (actual time=0.023..0.151 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
194. 1,822.969 1,823.477 ↓ 7,966.8 24,282,940 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.494..1,823.477 rows=24,282,940 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 335kB
  • Buffers: shared hit=38
195. 0.508 0.508 ↑ 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.014..0.508 rows=3,048 loops=1)

  • Buffers: shared hit=38
Planning time : 10.992 ms
Execution time : 52,611.514 ms