explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sLb8

Settings
# exclusive inclusive rows x rows loops node
1. 161.138 77,735.385 ↓ 7.6 595,625 1

Subquery Scan on report_items_live (cost=3,666,282.31..3,674,328.36 rows=78,498 width=152) (actual time=76,513.235..77,735.385 rows=595,625 loops=1)

2. 847.262 77,574.247 ↓ 7.6 595,625 1

GroupAggregate (cost=3,666,282.31..3,673,543.38 rows=78,498 width=208) (actual time=76,513.233..77,574.247 rows=595,625 loops=1)

  • Group Key: projects.account_id, projects.id, clients.id, clients.office_id, ""*SELECT* 1"".rate_card_id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1_1"".user_id, ""*SELECT* 1_1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1_1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1_1"".reportable_id, ""*SELECT* 1_1"".plan_row_uuid, ""*SELECT* 1"".currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), ""*SELECT* 1"".value, staff_membership_activity_links.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST((GREATEST(""*SELECT* 1_1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
3. 3,206.591 76,726.985 ↓ 7.6 595,625 1

Sort (cost=3,666,282.31..3,666,478.56 rows=78,498 width=204) (actual time=76,513.208..76,726.985 rows=595,625 loops=1)

  • Sort Key: projects.account_id, projects.id, clients.id, clients.office_id, ""*SELECT* 1"".rate_card_id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1_1"".user_id, ""*SELECT* 1_1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1_1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1_1"".reportable_id, ""*SELECT* 1_1"".plan_row_uuid, ""*SELECT* 1"".currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), ""*SELECT* 1"".value, staff_membership_activity_links.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST((GREATEST(""*SELECT* 1_1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
  • Sort Method: external merge Disk: 89816kB
4. 332.709 73,520.394 ↓ 7.6 595,625 1

Hash Left Join (cost=3,645,470.40..3,652,116.28 rows=78,498 width=204) (actual time=49,776.914..73,520.394 rows=595,625 loops=1)

  • Hash 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: 411771
5. 8,186.714 73,187.427 ↓ 7.6 595,625 1

Merge Left Join (cost=3,645,457.04..3,646,549.19 rows=78,498 width=192) (actual time=49,776.643..73,187.427 rows=595,625 loops=1)

  • Merge Cond: ((projects.account_id = original_exchange_rates.account_id) AND (("*SELECT* 1".currency)::text = (original_exchange_rates.currency)::text))
  • Join Filter: ((original_exchange_rates.start_date <= "*SELECT* 1_1".date) AND (original_exchange_rates.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 31169112
6. 8,262.113 61,773.281 ↓ 7.6 595,625 1

Merge Left Join (cost=3,645,363.83..3,646,008.02 rows=78,498 width=184) (actual time=49,775.559..61,773.281 rows=595,625 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: 31169112
7. 1,461.549 50,246.214 ↓ 7.6 595,625 1

Sort (cost=3,645,270.62..3,645,466.86 rows=78,498 width=176) (actual time=49,774.470..50,246.214 rows=595,625 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: external merge Disk: 76944kB
8. 552.909 48,784.665 ↓ 7.6 595,625 1

Merge Left Join (cost=3,619,318.32..3,632,179.08 rows=78,498 width=176) (actual time=47,865.379..48,784.665 rows=595,625 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: 228687
9. 812.995 47,157.874 ↓ 7.6 595,625 1

Sort (cost=3,565,967.30..3,566,163.55 rows=78,498 width=144) (actual time=46,994.397..47,157.874 rows=595,625 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: external merge Disk: 61104kB
10. 8,464.766 46,344.879 ↓ 7.6 595,625 1

Merge Left Join (cost=3,553,306.07..3,553,950.27 rows=78,498 width=144) (actual time=34,079.462..46,344.879 rows=595,625 loops=1)

  • Merge Cond: ((projects.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: 32276381
11. 1,145.937 34,474.117 ↓ 7.6 595,625 1

Sort (cost=3,553,212.86..3,553,409.11 rows=78,498 width=652) (actual time=34,078.339..34,474.117 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: external merge Disk: 59528kB
12. 302.607 33,328.180 ↓ 7.6 595,625 1

Hash Join (cost=3,070,999.46..3,524,024.83 rows=78,498 width=652) (actual time=17,046.343..33,328.180 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
13. 198.303 33,025.403 ↓ 4.6 1,091,769 1

Append (cost=3,070,981.05..3,521,025.66 rows=235,493 width=652) (actual time=15,172.740..33,025.403 rows=1,091,769 loops=1)

14. 676.771 32,624.728 ↓ 4.6 1,075,067 1

Hash Left Join (cost=3,070,981.05..3,267,153.20 rows=235,492 width=111) (actual time=15,172.739..32,624.728 rows=1,075,067 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
15. 10,087.944 31,946.948 ↓ 4.6 1,075,067 1

Merge Left Join (cost=3,070,919.06..3,258,229.38 rows=235,492 width=165) (actual time=15,171.710..31,946.948 rows=1,075,067 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: 34327938
16. 1,458.847 15,130.658 ↓ 4.6 1,075,067 1

Sort (cost=3,061,674.64..3,062,263.37 rows=235,492 width=153) (actual time=14,853.306..15,130.658 rows=1,075,067 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 124920kB
17. 436.058 13,671.811 ↓ 4.6 1,075,067 1

Hash Left Join (cost=3,013,554.57..3,022,147.49 rows=235,492 width=153) (actual time=11,918.349..13,671.811 rows=1,075,067 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: 5359
18. 741.212 13,235.530 ↓ 1.5 1,080,426 1

Merge Left Join (cost=3,013,535.88..3,020,259.68 rows=699,480 width=148) (actual time=11,918.097..13,235.530 rows=1,080,426 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: 198922
19. 1,955.426 12,357.742 ↓ 1.5 1,080,426 1

Sort (cost=3,012,714.99..3,014,463.69 rows=699,480 width=124) (actual time=11,900.322..12,357.742 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: external merge Disk: 90888kB
20. 452.106 10,402.316 ↓ 1.5 1,080,426 1

Hash Join (cost=2,757,308.75..2,853,956.74 rows=699,480 width=124) (actual time=7,487.468..10,402.316 rows=1,080,426 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
21. 265.337 9,946.910 ↓ 2.2 1,549,746 1

Append (cost=2,757,083.82..2,844,899.50 rows=699,480 width=120) (actual time=7,484.138..9,946.910 rows=1,549,746 loops=1)

22. 405.900 9,147.521 ↓ 80.2 860,997 1

Subquery Scan on *SELECT* 1_1 (cost=2,757,083.82..2,803,952.12 rows=10,731 width=120) (actual time=7,484.137..9,147.521 rows=860,997 loops=1)

23. 640.331 8,741.621 ↓ 80.2 860,997 1

Merge Right Join (cost=2,757,083.82..2,803,764.32 rows=10,731 width=138) (actual time=7,484.131..8,741.621 rows=860,997 loops=1)

  • Merge Cond: ((nw_intervals_by_dates_roles.account_id = projects_1.account_id) AND (nw_intervals_by_dates_roles.date = generate_series.generate_series) AND (nw_intervals_by_dates_roles.office_id = clients_1.office_id))
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
  • Rows Removed by Filter: 640596
24. 40.559 52.651 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=42.167..52.651 rows=58,766 loops=1)

  • Sort Key: nw_intervals_by_dates_roles.account_id, nw_intervals_by_dates_roles.date, nw_intervals_by_dates_roles.office_id
  • Sort Method: external merge Disk: 1504kB
25. 12.092 12.092 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.010..12.092 rows=58,799 loops=1)

26. 231.793 8,048.639 ↓ 1.2 1,501,593 1

Materialize (cost=2,751,164.92..2,757,448.79 rows=1,256,775 width=110) (actual time=7,441.952..8,048.639 rows=1,501,593 loops=1)

27. 1,759.366 7,816.846 ↑ 1.4 870,252 1

Sort (cost=2,751,164.92..2,754,306.86 rows=1,256,775 width=110) (actual time=7,441.948..7,816.846 rows=870,252 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients_1.office_id
  • Sort Method: external merge Disk: 69272kB
28. 281.521 6,057.480 ↑ 1.4 870,252 1

Merge Left Join (cost=2,447,606.68..2,477,790.47 rows=1,256,775 width=110) (actual time=4,635.205..6,057.480 rows=870,252 loops=1)

  • Merge Cond: ((projects_1.account_id = non_working_intervals_by_dates.account_id) AND (generate_series.generate_series = non_working_intervals_by_dates.date) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
29. 363.767 5,681.604 ↑ 1.4 870,234 1

Merge Left Join (cost=2,441,687.78..2,461,409.89 rows=1,256,775 width=106) (actual time=4,551.389..5,681.604 rows=870,234 loops=1)

  • Merge Cond: ((projects_1.account_id = summary_vacations_by_dates.account_id) AND (generate_series.generate_series = summary_vacations_by_dates.date) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
30. 3,883.889 5,125.799 ↑ 1.4 870,234 1

Sort (cost=1,190,017.64..1,193,159.58 rows=1,256,775 width=70) (actual time=4,370.828..5,125.799 rows=870,234 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: external merge Disk: 68496kB
31. 245.230 1,241.910 ↑ 1.4 870,234 1

Nested Loop (cost=2,043.15..959,602.20 rows=1,256,775 width=70) (actual time=35.229..1,241.910 rows=870,234 loops=1)

32. 21.625 157.976 ↓ 1.0 52,419 1

Hash Left Join (cost=2,043.14..4,453.19 rows=50,271 width=70) (actual time=35.194..157.976 rows=52,419 loops=1)

  • Hash Cond: (projects_1.client_id = clients_1.id)
33. 21.566 135.439 ↓ 1.0 52,419 1

Hash Left Join (cost=1,981.15..4,258.92 rows=50,271 width=70) (actual time=34.250..135.439 rows=52,419 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
34. 21.929 110.169 ↓ 1.0 52,419 1

Hash Left Join (cost=1,756.21..3,901.92 rows=50,271 width=62) (actual time=30.531..110.169 rows=52,419 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
35. 38.909 85.802 ↓ 1.0 52,419 1

Hash Join (cost=1,557.89..3,571.54 rows=50,271 width=58) (actual time=28.050..85.802 rows=52,419 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
36. 18.937 18.937 ↓ 1.0 52,422 1

Seq Scan on plan_items (cost=0.00..1,314.46 rows=52,394 width=46) (actual time=0.011..18.937 rows=52,422 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
37. 14.746 27.956 ↓ 1.0 46,641 1

Hash (cost=998.45..998.45 rows=44,755 width=28) (actual time=27.956..27.956 rows=46,641 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3035kB
38. 13.210 13.210 ↓ 1.0 46,641 1

Seq Scan on plan_rows (cost=0.00..998.45 rows=44,755 width=28) (actual time=0.007..13.210 rows=46,641 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
39. 0.740 2.438 ↓ 1.0 2,819 1

Hash (cost=163.10..163.10 rows=2,818 width=8) (actual time=2.438..2.438 rows=2,819 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
40. 1.698 1.698 ↓ 1.0 2,819 1

Seq Scan on project_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.015..1.698 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
41. 1.705 3.704 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=12) (actual time=3.704..3.704 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
42. 1.999 1.999 ↑ 1.0 5,686 1

Seq Scan on projects projects_1 (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.009..1.999 rows=5,686 loops=1)

43. 0.448 0.912 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=0.912..0.912 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
44. 0.464 0.464 ↑ 1.0 1,822 1

Seq Scan on clients clients_1 (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.012..0.464 rows=1,822 loops=1)

45. 838.704 838.704 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.006..0.016 rows=17 loops=52,419)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
46. 7.666 192.038 ↑ 16.5 34,338 1

Materialize (cost=1,251,670.14..1,254,499.14 rows=565,800 width=44) (actual time=180.551..192.038 rows=34,338 loops=1)

47. 53.081 184.372 ↑ 19.1 29,556 1

Sort (cost=1,251,670.14..1,253,084.64 rows=565,800 width=44) (actual time=180.547..184.372 rows=29,556 loops=1)

  • Sort Key: summary_vacations_by_dates.account_id, summary_vacations_by_dates.date, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2705kB
48. 6.695 131.291 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=1,113,148.47..1,180,202.64 rows=565,800 width=44) (actual time=94.349..131.291 rows=29,556 loops=1)

49. 26.259 124.596 ↑ 19.1 29,556 1

GroupAggregate (cost=1,113,148.47..1,174,544.64 rows=565,800 width=52) (actual time=94.347..124.596 rows=29,556 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
50. 23.688 98.337 ↑ 142.6 29,683 1

Sort (cost=1,113,148.47..1,123,730.30 rows=4,232,734 width=21) (actual time=94.329..98.337 rows=29,683 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2714kB
51. 16.974 74.649 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=2.770..74.649 rows=29,683 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
52. 9.746 54.957 ↑ 661.8 31,591 1

Nested Loop (cost=0.01..418,621.08 rows=20,907,000 width=17) (actual time=0.033..54.957 rows=31,591 loops=1)

53. 3.397 3.397 ↑ 1.0 20,907 1

Seq Scan on vacations (cost=0.00..481.07 rows=20,907 width=17) (actual time=0.015..3.397 rows=20,907 loops=1)

54. 41.814 41.814 ↑ 500.0 2 20,907

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

55. 1.404 2.718 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=20) (actual time=2.718..2.718 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
56. 1.314 1.314 ↑ 1.0 3,958 1

Seq Scan on staff_memberships (cost=0.00..86.58 rows=3,958 width=20) (actual time=0.011..1.314 rows=3,958 loops=1)

57. 78.714 94.355 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=83.809..94.355 rows=60,807 loops=1)

  • Sort Key: non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.user_id
  • Sort Method: external sort Disk: 1728kB
58. 15.641 15.641 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.017..15.641 rows=58,799 loops=1)

59. 399.798 534.052 ↑ 1.0 688,749 1

Hash Join (cost=1,805.96..30,562.50 rows=688,749 width=120) (actual time=24.919..534.052 rows=688,749 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
60. 109.373 109.373 ↑ 1.0 688,749 1

Seq Scan on time_logs (cost=0.00..15,842.49 rows=688,749 width=28) (actual time=0.014..109.373 rows=688,749 loops=1)

61. 9.920 24.881 ↑ 1.0 26,131 1

Hash (cost=1,479.32..1,479.32 rows=26,131 width=16) (actual time=24.880..24.881 rows=26,131 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
62. 10.012 14.961 ↑ 1.0 26,131 1

Hash Left Join (cost=198.32..1,479.32 rows=26,131 width=16) (actual time=1.472..14.961 rows=26,131 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: 11264
63. 3.507 3.507 ↑ 1.0 26,131 1

Seq Scan on epics (cost=0.00..612.31 rows=26,131 width=12) (actual time=0.005..3.507 rows=26,131 loops=1)

64. 0.419 1.442 ↓ 1.0 2,819 1

Hash (cost=163.10..163.10 rows=2,818 width=8) (actual time=1.442..1.442 rows=2,819 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
65. 1.023 1.023 ↓ 1.0 2,819 1

Seq Scan on project_plans general_epic_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.006..1.023 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
66. 1.481 3.300 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=12) (actual time=3.300..3.300 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
67. 1.819 1.819 ↑ 1.0 5,686 1

Seq Scan on projects (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.007..1.819 rows=5,686 loops=1)

68. 116.125 136.576 ↓ 285.4 1,174,237 1

Materialize (cost=820.88..913.45 rows=4,114 width=40) (actual time=17.767..136.576 rows=1,174,237 loops=1)

69. 1.973 20.451 ↑ 1.0 4,053 1

Unique (cost=820.88..862.02 rows=4,114 width=65) (actual time=17.762..20.451 rows=4,053 loops=1)

70. 4.760 18.478 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=17.761..18.478 rows=4,075 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: 771kB
71. 4.772 13.718 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..573.91 rows=4,114 width=65) (actual time=8.464..13.718 rows=4,114 loops=1)

72. 3.174 8.946 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=8.449..8.946 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 771kB
73. 2.249 5.772 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=56) (actual time=2.926..5.772 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
74. 0.627 0.627 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links (cost=0.00..77.21 rows=4,121 width=28) (actual time=0.016..0.627 rows=4,121 loops=1)

75. 1.551 2.896 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=32) (actual time=2.896..2.896 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
76. 1.345 1.345 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..86.58 rows=3,958 width=32) (actual time=0.009..1.345 rows=3,958 loops=1)

77. 0.085 0.223 ↑ 1.0 342 1

Hash (cost=14.42..14.42 rows=342 width=9) (actual time=0.223..0.223 rows=342 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
78. 0.138 0.138 ↑ 1.0 342 1

Seq Scan on accounts accounts_1 (cost=0.00..14.42 rows=342 width=9) (actual time=0.012..0.138 rows=342 loops=1)

79. 6,453.272 6,728.346 ↓ 7,248.5 35,256,840 1

Sort (cost=9,244.42..9,256.58 rows=4,864 width=48) (actual time=318.387..6,728.346 rows=35,256,840 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: external sort Disk: 2544kB
80. 10.085 275.074 ↓ 8.9 43,105 1

Subquery Scan on pa (cost=8,824.95..8,946.55 rows=4,864 width=48) (actual time=202.318..275.074 rows=43,105 loops=1)

81. 107.842 264.989 ↓ 8.9 43,105 1

HashAggregate (cost=8,824.95..8,897.91 rows=4,864 width=48) (actual time=202.316..264.989 rows=43,105 loops=1)

  • Group Key: plan_rows_1.project_plan_id, plan_rows_1.epic_id, plan_rows_1.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)
82. 46.085 157.147 ↑ 1.0 43,258 1

WindowAgg (cost=6,337.50..7,310.85 rows=43,260 width=68) (actual time=102.078..157.147 rows=43,258 loops=1)

83. 52.123 111.062 ↑ 1.0 43,258 1

Sort (cost=6,337.50..6,445.65 rows=43,260 width=60) (actual time=102.063..111.062 rows=43,258 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: external merge Disk: 2688kB
84. 26.924 58.939 ↑ 1.0 43,258 1

Hash Join (cost=1,400.35..3,006.32 rows=43,260 width=60) (actual time=25.312..58.939 rows=43,258 loops=1)

  • Hash Cond: (plan_rows_1.uuid = plan_roles.plan_row_uuid)
85. 6.772 6.772 ↑ 1.0 46,645 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..998.45 rows=46,645 width=28) (actual time=0.016..6.772 rows=46,645 loops=1)

86. 14.647 25.243 ↑ 1.0 43,260 1

Hash (cost=859.60..859.60 rows=43,260 width=32) (actual time=25.243..25.243 rows=43,260 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3096kB
87. 10.596 10.596 ↑ 1.0 43,260 1

Seq Scan on plan_roles (cost=0.00..859.60 rows=43,260 width=32) (actual time=0.010..10.596 rows=43,260 loops=1)

88. 0.467 1.009 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=1.009..1.009 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
89. 0.542 0.542 ↑ 1.0 1,822 1

Seq Scan on clients (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.016..0.542 rows=1,822 loops=1)

90. 8.449 202.372 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2_1 (cost=243,049.32..250,340.07 rows=1 width=111) (actual time=126.587..202.372 rows=16,702 loops=1)

91. 28.126 193.923 ↓ 16,702.0 16,702 1

Merge Right Join (cost=243,049.32..250,340.06 rows=1 width=118) (actual time=126.580..193.923 rows=16,702 loops=1)

  • Merge Cond: ((non_working_intervals_by_dates_1.account_id = staff_memberships_2.account_id) AND (non_working_intervals_by_dates_1.user_id = staff_memberships_2.user_id) AND (non_working_intervals_by_dates_1.date = generate_series_2.generate_series))
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
  • Rows Removed by Filter: 326
92. 31.926 31.926 ↑ 1.0 58,756 1

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..3,416.17 rows=58,799 width=16) (actual time=0.019..31.926 rows=58,756 loops=1)

93. 4.576 133.871 ↑ 16.0 17,028 1

Materialize (cost=243,048.90..244,407.00 rows=271,620 width=64) (actual time=126.533..133.871 rows=17,028 loops=1)

94. 54.387 129.295 ↑ 16.0 17,028 1

Sort (cost=243,048.90..243,727.95 rows=271,620 width=64) (actual time=126.529..129.295 rows=17,028 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, generate_series_2.generate_series
  • Sort Method: quicksort Memory: 2980kB
95. 13.788 74.908 ↑ 16.0 17,028 1

Nested Loop (cost=1,217.40..208,320.53 rows=271,620 width=64) (actual time=27.165..74.908 rows=17,028 loops=1)

96. 8.250 38.664 ↓ 1.0 11,228 1

Hash Join (cost=1,217.39..1,885.52 rows=10,865 width=64) (actual time=27.145..38.664 rows=11,228 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
97. 3.337 3.337 ↑ 1.0 20,907 1

Seq Scan on vacations vacations_1 (cost=0.00..481.07 rows=20,907 width=44) (actual time=0.012..3.337 rows=20,907 loops=1)

98. 0.705 27.077 ↑ 1.3 1,635 1

Hash (cost=1,191.68..1,191.68 rows=2,057 width=32) (actual time=27.077..27.077 rows=1,635 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
99. 0.741 26.372 ↑ 1.3 1,635 1

Hash Join (cost=1,075.31..1,191.68 rows=2,057 width=32) (actual time=23.905..26.372 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
100. 1.214 23.130 ↑ 1.3 1,635 1

Hash Join (cost=939.26..1,050.20 rows=2,060 width=16) (actual time=21.370..23.130 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
101. 0.566 0.566 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..77.21 rows=4,121 width=20) (actual time=0.007..0.566 rows=4,121 loops=1)

102. 0.414 21.350 ↓ 9.0 1,635 1

Hash (cost=936.98..936.98 rows=182 width=4) (actual time=21.350..21.350 rows=1,635 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
103. 0.965 20.936 ↓ 9.0 1,635 1

HashAggregate (cost=935.16..936.98 rows=182 width=4) (actual time=20.586..20.936 rows=1,635 loops=1)

  • Group Key: staff_activities_with_dates.link_id
104. 2.254 19.971 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=15.914..19.971 rows=1,635 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2451
105. 1.340 17.717 ↑ 1.0 4,086 1

Unique (cost=810.60..851.74 rows=4,114 width=613) (actual time=15.905..17.717 rows=4,086 loops=1)

106. 4.115 16.377 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=15.904..16.377 rows=4,114 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: 514kB
107. 4.529 12.262 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..563.63 rows=4,114 width=613) (actual time=7.257..12.262 rows=4,114 loops=1)

108. 2.727 7.733 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=7.244..7.733 rows=4,114 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: 514kB
109. 1.893 5.006 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=28) (actual time=2.545..5.006 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
110. 0.608 0.608 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..77.21 rows=4,121 width=12) (actual time=0.004..0.608 rows=4,121 loops=1)

111. 1.309 2.505 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=20) (actual time=2.505..2.505 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
112. 1.196 1.196 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..86.58 rows=3,958 width=20) (actual time=0.006..1.196 rows=3,958 loops=1)

113. 1.254 2.501 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=16) (actual time=2.501..2.501 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
114. 1.247 1.247 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..86.58 rows=3,958 width=16) (actual time=0.009..1.247 rows=3,958 loops=1)

115. 22.456 22.456 ↑ 12.5 2 11,228

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

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
116. 0.004 0.170 ↑ 38.0 3 1

Hash (cost=16.98..16.98 rows=114 width=4) (actual time=0.170..0.170 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
117. 0.166 0.166 ↑ 38.0 3 1

Seq Scan on accounts (cost=0.00..16.98 rows=114 width=4) (actual time=0.018..0.166 rows=3 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
118. 3,405.630 3,405.996 ↓ 26,074.1 32,749,038 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.918..3,405.996 rows=32,749,038 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
119. 0.366 0.366 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.018..0.366 rows=1,256 loops=1)

120. 130.203 1,073.882 ↓ 3.7 973,811 1

Materialize (cost=53,351.02..54,666.21 rows=263,037 width=48) (actual time=849.195..1,073.882 rows=973,811 loops=1)

121. 542.911 943.679 ↑ 1.0 253,593 1

Sort (cost=53,351.02..54,008.62 rows=263,037 width=48) (actual time=849.190..943.679 rows=253,593 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: external merge Disk: 14432kB
122. 44.102 400.768 ↑ 1.0 253,793 1

Append (cost=286.40..21,579.24 rows=263,037 width=48) (actual time=2.834..400.768 rows=253,793 loops=1)

123. 0.132 17.777 ↓ 8.1 388 1

Subquery Scan on *SELECT* 1 (cost=286.40..427.60 rows=48 width=48) (actual time=2.833..17.777 rows=388 loops=1)

124. 0.157 17.645 ↓ 8.1 388 1

Merge Join (cost=286.40..427.12 rows=48 width=612) (actual time=2.830..17.645 rows=388 loops=1)

  • Merge Cond: (clients_2.brand_id = brands.id)
125. 0.172 17.428 ↓ 8.1 388 1

Nested Loop (cost=280.26..1,625.03 rows=48 width=56) (actual time=2.765..17.428 rows=388 loops=1)

126. 0.008 1.844 ↓ 4.0 4 1

Merge Join (cost=279.84..280.14 rows=1 width=32) (actual time=1.828..1.844 rows=4 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients_2.brand_id)
127. 0.028 0.846 ↑ 37.7 3 1

WindowAgg (cost=106.29..109.12 rows=113 width=45) (actual time=0.829..0.846 rows=3 loops=1)

128. 0.051 0.818 ↑ 28.2 4 1

Sort (cost=106.29..106.57 rows=113 width=29) (actual time=0.817..0.818 rows=4 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
129. 0.767 0.767 ↑ 1.0 113 1

Seq Scan on rate_cards (cost=0.00..102.44 rows=113 width=29) (actual time=0.186..0.767 rows=113 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4642
130. 0.008 0.990 ↑ 1.0 4 1

Sort (cost=173.55..173.56 rows=4 width=8) (actual time=0.988..0.990 rows=4 loops=1)

  • Sort Key: clients_2.brand_id
  • Sort Method: quicksort Memory: 25kB
131. 0.008 0.982 ↑ 1.0 4 1

Nested Loop (cost=0.56..173.51 rows=4 width=8) (actual time=0.555..0.982 rows=4 loops=1)

132. 0.009 0.962 ↑ 1.0 4 1

Nested Loop (cost=0.28..172.25 rows=4 width=8) (actual time=0.545..0.962 rows=4 loops=1)

133. 0.933 0.933 ↑ 1.0 4 1

Seq Scan on pricing_models (cost=0.00..139.05 rows=4 width=4) (actual time=0.527..0.933 rows=4 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5680
134. 0.020 0.020 ↑ 1.0 1 4

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

  • Index Cond: (id = pricing_models.project_id)
135. 0.012 0.012 ↑ 1.0 1 4

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

  • Index Cond: (id = projects_2.client_id)
136. 15.412 15.412 ↓ 2.0 97 4

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,344.42 rows=48 width=28) (actual time=0.906..3.853 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
137. 0.033 0.060 ↑ 58.0 2 1

Sort (cost=6.14..6.43 rows=116 width=4) (actual time=0.059..0.060 rows=2 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
138. 0.027 0.027 ↑ 1.0 116 1

Seq Scan on brands (cost=0.00..2.16 rows=116 width=4) (actual time=0.008..0.027 rows=116 loops=1)

139. 19.910 108.558 ↓ 2.6 87,611 1

Subquery Scan on *SELECT* 2 (cost=570.01..5,336.48 rows=33,506 width=48) (actual time=17.839..108.558 rows=87,611 loops=1)

140. 56.985 88.648 ↓ 2.6 87,611 1

Hash Join (cost=570.01..5,001.42 rows=33,506 width=612) (actual time=17.837..88.648 rows=87,611 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
141. 24.336 24.336 ↑ 1.0 170,280 1

Seq Scan on rates rates_1 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.013..24.336 rows=170,280 loops=1)

142. 0.253 7.327 ↑ 1.1 631 1

Hash (cost=561.33..561.33 rows=695 width=24) (actual time=7.327..7.327 rows=631 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
143. 0.252 7.074 ↑ 1.1 631 1

Hash Join (cost=369.72..561.33 rows=695 width=24) (actual time=4.464..7.074 rows=631 loops=1)

  • Hash Cond: (clients_3.office_id = offices.id)
144. 0.263 4.754 ↑ 1.0 597 1

Hash Join (cost=208.51..391.23 rows=597 width=8) (actual time=2.391..4.754 rows=597 loops=1)

  • Hash Cond: (projects_3.client_id = clients_3.id)
145. 1.262 3.475 ↑ 1.0 597 1

Hash Join (cost=146.51..327.67 rows=597 width=8) (actual time=1.353..3.475 rows=597 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
146. 0.892 0.892 ↑ 1.0 5,686 1

Seq Scan on projects projects_3 (cost=0.00..153.86 rows=5,686 width=8) (actual time=0.007..0.892 rows=5,686 loops=1)

147. 0.163 1.321 ↑ 1.0 597 1

Hash (cost=139.05..139.05 rows=597 width=4) (actual time=1.321..1.321 rows=597 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
148. 1.158 1.158 ↑ 1.0 597 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..139.05 rows=597 width=4) (actual time=0.020..1.158 rows=597 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5087
149. 0.483 1.016 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=1.016..1.016 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
150. 0.533 0.533 ↑ 1.0 1,822 1

Seq Scan on clients clients_3 (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.009..0.533 rows=1,822 loops=1)

151. 0.182 2.068 ↑ 1.1 438 1

Hash (cost=155.25..155.25 rows=477 width=28) (actual time=2.068..2.068 rows=438 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
152. 0.197 1.886 ↑ 1.1 438 1

Hash Join (cost=137.29..155.25 rows=477 width=28) (actual time=1.141..1.886 rows=438 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
153. 0.531 1.542 ↑ 1.0 477 1

WindowAgg (cost=123.66..135.58 rows=477 width=45) (actual time=0.971..1.542 rows=477 loops=1)

154. 0.263 1.011 ↑ 1.0 477 1

Sort (cost=123.66..124.85 rows=477 width=29) (actual time=0.958..1.011 rows=477 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 62kB
155. 0.748 0.748 ↑ 1.0 477 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..102.44 rows=477 width=29) (actual time=0.137..0.748 rows=477 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4278
156. 0.074 0.147 ↑ 1.0 428 1

Hash (cost=8.28..8.28 rows=428 width=4) (actual time=0.147..0.147 rows=428 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
157. 0.073 0.073 ↑ 1.0 428 1

Seq Scan on offices (cost=0.00..8.28 rows=428 width=4) (actual time=0.008..0.073 rows=428 loops=1)

158. 20.432 120.307 ↑ 2.2 91,407 1

Subquery Scan on *SELECT* 3 (cost=824.34..9,105.14 rows=198,580 width=48) (actual time=15.527..120.307 rows=91,407 loops=1)

159. 59.825 99.875 ↑ 2.2 91,407 1

Hash Join (cost=824.34..7,119.34 rows=198,580 width=612) (actual time=15.525..99.875 rows=91,407 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
160. 24.634 24.634 ↑ 1.0 170,280 1

Seq Scan on rates rates_2 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.013..24.634 rows=170,280 loops=1)

161. 1.417 15.416 ↑ 1.1 3,909 1

Hash (cost=772.85..772.85 rows=4,119 width=24) (actual time=15.416..15.416 rows=3,909 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
162. 1.605 13.999 ↑ 1.1 3,909 1

Hash Join (cost=576.26..772.85 rows=4,119 width=24) (actual time=9.301..13.999 rows=3,909 loops=1)

  • Hash Cond: (projects_4.client_id = clients_4.id)
163. 1.644 6.661 ↑ 1.0 3,545 1

Hash Join (cost=224.94..373.31 rows=3,546 width=8) (actual time=3.559..6.661 rows=3,545 loops=1)

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
164. 1.546 1.546 ↑ 1.0 3,546 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..139.05 rows=3,547 width=4) (actual time=0.010..1.546 rows=3,546 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2138
165. 1.526 3.471 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=8) (actual time=3.471..3.471 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 287kB
166. 1.945 1.945 ↑ 1.0 5,686 1

Seq Scan on projects projects_4 (cost=0.00..153.86 rows=5,686 width=8) (actual time=0.012..1.945 rows=5,686 loops=1)

167. 0.688 5.733 ↑ 1.0 1,792 1

Hash (cost=328.90..328.90 rows=1,794 width=28) (actual time=5.733..5.733 rows=1,792 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
168. 0.688 5.045 ↑ 1.0 1,792 1

Hash Join (cost=261.39..328.90 rows=1,794 width=28) (actual time=2.216..5.045 rows=1,792 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_4.id)
169. 1.948 3.700 ↑ 1.0 1,793 1

WindowAgg (cost=199.39..244.24 rows=1,794 width=45) (actual time=1.554..3.700 rows=1,793 loops=1)

170. 0.904 1.752 ↑ 1.0 1,793 1

Sort (cost=199.39..203.88 rows=1,794 width=29) (actual time=1.543..1.752 rows=1,793 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 189kB
171. 0.848 0.848 ↑ 1.0 1,793 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..102.44 rows=1,794 width=29) (actual time=0.008..0.848 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2962
172. 0.362 0.657 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=4) (actual time=0.657..0.657 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
173. 0.295 0.295 ↑ 1.0 1,822 1

Seq Scan on clients clients_4 (cost=0.00..39.22 rows=1,822 width=4) (actual time=0.007..0.295 rows=1,822 loops=1)

174. 17.864 110.024 ↓ 2.4 74,387 1

Subquery Scan on *SELECT* 4 (cost=680.42..5,394.83 rows=30,903 width=48) (actual time=10.098..110.024 rows=74,387 loops=1)

175. 56.257 92.160 ↓ 2.4 74,387 1

Hash Join (cost=680.42..5,085.80 rows=30,903 width=612) (actual time=10.097..92.160 rows=74,387 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
176. 25.836 25.836 ↑ 1.0 170,280 1

Seq Scan on rates rates_3 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.014..25.836 rows=170,280 loops=1)

177. 0.416 10.067 ↓ 2.4 1,567 1

Hash (cost=672.41..672.41 rows=641 width=24) (actual time=10.067..10.067 rows=1,567 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
178. 1.061 9.651 ↓ 2.4 1,567 1

Hash Join (cost=490.82..672.41 rows=641 width=24) (actual time=7.959..9.651 rows=1,567 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
179. 0.648 0.648 ↑ 1.0 5,686 1

Seq Scan on projects projects_5 (cost=0.00..153.86 rows=5,686 width=4) (actual time=0.006..0.648 rows=5,686 loops=1)

180. 0.490 7.942 ↓ 2.4 1,567 1

Hash (cost=482.81..482.81 rows=641 width=24) (actual time=7.942..7.942 rows=1,567 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
181. 0.623 7.452 ↓ 2.4 1,567 1

Hash Join (cost=393.60..482.81 rows=641 width=24) (actual time=4.530..7.452 rows=1,567 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
182. 2.058 5.070 ↓ 1.0 2,372 1

WindowAgg (cost=235.35..294.62 rows=2,371 width=45) (actual time=2.763..5.070 rows=2,372 loops=1)

183. 1.656 3.012 ↓ 1.0 2,372 1

Sort (cost=235.35..241.27 rows=2,371 width=29) (actual time=2.752..3.012 rows=2,372 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 282kB
184. 1.356 1.356 ↓ 1.0 2,372 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..102.44 rows=2,371 width=29) (actual time=0.069..1.356 rows=2,372 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
185. 0.427 1.759 ↓ 1.0 1,537 1

Hash (cost=139.05..139.05 rows=1,536 width=8) (actual time=1.759..1.759 rows=1,537 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
186. 1.332 1.332 ↓ 1.0 1,537 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..139.05 rows=1,536 width=8) (actual time=0.012..1.332 rows=1,537 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
187. 3,264.589 3,264.954 ↓ 25,180.3 31,626,403 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.972..3,264.954 rows=31,626,403 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 147kB
188. 0.365 0.365 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.018..0.365 rows=1,256 loops=1)

189. 3,227.080 3,227.432 ↓ 25,180.3 31,626,403 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.970..3,227.432 rows=31,626,403 loops=1)

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
190. 0.352 0.352 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months original_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.007..0.352 rows=1,256 loops=1)

191. 0.128 0.258 ↓ 1.2 336 1

Hash (cost=9.79..9.79 rows=286 width=26) (actual time=0.258..0.258 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
192. 0.130 0.130 ↓ 1.2 336 1

Seq Scan on discounts (cost=0.00..9.79 rows=286 width=26) (actual time=0.010..0.130 rows=336 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
Planning time : 11.539 ms
Execution time : 77,867.912 ms